6.2 Error-Tolerant Behavior of Programs

Normally, an error causes an interrupt. VBA displays a dialog box by means of which program execution can be terminated or the code analyzed in the immediate window. A continuation of the program is essentially never possible. During program development interrupts due to errors are unavoidable. However, a user who uses an Excel program developed by you should never encounter a dialog box with the words "macro error."

But since even in a well-developed program it is not impossible that under certain circumstances an error will occur (blatantly incorrect program operation, lack of memory, hard drive full, and so on), the command On Error provides a controlled continuation of the program in the event of an error.

The Command On Error

The command On Error determines how the program should behave when an error occurs. There are three variants for the use of this command:

 On Error Resume Next      '(1) execute the next instruction On Error GoTo label       '(2) call error handling routine On Error GoTo 0           '(3) normal: macro error dialog

Variant (1): On Error Resume Next

Variant (1) represents the most convenient way to suppress error alerts: The program will simply continue with the next instruction. Since the instructions of a program build as a rule one on the other, there is a great probability that another error will soon occur. This error, however, will be met the same way as the previous one, so that the procedure will sooner or later come to an end. It is rather obvious that in this case the procedure will not have accomplished its intended purpose.

Thus it is generally the case that variant (1) does not represent a reasonable reaction to errors. There are exceptions, though. Consider, for example, the procedure test_resume_next . This procedure changes the display of row and column headers in the currently active window.

Of course, the execution of this procedure makes sense only if a worksheet is currently being displayed. If another sheet happens to be active, then an error occurs (since a chart sheet, say, that is, the object Chart , does not recognize the property DisplayHeadings ). Instead of thinking long and hard about how one might test whether the active sheet is in fact a worksheet, it is easier simply to insert the instruction On Error Resume Next into the procedure. Problem solved !

 Sub  test_resume_next()  On Error Resume Next   ActiveWindow.DisplayHeadings = _     Not ActiveWindow.DisplayHeadings End Sub

Within the procedure the occurrence of an error can be verified with Err . This function contains the number of the most recently occurring error (even if this error did not lead to an error alert due to On Error Resume Next ). The instruction On Error resets Err to 0.

Variant (2): On Error GoTo Label

Variant (2) enables the programming of " genuine " error-handling routines. VBA branches on the occurrence of an error into the part of the procedure indicated by a label . There a response to the error can take place (for example, display an error alert, save unsaved data, and so on). Then the procedure can be exited or continued with Resume .

For understanding the GoTo command it is necessary first to understand the notion of a label. A label is a program line consisting only of a name followed by a colon . Within a procedure a label can be jumped to with the GoTo command, that is, the execution of the program is continued on the line after the label. Jumps using GoTo can have the effect of making the syntax of a function or procedure difficult to decipher and therefore are to be avoided in structured programming. The command GoTo should therefore never be used ( except in conjunction with OnError ). (There is also no need. All the examples in this book have been formulated without GoTo .)

But let us return to error handling in which labels and jumps are not only allowed, but are absolutely necessary. On an error VBA jumps to the location given by the label. The label must be located within the current procedure. Normally, the error-handling routine is placed at the end of the procedure. To avoid the errorhandling routine being executed in the normal course of the procedure, Exit Sub or Exit Function is usually placed just before the label.

Within the error-handling routine you can determine the number of the error that has occurred with Err() and reply accordingly . A list of error numbers used by VBA can be found in the on-line help under the topic "error number." The response to an error can include calling other procedures (for example, to save data).

Note, however, that it is possible for errors to occur both within the errorhandling routine and in other procedures called. Variant (2) of On Error holds only for a single error! The next error triggers again the normal behavior (that is, the display of the "macro error" form). You can avoid this by executing On Error xxx within the error-handling routine.

The Command Resume

The faulty procedure can be continued from the error-handling routine with Resume or ended with Exit Sub / Function . If the end of the error-handling routine is reached without Resume being encountered , then the procedure is considered to have ended and the error as having been dealt with. There are three variants of the Resume command:

 Resume            ' reexecutes the instruction on which the error occurred Resume Next       ' resumes the procedure with the next instruction Resume label      ' resumes the procedure at label

In all three cases the program is continued within the procedure in which the error-handling routine is located. It is impossible to use Resume to jump into another procedure. To make possible a controlled response in the case of further errors, a new (or perhaps the same) error-handling routine should be erected at the latest before Resume . (Take care, however, to avoid an infinite loop by continual repetition of a faulty program segment.)

