Error Handlers for Excel Macros

By Michael Alexander

Error handlers allow you to specify what happens when an error is encountered while your Excel macro code runs. Error handlers are lines similar to the following:

On Error GoTo MyError

Without error handlers, any error that occurs in your code will prompt Excel to activate a less-than-helpful error message that typically won’t clearly convey what happened. However, with the aid of error handlers, you can choose to ignore the error or exit the code gracefully with your own message to the user.

There are three types of On Error statements:

  • On Error GoTo SomeLabel: The code jumps to the specified label.

  • On Error Resume Next: The error is ignored and the code resumes.

  • On Error GoTo 0: VBA resets to normal error-checking behavior.

On Error GoTo SomeLabel

Sometimes an error in your code means you need to gracefully exit the procedure and give your users a clear message. In these situations, you can use the On Error GoTo statement to tell Excel to jump to a certain line of code.

For example, in the following small piece of code, you tell Excel to divide the value in cell A1 by the value in cell A2, and then place the answer in cell A3. Easy. What could go wrong?

Sub Macro1()
Range("A3").Value = Range("A1").Value / Range("A2").Value
End Sub

As it turns out, two major things can go wrong. If cell A2 contains 0, you get a divide by 0 error. If cell A2 contains a non-numeric value, you get a type mismatch error.

To avoid a nasty error message, you can tell Excel that On Error, you want the code execution to jump to the label called MyExit.

In the following code, the MyExit label is followed by a message to the user that gives friendly advice instead of a nasty error message. Also note the Exit Sub line before the MyExit label, which ensures that the code will simply exit if no error is encountered:

Sub Macro1()
On Error GoTo MyExit
Range("A3").Value = Range("A1").Value / Range("A2").Value
Exit Sub
MyExit:
MsgBox "Please Use Valid Non-Zero Numbers"
End Sub

On Error Resume Next

Sometimes, you want Excel to ignore an error and simply resume running the code. In these situations, you can use the On Error Resume Next statement.

For example, the following piece of code is meant to delete a file called GhostFile.exe from the C:Temp directory. After the file is deleted, a nice message box tells the user that the file is gone:

Sub Macro1()
Kill "C:TempGhostFile.exe"
MsgBox "File has been deleted."
End Sub

The code works great if there is indeed a file to delete. But if for some reason the file called GhostFile.exe does not exist in the C:Temp drive, an error is thrown.

In this case, you don’t care if the file is not there because you were going to delete it anyway. So you can simply ignore the error and move on with the code.

By using the On Error Resume Next statement, the code runs its course whether or not the targeted file exists:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
MsgBox "File has been deleted."
End Sub

On Error GoTo 0

When using certain error statements, it may be necessary to reset the error-checking behavior of VBA. To understand what this means, take a look at the next example.

Here, you first want to delete a file called GhostFile.exe from the C:Temp directory. To avoid errors that may stem from the fact that the targeted file does not exist, you use the On Error Resume Next statement. After that, you try to do some suspect math by dividing 100/Mike:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
Range("A3").Value = 100 / "Mike"
End Sub

Running this piece of code should generate an error due to the fuzzy math, but it doesn’t. Why? Because the last instruction you gave to the code was On Error Resume Next. Any error encountered after that line is effectively ignored.

To remedy this problem, you can use the On Error GoTo 0 statement to resume normal error-checking behavior:

Sub Macro1()
On Error Resume Next
Kill "C:TempGhostFile.exe"
On Error GoTo 0
Range("A3").Value = 100 / "Mike"
End Sub

This code will ignore errors until the On Error GoTo 0 statement. After that statement, the code goes back to normal error checking and triggers the expected error stemming from the fuzzy math.