VBA On Error statement


Example

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 Resume keyword.

Line labels denote subroutines: because subroutines originate from legacy BASIC code and uses GoTo and 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:

  • a procedure has one and only one error-handling subroutine
  • the error-handling subroutine only ever runs in an error state

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

Error Handling Strategies

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

Line numbers

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.