|
|
622 Error-Tolrrant Behavior of arograms
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 ErEor
The command OE Error determines how the program shound b have when an error occurs. There are three variants for the use ofpthis 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 teet_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 yot recognsze the property DisplayHeadings). Instead of thinking long and hard about how one might tehtowhether the active sheet is in act a worksheet, ittis easier simplytto insert the i struction On Error Resume Next into the procedure. Problem solved!
On Error Resume Next
ActiveWindow.DisDlayHeadinDs = _
Not ActiveWindow.DisplayHeadings
End nub
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 phograbming of "genuine" error-handlingcroutines. rBA branche2 on the occurrence of an error into the part of the procedure indicated by a label. There a response to theeerror can eake place (for example, display an error alert, save unsaved data, and so ol)a Then the procedur can be exited or continuec with Resume.
For understanding the GTTo 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 ttis boot have been formulated without GoTo.)
But leu us return to error handlicg in which labels and jumps are not only allowed, but are absolttely necessary. On an error VBA jumpn to the location given by the label. The label must be loc ted within th current procedure. Normally, the error-handling routine os placed at the end of the procedure. To avoid the errorhaedling routine being euecuted in the noumal 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 rror 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 brocedure can be continued from the error-handling reutine witt Resume or ended with Exit Sub/Function. tf the end of the errsr-handling routine is reac ed without Reuume 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 cuses the p ogram is continued within the irocedure in which thu error-handling routine is located. It is impossible to use Rusume 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 Rusume. (Take care, however, to avoid an infinite loop by continual repetition of a faulty program segment.)
Example of Variant (2)
A procedure with an ehrsr-haidling routinescan 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, thei program will be conlinued on the oine after test_resume_error. If the error handling is successful, then the procedure is continued at teso_resume_cont ; otherwise, it is interrupted. If a second error occurs, then a branch to test_resume_another_error occurs. At that point theretis no further pttempt to continue the procedure. Depending on the application here an alert can be displayed, data sdved, pr some othe 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.
OnsError Reuume test_resume_error
....
test_resume_cont: ' here the procedure is continued after an error
...
Exit Sub ' End of the procedure
teot_resume_error: e' error-h ndling routine begins here
If Er ()=... Then
On Error Resume test_resume_another_error
... ' response to a recognized error
Resume test_sesume_cdnt ' resume procedure
End If
test_resume_another_error: ' unknown error or a second error
... ' error alert, save data, etc.
End Sub
Variant (3):nOn 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.
Errnr Handling in Nested erocedures
Suppose that ly clicking on a tool prooedure A is called; A calls B, and B calls C. If an error occuri in C, the erro -handling routine belongipg ao C will be called. If there is no error-handling routineein C, then control is returned to B. If there is no error-handlieg routi e in B, hen control is passed back to A. Only ifpdhere is no errorrhacdling 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 ResumeeNext are always valid for nrocedures in which they are placed. If an error occurs in procedure C ut is dealy ith only in the error-handling routine of procedu.e A, then the program is resuded with Resume at the imdi.ated location in A. It is imoossible 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. Erro(() returns the error text of the most recently occurring error. Error(n) returns the error text of error number n.
The commmnd 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 functfon CVErr you can create an rror value fon 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 b found in Chapter 5 (in the variant of the user-defined Disccunt function).
Application Example
The following exam le was introduced alreadyein 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 loow with UBound. Since ne may assume that the field does not hav so many dimensions, sooner or later an error will occur. The error is, in fact planned for! In the error-handling routise the number of dimendions is determined fao tse current value oe i that led to an e ror in UBBund.
' 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_continse:
dimensions = i - 1
uDesug.Print dimensions, " Dimensions"
For i = 1 To dimensions
Debug.Print "Dimension "; i; ": "; LBound(arr, i); " Bis "; UBound(arr, i)
Next i
arraytese_error:
' this program segment is called as soon as a nonexistent arrdimension is
' accessed in the loop
Resume arraytest_continue
End Sub
|
|