VBA Error Handling Resume keyword
Example
An error-handling subroutine will either:
- run to the end of the procedure, in which case execution resumes in the calling procedure.
- or, use the
Resumekeyword to resume execution inside the same procedure.
The Resume keyword should only ever be used inside an error handling subroutine, because if VBA encounters Resume without being in an error state, runtime error 20 "Resume without error" is raised.
There are several ways an error-handling subroutine may use the Resume keyword:
Resumeused alone, execution continues on the statement that caused the error. If the error isn't actually handled before doing that, then the same error will be raised again, and execution might enter an infinite loop.Resume Nextcontinues execution on the statement immediately following the statement that caused the error. If the error isn't actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.Resume [line label]continues execution at the specified line label (or line number, if you're using legacy-style line numbers). This would typically allow executing some cleanup code before cleanly exiting the procedure, such as ensuring a database connection is closed before returning to the caller.
On Error Resume Next
The On Error statement itself can use the Resume keyword to instruct the VBA runtime to effectively ignore all errors.
If the error isn't actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.
The emphasis above cannot be emphasized enough. On Error Resume Next effectively ignores all errors and shoves them under the carpet. A program that blows up with a runtime error given invalid input is a better program than one that keeps running with unknown/unintended data - be it only because the bug is much more easily identifiable. On Error Resume Next can easily hide bugs.
The On Error statement is procedure-scoped - that's why there should normally be only one, single such On Error statement in a given procedure.
However sometimes an error condition can't quite be avoided, and jumping to an error-handling subroutine only to Resume Next just doesn't feel right. In this specific case, the known-to-possibly-fail statement can be wrapped between two On Error statements:
On Error Resume Next
[possibly-failing statement]
Err.Clear 'resets current error
On Error GoTo 0
The On Error GoTo 0 instruction resets error handling in the current procedure, such that any further instruction causing a runtime error would be unhandled within that procedure and instead passed up the call stack until it is caught by an active error handler. If there is no active error handler in the call stack, it will be treated as an unhandled exception.
Public Sub Caller()
On Error GoTo Handler
Callee
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & " in Caller."
End Sub
Public Sub Callee()
On Error GoTo Handler
Err.Raise 1 'This will be handled by the Callee handler.
On Error GoTo 0 'After this statement, errors are passed up the stack.
Err.Raise 2 'This will be handled by the Caller handler.
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & " in Callee."
Resume Next
End Sub
