Error Handlers for Excel Macros
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.