Even with guard clauses, one cannot realistically always account for all possible error conditions that could be raised in the body of a procedure. The
On Error GoTo statement instructs VBA to jump to a line label and enter "error handling mode" whenever an unexpected error occurs at runtime. After handling an error, code can resume back into "normal" execution using the
Line labels denote subroutines: because subroutines originate from legacy BASIC code and uses
GoSub jumps and
Return statements to jump back to the "main" routine, it's fairly easy to write hard-to-follow spaghetti code if things aren't rigorously structured. For this reason, it's best that:
This means a procedure that handles its errors, should be structured like this:
Private Sub DoSomething() On Error GoTo CleanFail 'procedure code here CleanExit: 'cleanup code here Exit Sub CleanFail: 'error-handling code here Resume CleanExit End Sub
Sometimes you want to handle different errors with different actions. In that case you will inspect the global
Err object, which will contain information about the error that was raised - and act accordingly:
CleanExit: Exit Sub CleanFail: Select Case Err.Number Case 9 MsgBox "Specified number doesn't exist. Please try again.", vbExclamation Resume Case 91 'woah there, this shouldn't be happening. Stop 'execution will break here Resume 'hit F8 to jump to the line that raised the error Case Else MsgBox "An unexpected error has occurred:" & vbNewLine & Err.Description, vbCritical Resume CleanExit End Select End Sub
As a general guideline, consider turning on the error handling for entire subroutine or function, and handle all the errors that may occur within its scope. If you need to only handle errors in the small section section of the code -- turn error handling on and off a the same level:
Private Sub DoSomething(CheckValue as Long) If CheckValue = 0 Then On Error GoTo ErrorHandler ' turn error handling on ' code that may result in error On Error GoTo 0 ' turn error handling off - same level End If CleanExit: Exit Sub ErrorHandler: ' error handling code here ' do not turn off error handling here Resume End Sub
VBA supports legacy-style (e.g. QBASIC) line numbers. The
Erl hidden property can be used to identify the line number that raised the last error. If you're not using line numbers,
Erl will only ever return 0.
Sub DoSomething() 10 On Error GoTo 50 20 Debug.Print 42 / 0 30 Exit Sub 40 50 Debug.Print "Error raised on line " & Erl ' returns 20 End Sub
If you are using line numbers, but not consistently, then
Erl will return the last line number before the instruction that raised the error.
Sub DoSomething() 10 On Error GoTo 50 Debug.Print 42 / 0 30 Exit Sub 50 Debug.Print "Error raised on line " & Erl 'returns 10 End Sub
Keep in mind that
Erl also only has
Integer precision, and will silently overflow. This means that line numbers outside of the integer range will give incorrect results:
Sub DoSomething() 99997 On Error GoTo 99999 99998 Debug.Print 42 / 0 99999 Debug.Print Erl 'Prints 34462 End Sub
The line number isn't quite as relevant as the statement that caused the error, and numbering lines quickly becomes tedious and not quite maintenance-friendly.