If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message like this:
But you can change this and instruct Excel to allow your code to deal with run-time errors.
NOTE : I’m going to use the terms sub, function and procedure interchangeably. For the purposes of this article they all mean the same thing – a chunk of code written to do a particular thing.
The On Error Statement
To instruct Excel what to do when an error occurs, you use the On Error statement. You can use On Error in four ways:
On Error GoTo 0 On Error Resume Next On Error GoTo [label] On Error GoTo -1
On Error GoTo 0
This is the default mode and is already turned on when you start writing your code. You don’t need to use an On Error GoTo 0 statement at the start of your VBA.
In this mode VBA displays the standard style error message box, and gives you the choice to Debug the code (enter VBA editor and use debugging tools) or End code execution.
You would use On Error GoTo 0 to turn default error handling back on if you have previously told VBA to deal with errors in some other way e.g. by using On Error Resume Next.
On Error Resume Next
On Error Resume Next tells VBA to continue executing statements immediately after the statement that generated the error.
On Error Resume Next allows your code to continue running even if an error occurs. Resume Next does not fix an error, it just ignores it. This can be good and bad.
If you know that your code could generate an error, then using Resume Next can prevent an interruption in code execution.
For example, we want to create a file, but I want to make sure a file with the same name doesn’t already exist. To do this, I will attempt to delete the file, and of course if it doesn’t already exist, an error will occur.
I don’t care if an error occurs. If it does, the file doesn’t exist and that’s fine for what I want to do. So before I attempt to delete the file, I instruct VBA to ignore the error.
Sub DeleteFile() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error Resume Next 'Delete the file Kill FilePath 'If the file doesn't exist the Kill statement 'generates an error. 'But I have assumed that is ok as it indicates that 'I am ok to create a new file with the same filename 'This is a bad assumption - see the next sub 'UnhandledError() End Sub
But hang on. What if the file I am trying to delete is read only? If it is I will get an error when I try to delete it. I’ve assumed that I will only get an error if the file isn’t there. So an error caused by trying to delete a read only file will get missed.
And If I then try to create a new file with the same name, or open it for writing data to it, I will generate more errors and they will be missed too.
Sub UnhandledError() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error Resume Next 'Delete the file Kill FilePath 'But if the file is read-only I can't delete it 'and the Kill statement generates an error 'I can't now create a new file with the same name 'Trying to Open the file for writing data to it will 'also generate an error but it will be missed as 'we've told VBA to continue executing code if an 'error occurs Open FilePath For Output As #1 Write #1, "Some data" Close #1 End Sub
If you do use On Error Resume Next you should immediately turn default error handling back on (or turn on your custom error handler – see below)
Be careful when using On Error Resume Next. You are better off seeing if an error occurred by checking the Err object (see below). Doing this can tell you the error number and help you figure out exactly what happened.
On Error GoTo [LABEL]
If an error occurs, this transfers code execution to the line following the label. This is typically used to specify your own error handling code.
None of the code between the line generating the error and the label is executed.
So you write your own error handling code and use On Error GoTo [LABEL] to instruct VBA to use your code to deal with errors.
You can place your error-handling code anywhere in a procedure, but typically it is placed at the end.
Your error handler should either fix the error and resume code execution, or terminate the routine gracefully.
Sub ErrorHandler() Dim num As Integer On Error GoTo ErrHandler num = 1 / 0 MsgBox "This line is not executed" Exit sub ErrHandler: MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description End Sub
As VBA will execute each line of code in turn going from top to bottom, if no error is generated then it will execute your error handling code when it gets to that point in your sub.
To prevent this happening, use an Exit Sub, Exit Function, or Exit Property statement before your error handling routine.
In the example above, if the value assigned to num was valid e.g. num = 1/1, then we don’t want the code beneath ErrHandler: executed. So just before the ErrHandler: label, I've used an Exit Sub statement.
Multiple Error Handlers
You can have more than one error handler in a routine, but only one of them can be active at any time.
You could have something like:
Sub MultipleErrorHandlers() On Error GoTo ErrHandler1 [some code] On Error GoTo ErrHandler2 [some code] Exit Sub ErrHandler1: [ErrHandler1 Code] Exit Sub ErrHandler2: [ErrHandler1 Code] Exit Sub End Sub
If an error occurs between On Error GoTo ErrHandler1 and On Error GoTo ErrHandler2 then the ErrHandler1 code is executed.
If an error occurs after On Error GoTo ErrHandler2 then the ErrHandler2 code is executed.
NOTE: Notice that at the end of each error handling routine is an Exit Sub statement. If I didn’t use these, when the ErrHandler1 code is finished executing, VBA could just continue on down to the next line and execute the ErrHandler2 code as well.
Strictly speaking I don’t need the Exit Sub at the end of the ErrHandler2 code, as it is the last line in the sub, but it is a good habit to get into.
When an error occurs the Err object contains information about the error like the error number and a description of the error.
As any given line of code can generate multiple errors it’s a good idea to examine the Err object to determine what you want to do in your code.
Err.Number gives you the error number, and Err.Description gives you a description of the error.
Sub CheckErrObject() Dim FilePath As String FilePath = "d:\temp\somefile.csv" On Error GoTo ErrHandler 'Delete the file Kill FilePath Open FilePath For Output As #1 Write #1, "Some data" Close #1 Exit Sub ErrHandler: Select Case Err.Number Case 53 ' File doesn't exist Err.Clear ' Clear the error Case 75 ' File is Read Only MsgBox "Error Number : " & Err.Number & vbCrLf & vbCrLf & Err.Description Exit Sub Case Else ' Code to handle other errors End Select Resume Next ' Continue executing code after line that generated error End Sub
The Resume statement tells VBA to resume executing code at a specified point. Resume can only be used in an error handling routine, any other use will generate an error.
Resume takes three forms:
Resume Resume Next Resume [label]
Using just Resume causes execution to resume at the same line of code that caused the error. If you haven’t fixed the error, your code will begin an infinite loop as it switches between the line of code generating the error and the error handling routine.
If you look at the example sub Resume_Next() which is below, num = 1 / 0 causes a Divide by 0 error. I’ve instructed VBA to use my error handler, called ErrHandler.
In ErrHandler I’ve attempted to fix the error by assigning num the value 1. If I then used only Resume, all that would happen is that VBA would go back to num = 1 / 0 and another Divide by 0 error would be generated.
Instead, I use Resume Next to carry on executing code at the line after the one causing the error.
In doing so I have handled the error by assigning the value 1 to num, and execution will continue without another error at the line result = num / 1
Sub Resume_Next() Dim num As Integer Dim result As Integer On Error GoTo ErrHandler num = 1 / 0 result = num / 1 Exit Sub ErrHandler: num = 1 Resume Next End Sub
Resume [label] passes code execution to the line with that label.
Sub Resume_Next() Dim num As Integer Dim result As Integer On Error GoTo ErrHandler num = 1 / 0 result = num / 1 MyLabel: ‘Code execution starts again from here result = num * 1 Exit Sub ErrHandler: num = 1 Resume MyLabel End Sub
Whenever you use Resume it clears the Err object.
Error Handling With Multiple Procedures
Every sub/function doesn’t have to have an error handler. If an error occurs, VBA will use the last On Error statement to determine what happens.
If an On Error statement has been used in a procedure and an error occurs in that procedure, then that error is handled as I've just described.
But if an error occurs in a sub that hasn’t used an On Error statement, VBA goes back through procedure calls until it finds an On Error directive.
Let’s look at an example with three subs.
SubOne() calls SubTwo(). SubTwo calls SubThree(), and has some code of its own to execute. SubThree() carries out a calculation.
Sub SubOne() On Error GoTo ErrHandler SubTwo Exit Sub ErrHandler: MsgBox "Error caught in SubOne" End Sub Sub SubTwo() SubThree MsgBox "No errors here" End Sub Sub SubThree() Dim num As Integer num = 1 / 0 End Sub
SubOne() has an error handler routine, ErrHandler, and has instructed VBA to use it.
SubTwo() will display a message on screen after it’s call to SubThree() has finished.
However SubThree() generates a Divide by 0 error.
SubThree() hasn’t used an On Error statement to tell VBA what to do if an error occurs, so VBA goes back to SubTwo(). That also doesn’t have an On Error statement so VBA goes back to SubOne().
Here we have our error handler and the code in it is executed.
Note that the message "No errors here" in SubTwo() is not displayed because that line of code is not executed.
When SubThree() generated an error, code execution went back to the error handler in SubOne() and any code in SubTwo() after the call to SubThree() is missed out.
On Error GoTo -1
This resets the current error. It’s the equivalent of using Err.Clear.
You can see that in this sub, after the Divide By 0 error is generated, after On Error GoTo -1 is used, Err.Number is 0 (no error) and Err.Description is empty.
Sub GoToMinus1() Dim num As Integer On Error Resume Next num = 1 / 0 'If error is Divide by Zero If Err.Number = 11 Then MsgBox "Error Code : " & Err.Number & " , " & Err.Description On Error GoTo -1 MsgBox "Error Code : " & Err.Number & " , " & Err.Description End If End Sub
Download this workbook for all the sample code used here plus several other examples.
If you liked this or know someone who makes a lot of mistakes in their VBA 🙂 , please click the buttons below to share.