Example of Variant (2)

A procedure with an error-handling routine can be constructed along the lines of the procedure test_resume : In the normal case, that is, when no error occurs, the procedure will continue to the instruction Exit Sub . If an error occurs, then program will be continued on the line after test_resume_error . If the error handling is successful, then the procedure is continued at test_resume_cont ; otherwise , it is interrupted . If a second error occurs, then a branch to test_resume_another_error occurs. At that point there is no further attempt to continue the procedure. Depending on the application, here an alert can be displayed, data saved, or some other action taken.

Do not depend on your error-handling routine to succeed in actually removing the error. Consider the possibility of an error occurring in the error-handling routine itself. Avoid at all cost an infinite loop! (An error leads to a call to the errorhandling routine, from there the procedure is continued, the error appears again, a new call to the error-handling routine, and so on, and so on.)

From the point of view of the user one thing is most important: It matters not how, when, or where an error occurred, but in no case should data be lost.

 Sub  test_resume()  On Error Resume test_resume_error   ... test_resume_cont:           ' here the procedure is continued after an error   ...   Exit Sub                  ' End of the procedure test_resume_error:          ' error-handling routine begins here   If Err()=... Then     On Error Resume test_resume_another_error     ...                     ' response to a recognized error     Resume test_resume_cont ' resume procedure   End If test_resume_another_error:  ' unknown error or a second error   ...                       ' error alert, save data, etc. End Sub

Variant (3): On Error GoTo 0

The third variant of On Error serves to deactivate previously erected error-handling routines. After On Error GoTo 0 the "normal" behavior of VBA is again in force, that is, the display of Macro Error forms.

Error Handling in Nested Procedures

Suppose that by clicking on a tool procedure A is called; A calls B, and B calls C. If an error occurs in C, the error-handling routine belonging to C will be called. If there is no error-handling routine in C, then control is returned to B. If there is no error-handling routine in B, then control is passed back to A. Only if there is no error-handling routine in A does the Macro Error form appear.

Visual Basic thus searches through all of the mutually called subprograms in reverse order for a suitable error-handling routine. Only when there is none even in the original calling procedure does Visual Basic display an error alert and interrupt program execution.

The two commands Resume and Resume Next are always valid for procedures in which they are placed. If an error occurs in procedure C but is dealt with only in the error-handling routine of procedure A, then the program is resumed with Resume at the indicated location in A. It is impossible to jump with Resume to the current procedure (for example, to the instruction in C on which the error occurred).

The Functions Err, Error( ), CVErr, and the Command Error

Err returns an identification number of the error that has occurred. The range of possible error numbers is given in the on-line help. Error() returns the error text of the most recently occurring error. Error(n) returns the error text of error number n .

The command Error can be used to simulate an error. This would make sense, for example, in testing an error-handling routine. The instruction Error n leads to the display of the macro error form and can thus be reasonably placed at the end of an error-handling routine for the case that the error was not removed.

With the function CVErr you can create an error value for a Variant variable. This function can, for example, be used in a user-defined worksheet function to return an error value instead of a result.

 result = CVErr(xlErrValue)

A list of predefined error constants for this purpose can be found in the on-line help under "error values in cells ." An example for the application of CVErr can be found in Chapter 5 (in the variant of the user-defined Discount function).

Application Example

The following example was introduced already in Chapter 4, where the issue there was to determine the number of dimensions of a field that was to be passed as a parameter to a procedure. Since there is no suitable function to accomplish this purpose, the upper index limit up to dimension 10 is determined in the first For loop with UBound . Since one may assume that the field does not have so many dimensions, sooner or later an error will occur. The error is, in fact, planned for! In the error-handling routine the number of dimensions is determined from the current value of i that led to an error in UBound .

 ' example file VBA-Concepts.xls, Module Procedures Sub  arraytest(arr())  Dim i, dimensions   On Error GoTo arraytest_error   For i = 1 To 10: dimensions = UBound(arr, i): Next i arraytest_continue:   dimensions = i - 1   Debug.Print dimensions, " Dimensions"   For i = 1 To dimensions     Debug.Print "Dimension "; i; ": "; LBound(arr, i); " Bis "; UBound(arr, i)   Next i   Exit Sub arraytest_error:   ' this program segment is called as soon as a nonexistent arrdimension is   ' accessed in the loop   Resume arraytest_continue End Sub