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.
The Good?
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
The Bad
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.
Error Handlers
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.
Err object
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
Resume
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
Sample Code
Enter your email address below to download the sample workbook.
Download this workbook for all the sample code used here plus several other examples.
John Mark
Nice explanation! Good clear examples! Thanks
Philip Treacy
Thanks John, glad you found it useful.
Regards
Phil
Alan Elston
Hi Philip
Error handling in VBA doesn’t seems to be fully understood by almost anyone, and there are not many Blogs on it, so this Blog of yours is a useful Blog to have.
I am just slightly put off by your last bit about the On Error GoTo -1
In particular, your statement …”… This resets the current error. It’s the equivalent of using Err.Clear….”
I expect you didn’t mean to say they are similar things, but it might be read like that by the unwary….
Here is my take on it, mostly what I have got from experimenting….
On Error GoTo -1 takes Excel out of the so called “exception state”. It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of “clearing the exception”.
The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ Resume, ( Resume; Resume Next; Resume [label] ) ,
_ changing the error handler
So those 3(5) things have the same effect on, and the same relevance to, Err.Clear as On Error GoTo -1 does/ is. They all do like Err.Clear as a secondary thing in addition to their main thing.
It is a common mistake to see Err.Clear being regarded as the same as, or similar to, On Error GoTo -1
On Error GoTo -1 is not equivalent of using Err.Clear. It does ( also ) clear the error object, (equivalent of using Err.Clear ).
It seems that every time I answer a forum question or make a comment or read a Blog on error handling in VBA, I find some other small feature of it that I had previously missed.
It seems that to fully understand error handling in VBA, it needs an explanation that is too long for a Blog, but possibly too small for a book. I am not sure yet if anyone fully understands the subject.
Chip Pearson and a few others who followed his examples have completely ignored the On Error GoTo -1 and exception state issue, although he did touch on the exception state in his comment to your Blog….
There is an awful lot of similar mistakes in coding caused by not understanding all the issues, in particular caused by not considering the effects of the “exception state”.
Missing that last bit of important understanding seems to cause almost everybody to trip up, and end up getting something wrong, even some of the best experts.
Alan
Alan Elston
P.S.
I can go a long way in explaining what I was getting at with a very simple example. This following example has solved a similar Forum question that I must have answered now about a hundred times.
The mistake it illustrates is made by newbies and experienced professionals alike:
The code snipped below simulates a typical coding problem whereby somebody suddenly notices that it’s not handling an error more than once.
At first glance one might expect that this macro will loop 10 times, thereby handling an error 10 times.. It doesn’t. On the second loop, the default VBA error handler pops up and code execution stops…
Sub OnlyTrapOneError()
Dim Rslt As Double, Cnt As Long
For Cnt = 1 To 10
‘ Some coding not expected to error
‘
On Error GoTo sKip ‘ I might be expecting the next code line to error, so I have introduced my error handling to handle it
Let Rslt = 1 / 0 ‘
‘
sKip:
On Error GoTo 0 ‘ I am not expecting any more errors , so good practice is to remove my error handling
‘
‘ Some other coding not expected to error
Next Cnt
End Sub
That simple macro is using good coding practice in that it only uses ( enables ) the error handler when it might need it. In other words we “turn it on” with
On Error GoTo sKip
and “turn it off” ( return to default VBA error handling) with
On Error GoTo 0
So far so good.
But the problem is that mostly when an error occurs, VBA coding goes into what is referred to as an “exception state”. The two main characteristics of the “exception state” are:
_ In this state further errors are handled by the default VBA error handler
_ Any attempts to enable another error handler are ignored.
( We can say those two things in a more simple way: VBA is busy in a state handling the first error and cant handle more than one at a time…)
One solution to the problem is to also include an
On Error GoTo -1
A good place for it would be before ,or after, the On Error GoTo 0
It makes no difference if it’s before or after: those two On Error statements don’t effect each other in any way***.
An alternative solution would be just to replace the On Error GoTo 0 with On Error GoTo -1. But that is slightly less good practice, since we have are error handler active for more of the coding than we probably need it.
BTW, if we did chose to just replace the On Error GoTo 0 with On Error GoTo -1, then we could move the On Error GoTo Skip to outside the Loop, so it’s only done once.
This last solution would work because On Error GoTo -1 does not disable the error handler:
On Error GoTo 0 disables the error handler, ( and BTW, On Error GoTo 0 does this in the normal or “exception state” ***)
_._______
In a recent forum post, the OP who I explained this all to came up with a simple summary I liked:
on error goto 0 — disables error trapping but does not reset the error state. Any new error trapping is ignored until the state is reset
on error goto -1 — resets the error state, restoring error trapping as it was before the error occurred
_.______
I should say finally that I agree with most people that one should try to avoid using error handling in coding to solve a possible expected problem , and instead use, if at all possible some other way that does not invlove error handling.
Run time error handling in VBA should be used intelligently. That is rather difficult in the practice since very few people, not even some the most experienced professionals, fully understand it.
Alan
Chip Pearson
This is a fine article on error handling in VBA. Most people just put “On Error Resume Next” at the top of the procedure and assume that any errors will be fixed automatically. A very bad assumption, but I’ve seen it more times than I like to think. There are a few minor points that I would add to your article. The first is that when code execution drops into an error handling block (e.,g “On Error Goto ErrHandler”), ALL error handling within the error handler is turned off, and if an run-time error occurs within the error handling block, you get an untrappable run-time error. For example, examine the following code.
Sub AAA()
Dim X As Long
Dim Y As Long
On Error GoTo ErrHandler
X = 10
Y = 0
10: Debug.Print X / Y
Exit Sub
20:
ErrHandler:
Debug.Print "Last successful label: " & Erl
On Error Resume Next
30: Debug.Print X / Y
Resume
End Sub
The code will raise an Div/0 error at line 10, at which execution transfers to
ErrHandler:
. VBA is now running in “error mode” and the Div/0 error at line 30 cannot be trapped. It will always raise an untrappable error. The code above also illustrates the use of the little-knownErl
statement. Its value is updated every time a numeric line label is encountered, and can be read in an error handling block to indicate the last successful line label. In the code above, the debug message will show that line 10 was that last successfully labeled line. I put line labels to separate logically connected blocks of code, so I can easily see more or less where the error arose. There are any number of free add-ins that will put a line number on every line of code, and Erl will then identify the exact line of code. I think this is overkill. I put in a line label every 20 or so lines, depending on the structure of the code. Note that the line labels must be numeric (e.g,10:
.Erl
will not recognize alphabetic labels (e.g,.ErrHandler:
.Next, should ALWAYS ALWAYS ALWAYS set error handling in the Options dialog to Break In Class Module. Suppose you have code to show a user form:
UserForm1.Show
If a run time error occurs within UserForm1’s object module, the debugger will highlight the line above as the source of the error. Clearly, this line is not an error, and no matter how many hours you stare at that line, you won’t find an error. But if you set error handling to Break In Class Module, the debugger will take you inside UserForm1’s object module directly to the line of code that really is the source of the error.
Another technique I use when the code calls procedures that call other procedures and so on, whose error handling is indecipherable, is to put an error handler in the procedure that initiates then entire chain of procedures. For example,
Sub AAA()
On Error GoTo ErrHandler:
BBB
Exit Sub
ErrHandler:
Debug.Print "Last Chance Handler"
End Sub
Sub BBB()
CCC
End Sub
Sub CCC()
DDD
End Sub
Sub DDD()
Debug.Print 1 / 0
End Sub
Here, the error handler in proc AAA will take over if there is no other error handling. This is especially useful if you have to use code that you did not write yourself. All it does is provide a last chance error handler to prevent an untrapped error dialog. It doesn’t do anything except prevent the untrapped error dialog from popping up. An untrappped error dialog tells the world you don’t know what you are doing, and if you are a consultant, you’ll likely lose the client over that one dialog box. It provides a graceful and non-threatening way to handle an error whose source over which you have no control.
I just thought you would find this interesting and I think it would be a worthwhile addition to your fine article on error handling. I usually code in VBNET or C# and use
Try/Catch/Finally
structures for error handling, and when I have to go into VBA,On Error
in all its variations seems extremely primitive.Philip Treacy
Hi Chip,
Thanks for reading the article and leaving such a long and detailed reply, much appreciated.
You make good points about both the un-trappable errors and enabling Break In Class Module. Nothing worse than being shown a line of code that hasn’t actually generated the error.
In the section ‘Error Handling With Multiple Procedures’ I tried to convey what you are saying about having a ‘last chance’ error handler, but I like your example of doing this if using code you didn’t write yourself.
I write a fair bit of PHP so I am familiar with Try/Catch and having that would indeed be a big improvement for VBA.
Cheers
Phil
SD
Great article. It helped me a lot with handling errors in my code.
I am a newbie and don’t have much knowledge about VBA. I am working on a module that throws an error inside the error handler. Can you guys please elaborate on how to handle if an run-time error occurs within the error handling block. I thought another error handler inside the first error handler would help but as Chip said it’s not going to work.
I am trying to scrape prices and there are more than 2 condition for scraping it. 1st one is taken care by the ‘ErrHandler1’ but I can’t get though how to make my code skip the error in ‘ErrHandler1’ and execute the code in ‘ErrHandler2’. It would be great if you guys can help me out.
Philip Treacy
Hi,
Hard to give you advice without seeing the code and recreating the error.
Please post a topic on our forum and attach your workbook with a description of how the error is generated.
Regards
Phil
Joyce Swensson
Thank you for this. I am working on improving error handling in my code. It seems to me it might be helpful to have an “error log” created that captures the values of various variables, the error number and description etc. Hopefully, I can figure this out.
Philip Treacy
Hi Joyce,
I guess in an ideal world you’d be able to anticipate any errors and deal with them in your error handling routines. But for unforeseen circumstances, you could write the values of variables and any error data to a file and use that as your log.
Regards
Phil