Using the On Error Statement

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In the MultiPage.xls project from Chapter 6, the Click() event of the cmdCalcStats Command Button control contained the statement:

On Error Resume Next

The On Error statement enables error handling in a VBA program. The On Error statement must be followed with instructions to VBA for deciding a course of action when a runtime error is encountered. The course of action taken depends on the type of error that is anticipated.

The On Error statement must precede the code that is anticipated to generate the runtime error. The On Error statement is normally placed near the beginning of a procedure.

In the case of the Click() event procedure in Chapter 6, a runtime error was anticipated for the AVERAGE(), MEDIAN(), and STDEVP() worksheet functions when the user failed to select data, but clicked the Calculate button. Because the runtime error will only occur under special circumstances, it was handled by using the Resume Next clause. The Resume Next clause sends program execution to the next line of code following the line that generated the error. When the user notices that no statistics were calculated after clicking the Calculate button, then he or she should conclude that they need to select a range of cells on the worksheet. The Resume Next clause is the simplest solution for handling runtime errors and works well in the MultiPage.xls project; however, it may not always be the best solution.

When an anticipated error requires execution of a special block of code, use the GoTo statement after On Error.

On Error GoTo ErrorHandler

The term ErrorHandler refers to a line label used to direct program execution to the block of code specifically created for handling the runtime error. Line labels must start at the leftmost position in the editor window and end with a colon. The error handling code follows the line label.

The use of the GoTo statement goes all the way back to the earliest versions of Basic and a few other programming languages. The GoTo statement is rarely seen anymore because when overused, the order of execution of programming statements can be very difficult to follow and results in what is termed "spaghetti code." Spaghetti code is very hard to debug and for that reason, the use of the GoTo statement in VBA should be limited to error handling routines.

An illustration of the error handling process appears in Figure 7.2.

Sub MySub(}

'Variable declarations On Error GoTo ErrorHandler

Line of code that generates an error-*-

Next line of code executes when error is resolved

Main block of code

Exit Sub-^'Program execution exits MySub()

Order of program execution in a procedure with error handling.

'Error handling code that fixes the error

'If error is not resolved then exit the sub-*■ 4


End Sub

Figure 7.2 shows the order of program execution in a sub procedure that contains error handling code. The order of program execution proceeds as follows:

1. If no error is generated, the main block of code executes but program execution exits the sub procedure before reaching the ErrorHandler line label.

2. An error is generated and code execution proceeds to the ErrorHandler line label.

3. The error is resolved in the ErrorHandler and code execution proceeds back to the original line of code that generated the error. Then the main block of code executes before program execution exits the sub procedure.

4. If the error is not resolved, then program execution should exit the sub without executing the main block of code.

Now consider the Click() event procedure of the Calculate button after I added a little more error handling code.

Private Sub cmdCalcStats_Click() Const NUMFORMAT = "#.00"

On Error GoTo ErrorHandler

IblCount.Caption = Application.WorksheetFunction.Count _


lblSum.Caption = Application.WorksheetFunction.Sum _

(Range(refStats.Text)) IblMin.Caption = Application.WorksheetFunction.Min _

(Range(refStats.Text)) lblMax.Caption = Application.WorksheetFunction.Max _

(Range(refStats.Text)) lblMedian.Caption = Application.WorksheetFunction.Median _

(Range(refStats.Text)) lblAvg.Caption = Format(Application.WorksheetFunction.Average _

(Range(refStats.Text)), NUMFORMAT) lblStanDev.Caption = Format(Application.WorksheetFunction.StDevP _

(Range(refStats.Text)), NUMFORMAT)

Exit Sub ErrorHandler:

MsgBox "An error was encountered while attempting to calculate the statistics. " _ & vbCrLf & Err.Description & vbCrLf & _

"Check for a valid range selection and try again." & vbCrLf, _ vbCritical, "Error " & Err.Number

End Sub

After the constant declaration, the error handler is "turned on" with the On Error statement and a reference to the ErrorHandler line label. The error handling code starts with the line label, but is not a separate procedure. Instead, it is a block of code isolated by the line label; therefore, an Exit Sub statement is placed near the end of the procedure just before the line label to prevent the code in the error-handling block from being executed if no error is generated.

The error handling code follows the line label, and due to the structure of the sub procedure, will only be executed when a runtime error occurs. In this example, the error handling code is only one statement, albeit a long one. A message box with a description of the error is displayed to the user. The description is obtained from the Description property of the Err object. The Err object stores information about runtime errors and is intrinsic to VBA. The properties of the Err object are initialized when a runtime error occurs with an error handling routine enabled so you can access its properties in any error handling code block.

When possible, you should write code in your error handler that fixes the error and resumes program execution at the error's source using the Resume keyword. In this example, that is not possible because the error is generated by an invalid range selection. In this case, the best you can do is to anticipate the cause of the error and suggest a solution to the user.

Figure 7.3 shows the message box displayed by the error handler in the Click() event procedure of the Calculate button.

The message box displayed by the error handler in the MultiPage.xls project from Chapter 6.

More examples of error handling code blocks are discussed later in the chapter.

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment