The On Error Statement

The On Error statement is used to build error-handling routines for VBA applications. Error handling is set up using an On Error GoTo line, where line is the line label at which the error handler begins. The outline of a VBA procedure with basic error handling is shown below:

Sub MyProcedure()
    On Error GoTo ErrorHandler
    ''' Procedure code goes here.
    Exit Sub
ErrorHandler:
    MsgBox "Error", vbCritical, "My Add-in"
End Sub

There are some cases in which you want a procedure to ignore any errors that occur. You can allow a procedure to bypass errors using the On Error Resume Next statement. When an error occurs, this statement causes program execution to continue at the line immediately following the line that caused the error. If you intentionally turn off error handling, it is recommended that you document the expected errors you are ignoring in your code. This serves you well when you need to upgrade or troubleshoot this section of code in the future. The basic guidelines for turning off errors are:

• Do not do it unless it's absolutely necessary.
• Be aware of the side effects (other errors you weren't anticipating will also be ignored).
• Document the expected errors you are ignoring (for future development).
• Return to a custom error handler as soon as is practical.

If it becomes necessary to disable custom error handling in one of your procedures, you can do this with the On Error GoTo 0 statement. This statement restores standard Microsoft Excel error handling.

Your error-handling code can also be set up to write information to a text file (a log) so that you can analyze errors that occur during testing and/or usage. This strategy is very effective when you are getting bug reports that are tough to reproduce in your test environment.

The following error handling is more real-world and provides a much more realistic view of the process of handling errors. While it's simpler than most real-world code, it's much closer than the previous example. It contains a module level Public variable to pass error messages between procedures, and a function that returns True or False depending on different conditions.

Option Explicit
''' Passes error messages between procedures.
Public gszErrMsg As String  

Sub ApplicationMain()
    On Error GoTo ApplicationMainError
    ''' This function is used to open the workbook.
    If Not bOpenWorkbook("MyBook.xls") _
        Then GoTo ApplicationMainError
    ''' The rest of your add-in code goes here.
    Exit Sub
ApplicationMainError:
    On Error Resume Next
    ''' Do any necessary clean up operations here.
    MsgBox prompt:=gszErrMsg, Buttons:=vbCritical, _
    Title:="My Add-in"
End Sub

Function bOpenWorkbook(szName As String) As Boolean
    On Error Resume Next
    Workbooks.Open FileName:=szName
    If Err.Number <> 0 Then
        gszErrMsg = "Error opening workbook '" & _
            szName & "'."
        bOpenWorkbook = False
    Else
        bOpenWorkbook = True
    End If
End Function

In real-world code, error handling often needs to address cleanup issues that are beyond the scope of this chapter. Real error handling can be extremely complex due to the nature of stopping in the middle of ongoing code. The ideal learning tool is to view production-quality code available on many Web sites and study the developer's error-handling routines.