On Error GoTo err2 Debug.Print 1 / 0
Debug.Print "Other code" 'not executed Exit Sub
err1:
Debug.Print "1 in err1" 'not executed Exit Sub
err2:
Debug.Print "1 in err2" 'executed End Sub
Handling errors in error-handling routines
A fatal error in an error-handling routine
When an error handler of On Error GoTo line in a particular procedure is activated (that is, in the process of handling an error in its error-handling routine), any On Error statements of error handlers encountered in the routine are executed and hence enabled, but they cannot be activated even when another error occurs. In other words, when an On-Error-GoTo-line error handler is active, any runtime error in the error-handling routine is fatal. See the Sample3Fail procedure below.
Sub Sample3Fail()
'Any error that occurs in an error-'handling routine is fatal
On Error GoTo err1 Debug.Print 1 / 0
Exit Sub 'not executed
err1:
Debug.Print "1 in err1" 'executed On Error GoTo err2 'not working 'On Error Resume Next 'not working Debug.Print 1 / 0 'fatal error Exit Sub 'not executed err2:
Debug.Print "1 in err2" 'not executed End Sub
Overcoming fatal errors with the Resume statement
An active error handler in a procedure can be deactivated (but it still remains enabled) by a Resume statement. This statement can only be used in an error-handling routine and an error must have been occurred. Otherwise, an execution of the statement generates a runtime error with an error message of
"Resume without error".
When a Resume statement is executed, VBE resumes execution at certain line of code in the procedure, at which it depends on one of the following forms of the Resume statement.
Statement Description
Resume Execution resumes at the same line of statement that caused the error.
Resume Next Execution resumes with the statement immediately following the statement that caused the error.
Resume line Execution resumes at the line of code labeled line in the procedure.
When a Resume statement is executed, the current error handler is deactivated (but not disabled). If a runtime error then occurs, the last enabled handler will be activated.
In the Sample4aInfiniteLoop procedure below, when the division-by-zero error occurs, execution jumps to the line of code labeled err1. When the Resume statement is executed, it resumes execution at the division-by-zero statement. Hence, the division-by-zero error occurs again, On Error Goto err1 is activated again, and execution jumps to the line labeled err1. Hence, the Sub procedure loops forever. The discussion here is for the understanding of the flow of control when using the Resume statement. Please consult the VBA Help system (by searching the word Resume) for a working example on how the statement can possibly be used to rectify the cause of an expected runtime error.
Sub Sample4aInfiniteLoop()
'Resuming execution at the line of code
'that caused an error (without rectifying 'the cause in the error-handling routine) 'forms an infinite loop
On Error GoTo err1 Debug.Print 1 / 0
Debug.Print "other code" 'not executed Exit Sub
err1:
Debug.Print "1 in err1" 'executed Resume
End Sub
The following Sample4b procedure replaces Resume with Resume Next to avoid an infinite loop.
When the division-by-zero error occurs, execution jumps to err1. When the Resume Next statement is executed, it resumes execution with the statement immediately following the division-by-zero statement.
Sub Sample4b()
'Resuming execution with the statement 'immediately following the statement 'that caused the error
On Error GoTo err1 Debug.Print 1 / 0
Debug.Print "other code" 'executed Exit Sub 'executed
err1:
Debug.Print "1 in err1" 'executed Resume Next
End Sub
The third possible way of using the Resume statement is shown in the following Sample4c and Sample4d procedures. In each of the procedures, it shows that how the active error handler On Error GoTo err1 is deactivated by the Resume statement and control is diverted to a specified line of code to enable a new error handler. The newly enabled error handler is then the last enabled error handler.
It is activated when an error occurs. Hence, the fatal error in Sample3Fail can be avoided and handled gracefully.
Sub Sample4c()
'Deactivating an active error handler and 'diverting control to a specified
'line to enable a new error handler
On Error GoTo err1 Debug.Print 1 / 0
Exit Sub 'not executed err1:
Debug.Print "1 in err1" 'executed Resume Continue
Exit Sub 'not executed Continue:
On Error GoTo err2 Debug.Print 1 / 0
Exit Sub 'not executed err2:
Debug.Print "1 in err2" 'executed End Sub
Sub Sample4d()
'Deactivating an active error handler and 'diverting control to a specified
'line to enable a new error handler On Error GoTo err1
Debug.Print 1 / 0
Exit Sub 'not executed err1:
Debug.Print "1 in err1" 'executed Resume Continue
Exit Sub 'not executed Continue:
On Error Resume Next Debug.Print 1 / 0
Debug.Print "2" 'executed On Error GoTo 0
End Sub
The following Sample4dAlternative procedure is an alternative to Sample4d. It also shows that the last enabled error handler, On Error Resume Next, is properly activated when the division-by-zero error occurs. Try to comment the On Error Resume Next statement and step through the code to see what happens. It forms an infinite loop.
Sub Sample4dAlternative()
'Deactivating an active error handler and 'diverting control to a new line
'to enable a new error handler On Error GoTo err1
Debug.Print 1 / 0
Exit Sub 'not executed err1:
Debug.Print "1 in err1" 'executed On Error Resume Next
Overcoming fatal errors with a called Sub procedure
An alternative to handling errors gracefully in an error-handling routine, without using the Resume statement, is to use a called procedure in the routine because any On Error statement in a calling procedure will become inactive when another procedure is called. The following SampleAsIn4c and SampleAsIn4d procedures show the alternatives to the Sample4c and Sample4d procedures, respectively.
Sub SampleAsIn4c()
'An alternative to Sample4c by using 'a called procedure
On Error GoTo err1 Debug.Print 1 / 0
Exit Sub 'not executed err1:
Debug.Print "1 in err1" 'executed Call GotoErr2Sub
End Sub
Private Sub GotoErr2Sub() On Error GoTo err2 Debug.Print 1 / 0
Exit Sub 'not executed err2:
Debug.Print "1 in Err2" 'executed End Sub
Sub SampleAsIn4d()
'An alternative to Sample4d by using 'a called procedure
On Error GoTo err1 Debug.Print 1 / 0
Exit Sub 'not executed err1:
Debug.Print "1 in err1" 'executed Call ResumeNextSub
Debug.Print "2 in err1" 'executed End Sub
Private Sub ResumeNextSub() On Error Resume Next Debug.Print 1 / 0
Debug.Print "2" 'executed On Error GoTo 0
End Sub
Chapter 4:
Some other tips on VBA programming
Other than the tools discussed in Chapter 1 and some tips discussed along the way in previous chapters, below are some other tips on VBA programming for beginners.