VBA Chapter 11 of 24: Error Handling in VBA for Excel

Handling VBA Errors

Here is the basic way to handle an error.   The first thing you create is an address where VBA will jump if there is an error. In this example the address is addJump with the NOT OPTIONAL colon at the end. Below addJump is what is supposed to happen of there is an error and above is the procedure that should run if there are no errors. Notice that the last line of code in the section above addJump is Exit Sub because if there are no errors you don't want the rest of the code to be executed.

Example 1:

Sub proTestErrorHandler()

        On Error GoTo addJump
        Workbooks.Open "xxxxxx"
        Exit Sub

addJump:

        MsgBox "An error has occurred, call Peter at 1 514-257-0734"

End Sub

Example 2:

Sub proTestErrorHandlerNoError()

        On Error GoTo addJump
        MsgBox "Everything is fine."
        Exit Sub

addJump:

        MsgBox "An error has occurred, call Peter at 1 514-257-0734"

End Sub

Copy/Paste both examples in a module of your own and run them. The first example will generate a message box saying "An error has occurred, call Peter at 1 514-257-0734"  because an error has occurred. The workbook xxxxxx can't be found. The second example will generate a message box saying "Everything is fine".

If you run the procedure without the error handler:

Sub proTestErrorHandler()

          Workbooks.Open "xxxxxx"

End Sub

You end up with the following message box from VBA.

If you just want errors to be ignore you write On Error Resume Next  at the beginning  of the procedure. Copy/Paste the following procedure in a module of your own and run it. It will generate a message box saying An error has occurred but we have ignored  it.

Sub proTestErrorHandlerIgnore()

          On Error Resume Next
          Workbooks.Open "xxxxxx"
          MsgBox "An error has occurred but we have ignored  it."

End Sub

Next Chapter: VBA for Excel Code for the Application

VBA Table of Contents
or use the quick links below

Discover Even More in 50 Excel spreadsheets