20.3 Errors

Sometimes errors occur in VBA. There are two main types of errors: syntax errors and run-time errors. With syntax errors, VBA will not let you run your code until they are fixed. These errors range from misspelled variables and control flow commands to missing parameters, incorrect operators, or mismatched parentheses. While there are lots of different ways to introduce syntax errors, they are relatively easy to fix because Excel will notice them and give you some idea of how to address the issue.

Run-time errors are more interesting because they don’t show up until your program is running. Anticipating what the user will do and how they could mess up your function is a creative task. The code we write should be resilient to major errors. Understanding how to handle errors is an important skill to acquire if you will be doing a fair amount of VBA programming.

Excel’s handling of errors is pretty basic. You can tell Excel to ignore all errors, to stop if it finds an error, or to go to a specific label if an error is detected in a certain section of code. To tell Excel to ignore errors and continue on in the code, add the following code snippet.

On Error Resume Next

Use the following snippet to reset your error handling; the code will stop and display an error as it occurs.

On Error GoTo 0

If you want a section of code to execute when an error is encountered, you can tell it to go to a different section of your code by defining a label and using the following snippet.

On Error GoTo [Label]

Here, the label would be the name of a label you introduce into your code. It may look something like the following code.

Sub ThrowAnError()
     On Error GoTo ErrorHandler
    
     Dim num1 As Integer, num2 As Integer
    
     num1 = 37
     num2 = num1 / 0
     num2 = num2 * 3
    
     Exit Sub
ErrorHandler:
     MsgBox "The following error occurred: " & Err.Description
End Sub

Notice the label called ErrorHandler at the bottom of the sub procedure. A block of code can have multiple labels. The name of each is followed by a colon and then one or more lines of code. Here, if we run into an error, we will display a message box with a description of the error. The Err object is a VBA object that contains a number and description of the latest error encountered.

By adding the On Error GoTo ErrorHandler, Excel will jump directly to the ErrorHandler if an error is encountered. The name could have been anything, but ErrorHandler is fairly descriptive. Go ahead and try to run this code. Once you get it to display the message box, try changing the error handling to ignore errors. You will want to think about how you can use error handling in VBA to make your programs and functions more resilient.