“Abort, Retry, Fail?” – MS-DOS error message circa 1986
This post provides a complete guide to VBA Error Handing. If you are looking for a quick summary then check out the quick guide table in the first section.
If you are looking for a particular topic on VBA Error Handing then check out the table of contents below(if it’s not visible click on the post header).
If you are new to VBA Error Handling, then you can read the post from start to finish as it is laid out in logical order.
- 1 A Quick Guide to Error Handing
- 2 The Webinar
- 3 Download the Error Handling Library
- 4 Introduction
- 5 VBA Errors
- 6 The On Error Statement
- 7 Resume Next
- 8 The Err Object
- 9 Logging
- 10 Other Error Related Items
- 11 A Simple Error Handling Strategy
- 12 A Complete Error Handling Strategy
- 13 Error Handling in a Nutshell
- 14 What’s Next?
A Quick Guide to Error Handing
|On Error Goto 0||When error occurs, the code stops and displays the error.|
|On Error Goto -1||Clears the current error setting and reverts to the default.|
|On Error Resume Next||Ignores the error and continues on.|
|On Error Goto [Label]||Goes to a specific label when an error occurs.
This allows us to handle the error.
|Err Object||When an error occurs the error information is stored here.|
|Err.Number||The number of the error.
(Only useful if you need to check a specific error occurred.)
|Err.Description||Contains the error text.|
|Err.Source||You can populate this when you use Err.Raise.|
|Err.Raise||A function that allows you to generate your own error.|
|Error Function||Returns the error text from an error number.
|Error Statement||Simulates an error. Use Err.Raise instead.|
Members of the Webinar Archives can access the webinar for this article by clicking on the image below.
(Note: Archive members have access to the webinar archive.)
Download the Error Handling Library
Error Handling refers to code that is written to handle errors which occur when your application is running. These errors are normally caused by something outside your control like a missing file, database being unavailable, data being invalid etc.
If we think an error is likely to occur at some point, it is good practice to write specific code to handle the error if it occurs and deal with it.
For all other errors, we use generic code to deal with them. This is where the VBA error handling statement comes into play. They allow our application to deal gracefully with any errors we weren’t expecting.
To understand error handling we must first understand the different types of errors in VBA.
There are three types of errors in VBA:
We use error handling to deal with runtime errors. Let’s have a look at each of these error types so that it is clear what a runtime error is.
If you have used VBA for any length of time you will have seen a syntax error. When you type a line and press return, VBA will evaluate the syntax and if it is not correct it will display an error message.
For example if you type If and forget the Then keyword, VBA will display the following error message
Some examples of syntax errors are
' then is missing If a > b ' equals is missing after i For i 2 To 7 ' missing right parenthesis b = left("ABCD",1
Syntax errors relate to one line only. They occur when the syntax of one line is incorrect.
Note: You can turn off the Syntax error dialog by going to Tools->Options and checking off “Auto Syntax Check”. The line will still appear red if there is an error but the dialog will not appear.
Compilation errors occur over more than one line. The syntax is correct on a single line but is incorrect when all the project code is taken into account.
Examples of compilation errors are:
- If statement without corresponding End If statement
- For without Next
- Select without End Select
- Calling a Sub or Function that does not exist
- Calling a Sub or Function with the wrong parameters
- Giving a Sub or Function the same name as a module
- Variables not declared(Option Explicit must be present at the top of the module)
The following screenshot shows a compilation error that occurs when a For loop has no matching Next statement.
To find compilation errors, we use Debug->Compile VBA Project from the Visual Basic menu.
When you select Debug->Compile, VBA displays the first error it comes across.
When this error is fixed, you can run Compile again and VBA will then find the next error.
Debug->Compile will also include syntax errors in it’s search which is very useful.
If there are no errors left and you run Debug->Compile , it may appear that nothing happened. However, “Compile” will be grayed out in the Debug menu. This means your application has no compilation errors at the current time.
Debug->Compile Error Summary
- Debug->Compile finds compilation(project wide) errors.
- It will also find syntax errors.
- It finds one error each time you use it.
- When there are no compilation errors left the Compile option will appear grayed out in the menu.
You should always use Debug->Compile before you run your code. This ensures that your code has no compilation errors when you run it.
If you do not run Debug->Compile then VBA may find compile errors when it runs. These should not be confused with Runtime errors.
Runtime errors occur when your application is running. They are normally outside of your control but can be caused by errors in your code.
For example, imagine your application reads from an external workbook. If this file gets deleted then VBA will display an error when your code tries to open it.
Other examples of runtime errors are
- a database not being available
- the user entering invalid data
- a cell containing text instead of a number
As we have seen, the purpose of error handling is to deal with runtime errors when they occur.
Expected Versus Unexpected Errors
When we think a runtime error could occur we put code in place to handle it. For example, we would normally put code in place to deal with a file not being found.
The following code checks if the file exists before it tries to open it. If the file does not exist then a user friendly message is displayed and the code exits the sub.
' https://excelmacromastery.com/ Sub OpenFile() Dim sFile As String sFile = "C:\docs\data.xlsx" ' Use Dir to check if file exists If Dir(sFile) = "" Then ' if file does not exist display message MsgBox "Could not find the file " & sFile Exit Sub End If ' Code will only reach here if file exists Workbooks.Open sFile End Sub
When we think an error is likely to occur at some point, it is good practice to add code to handle the situation. We normally refer to these errors as expected errors.
If we don’t have specific code to handle an error it is considered an unexpected error. We use the VBA error handling statements to handle the unexpected errors.
Runtime Errors that are not VBA Errors
Before we look at the VBA Handling there is one type of error we must mention. Some runtime errors are not considered errors by VBA but only by the user.
Let me explain this with an example. Imagine you have an application that requires you to add the values in the variables a and b
result = a + b
Let’s say you mistakenly use an asterisk instead of the plus sign
result = a * b
This is not a VBA error. Your code syntax is perfectly legal. However, from your requirements point of view it is an error.
These errors cannot be dealt with using error handling as they obviously won’t generate any error. You can deal with these errors using Unit Testing and Assertions. I have an in-depth post about using VBA assertions – see How to Make Your Code BulletProof.
The On Error Statement
As we have seen there are two ways to treat runtime errors
- Expected errors – write specific code to handle them.
- Unexpected errors – use VBA error handling statements to handle them.
The VBA On Error statement is used for error handling. This statement performs some action when an error occurs during runtime.
There are four different ways to use this statement
- On Error GoTo 0 – the code stops at the line with the error and displays a message.
- On Error Resume Next – the code moves to next line. No error message is displayed.
- On Error GoTo [label] – the code moves to a specific line or label. No error message is displayed. This is the one we use for error handling.
- On Error GoTo -1 – clears the current error.
Let’s look at each of these statements in turn.
On Error GoTo 0
This is the default behavior of VBA. In other words, if you don’t use On Error then this is the behavior you will see.
When an error occurs, VBA stops on the line with the error and displays the error message. The application requires user intervention with the code before it can continue. This could be fixing the error or restarting the application. In this scenario no error handling takes place.
Let’s look at an example. In the following code, we have not used any On Error line so VBA will use the On Error GoTo 0 behavior by default.
' https://excelmacromastery.com/ Sub UsingDefault() Dim x As Long, y As Long x = 6 y = 6 / 0 x = 7 End Sub
The second assignment line results in a divide by zero error. When we run this code we will get the error message shown in the screenshot below
When the error appears you can choose End or Debug
If you select End then the application simply stops.
If you select Debug the application stops on the error line as the screenshot below shows
This behaviour is fine when you are writing VBA code as it shows you the exact line with the error.
This behavior is unsuitable for an application that you are given to a user. These errors look unprofessional and they make the application look unstable.
An error like this is essentially the application crashing. The user cannot continue on without restarting the application. They may not use it at all until you fix the error for them.
By using On Error GoTo [label] we can give the user a more controlled error message. It also prevents the application stopping. We can get the application to perform in a predefined manner.
On Error Resume Next
Using On Error Resume Next tells VBA to ignore the error and continue on.
There are specific occasions when this is useful. Most of the time you should avoid using it.
If we add Resume Next to our example Sub then VBA will ignore the divide by zero error
' https://excelmacromastery.com/ Sub UsingResumeNext() On Error Resume Next Dim x As Long, y As Long x = 6 y = 6 / 0 x = 7 End Sub
It is not a good idea to do this. If you ignore the error, then the behavior can be unpredictable. The error can affect the application in multiple ways.You could end up with invalid data. The problem is that you aren’t aware that something went wrong because you have suppressed the error.
The code below is an example of where using Resume Next is valid
' https://excelmacromastery.com/ Sub SendMail() On Error Resume Next ' Requires Reference: ' Microsoft Outlook 15.0 Object Library Dim Outlook As Outlook.Application Set Outlook = New Outlook.Application If Outlook Is Nothing Then MsgBox "Cannot create Microsoft Outlook session." _ & " The email will not be sent." Exit Sub End If End Sub
In this code we are checking to see if Microsoft Outlook is available on a computer. All we want to know is if it is available or not. We are not interested in the specific error.
In the code above, we continue on if there is an error. Then in the next line we check the value of the Outlook variable. If there has been an error then the value of this variable will be set to Nothing.
This is an example of when Resume could be useful. The point is that even though we use Resume we are still checking for the error. The vast majority of the time you will not need to use Resume.
On Error GoTo [label]
This is how we use Error Handling in VBA. It is the equivalent of the Try and Catch functionality you see in languages such as C# and Java.
When an error occurs you send the error to a specific label. It is normally at the bottom of the sub.
Let’s apply this to the sub we have been using
' https://excelmacromastery.com/ Sub UsingGotoLine() On Error GoTo eh Dim x As Long, y As Long x = 6 y = 6 / 0 x = 7 Done: Exit Sub eh: MsgBox "The following error occurred: " & Err.Description End Sub
The screenshot below shows what happens when an error occurs
VBA jumps to the eh label because we specified this in the On Error Goto line.
Note 1: The label we use in the On…GoTo statement, must be in the current Sub/Function. If not you will get a compilation error.
Note 2: When an error occurs when using On Error GoTo [label], the error handling returns to the default behaviour i.e. The code will stop on the line with the error and display the error message. See the next section for more information about this.
On Error GoTo -1
This statement is different than the other three. It is used to clear the current error rather than setting a particular behaviour.
When an error occurs using On Error GoTo [label], the error handling behaviour returns to the default behaviour i.e. “On Error GoTo 0”. That means that if another error occurs the code will stop on the current line.
This behaviour only applies to the current sub. Once we exit the sub, the error will be cleared automatically.
Take a look at the code below. The first error will cause the code to jump to the eh label. The second error will stop on the line with the 1034 error.
' https://excelmacromastery.com/ Sub TwoErrors() On Error Goto eh ' generate "Type mismatch" error Error (13) Done: Exit Sub eh: ' generate "Application-defined" error Error (1034) End Sub
If we add further error handling it will not work as the error trap has not been cleared.
In the code below we have added the line
On Error Goto eh_other
after we catch the first error.
This has no effect as the error has not been cleared. In other words the code will stop on the line with the error and display the message.
' https://excelmacromastery.com/ Sub TwoErrors() On Error Goto eh ' generate "Type mismatch" error Error (13) Done: Exit Sub eh: On Error Goto eh_other ' generate "Application-defined" error Error (1034) Exit Sub eh_other: Debug.Print "eh_other " & Err.Description End Sub
To clear the error we use On Error GoTo -1. Think of it like setting a mouse trap. When the trap goes off you need to set it again.
In the code below we add this line and the second error will now cause the code to jump to the eh_other label
' https://excelmacromastery.com/ Sub TwoErrors() On Error Goto eh ' generate "Type mismatch" error Error (13) Done: Exit Sub eh: ' clear error On Error Goto -1 On Error Goto eh_other ' generate "Application-defined" error Error (1034) Exit Sub eh_other: Debug.Print "eh_other " & Err.Description End Sub
Note 1: There are probably rare cases where using On Error GoTo -1 is useful. In most cases using Resume Next is better as it clears the error and resumes the code at the next line after the error occurs.
Note 2: The Err Object has a member Clear. Using Clear clears the text and numbers in the Err object, but it does NOT reset the error.
Using On Error
As we have seen, VBA will do one of three things when an error occurs
- Stop and display the error.
- Ignore the error and continue on.
- Jump to a specific line.
VBA will always be set to one of these behaviors. When you use On Error, VBA will change to the behaviour you specify and forget about any previous behavior.
In the following Sub, VBA changes the error behaviour each time we use the On Error statement
' https://excelmacromastery.com/ Sub ErrorStates() Dim x As Long ' Go to eh label if error On Error Goto eh ' this will ignore the error on the following line On Error Resume Next x = 1 / 0 ' this will display an error message on the following line On Error Goto 0 x = 1 / 0 Done: Exit Sub eh: Debug.Print Err.Description End Sub
The Resume Next statement is used to clear the error and then resume the code from the line after where the error occurred.
If your code can have multiple errors and you want to keep detecting them then this line is very useful.
For example, in the following code we want to resume the code after the error has been reported:
Private Sub Main() On Error Goto eh Dim i As Long For i = 1 To 3 ' Generate type mismatch error Error 13 Next i done: Exit Sub eh: Debug.Print i, Err.Description End Sub
We could use On Error Goto -1 to clear the code and then use a goto statement to go back to the code like this:
Private Sub Main() On Error Goto eh Dim i As Long For i = 1 To 3 ' Generate type mismatch error Error 13 continue: Next i done: Exit Sub eh: Debug.Print i, Err.Description On Error Goto -1 ' clear the error Goto continue ' return to the code End Sub
The Resume Next provides a nicer way of doing it and it always means the code is much clearer and easier to understand:
Private Sub Main() On Error Goto eh Dim i As Long For i = 1 To 3 ' Generate type mismatch error Error 13 continue: Next i done: Exit Sub eh: Debug.Print i, Err.Description ' clear the error and return to the code Resume Next End Sub
The Err Object
When an error occurs you can view details of the error using the Err object.
When an runtime error occurs, VBA automatically fills the Err object with details.
The code below will print “Error Number: 13 Type Mismatch” which occurs when we try to place a string value in the long integer total
' https://excelmacromastery.com/ Sub UsingErr() On Error Goto eh Dim total As Long total = "aa" Done: Exit Sub eh: Debug.Print "Error number: " & Err.Number _ & " " & Err.Description End Sub
The Err.Description provides details of the error that occurs. This is the text you normally see when an error occurs e.g. “Type Mismatch”
The Err.Number is the ID number of the error e.g. the error number for “Type Mismatch” is 13. The only time you really need this is if you are checking that a specific error occurred and this is only necessary on rare occasions.
The Err.Source property seems like a great idea but it does not work for a VBA error. The source will return the project name, which hardly narrows down where the error occurred. However, if you create an error using Err.Raise you can set the source yourself and this can be very useful.
Getting the Line Number
The Erl function is used to return the line number where the error occurs.
It often causes confusion. In the following code, Erl will return zero
' https://excelmacromastery.com/ Sub UsingErr() On Error Goto eh Dim val As Long val = "aa" Done: Exit Sub eh: Debug.Print Erl End Sub
This is because there are no line numbers present. Most people don’t realise it but VBA allows you to have line numbers.
If we change the Sub above to have line number it will now print out 20
' https://excelmacromastery.com/ Sub UsingErr() 10 On Error Goto eh Dim val As Long 20 val = "aa" Done: 30 Exit Sub eh: 40 Debug.Print Erl End Sub
Adding line numbers to your code manually is cumbersome. However there are tools available that will allow you to easily add and remove line numbers to a sub.
When you are finished working on a project and hand it over to the user it can be useful to add line numbers at this point. If you use the error handling strategy in the last section of this post, then VBA will report the line where the error occurred.
Err.Raise allows us to create errors. We can use it to create custom errors for our application which is very useful. It is the equivalent of the Throw statement in Java\C#.
The format is as follows
Err.Raise [error number], [error source], [error description]
Let’s look at a simple example. Imagine we want to ensure that a cell has an entry that has a length of 5 characters. We could have a specific message for this
' https://excelmacromastery.com/ Public Const ERROR_INVALID_DATA As Long = vbObjectError + 513 Sub ReadWorksheet() On Error Goto eh If Len(Sheet1.Range("A1")) <> 5 Then Err.Raise ERROR_INVALID_DATA, "ReadWorksheet" _ , "The value in the cell A1 must have exactly 5 characters." End If ' continue on if cell has valid data Dim id As String id = Sheet1.Range("A1") Done: Exit Sub eh: ' Err.Raise will send code to here MsgBox "Error found: " & Err.Description End Sub
When we create an error using Err.Raise we need to give it a number. We can use any number from 513 to 65535 for our error. We must use vbObjectError with the number e.g.
Err.Raise vbObjectError + 513
Err.Clear is used to clear the text and numbers from the Err.Object. In other words, it clears the description and number.If you want the clear the actual error you can use either On Error GoTo -1 or Resume Next
It is rare that you will need to use Err.Clear but let’s have a look at an example where you might.
In the code below we are counting the number of errors that will occur. To keep it simple we are generating an error for each odd number.
We check the error number each time we go through the loop. If the number does not equal zero then an error has occurred. Once we count the error we need to set the error number back to zero so it is ready to check for the next error.
' https://excelmacromastery.com/ Sub UsingErrClear() Dim count As Long, i As Long ' Continue if error as we will check the error number On Error Resume Next For i = 0 To 9 ' generate error for every second one If i Mod 2 = 0 Then Error (13) ' Check for error If Err.Number <> 0 Then count = count + 1 Err.Clear ' Clear Err once it is counted End If Next Debug.Print "The number of errors was: " & count End Sub
Note 1: Err.Clear resets the text and numbers in the error object but it does not clear the error – see Resume Next Or On Error GoTo -1 for more information about clearing the actual error.
Logging means writing information from your application when it is running. When an error occurs you can write the details to a text file so you have a record of the error.
The code below shows a very simple logging procedure
' https://excelmacromastery.com/ Sub Logger(sType As String, sSource As String, sDetails As String) Dim sFilename As String sFilename = "C:\temp\logging.txt" ' Archive file at certain size If FileLen(sFilename) > 20000 Then FileCopy sFilename _ , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt")) Kill sFilename End If ' Open the file to write Dim filenumber As Variant filenumber = FreeFile Open sFilename For Append As #filenumber Print #filenumber, CStr(Now) & "," & sType & "," & sSource _ & "," & sDetails & "," & Application.UserName Close #filenumber End Sub
You can use it like this
' Create unique error number ' https://excelmacromastery.com/ Public Const ERROR_DATA_MISSING As Long = vbObjectError + 514 Sub CreateReport() On Error Goto eh If Sheet1.Range("A1") = "" Then Err.Raise ERROR_DATA_MISSING, "CreateReport", "Data is missing from Cell A1" End If ' other code here Done: Exit Sub eh: Logger "Error", Err.Source, Err.Description End Sub
The log is not only for recording errors. You can record other information as the application runs. When an error occurs you can then check the sequence of events before an error occurred.
Below is an example of logging. How you implement logging really depends on the nature of the application and how useful it will be:
' https://excelmacromastery.com/ Sub ReadingData() Logger "Information", "ReadingData()", "Starting to read data." Dim coll As New Collection ' add data to the collection coll.Add "Apple" coll.Add "Pear" If coll.Count < 3 Then Logger "Warning", "ReadingData()", "Number of data items is low." End If Logger "Information", "ReadingData()", "Number of data items is " & coll.Count Logger "Information", "ReadingData()", "Finished reading data." End Sub
Having a lot of information when dealing with an error can be very useful. Often the user may not give you accurate information about the error that occurred. By looking at the log you can get more accurate information about the information.
Other Error Related Items
This section covers some of the other Error Handling tools that VBA has. These items are considered obsolete but I have included them as they may exist in legacy code.
The Error Function is used to print the error description from a given error number. It is included in VBA for backward compatibility and is not needed because you can use the Err.Description instead.
Below are some examples:
' Print the text "Division by zero" Debug.Print Error(11) ' Print the text "Type mismatch" Debug.Print Error(13) ' Print the text "File not found" Debug.Print Error(53)
The Error statement allows you to simulate an error. It is included in VBA for backward compatibility. You should use Err.Raise instead.
In the following code we simulate a “Divide by zero” error.
' https://excelmacromastery.com/ Sub SimDivError() On Error Goto eh ' This will create a division by zero error Error 11 Exit Sub eh: Debug.Print Err.Number, Err.Description End Sub
This statement is included in VBA for backward compatibility. You should use Err.Raise instead.
A Simple Error Handling Strategy
With all the different options you may be confused about how to use error handling in VBA. In this section, I’m going to show you how to implement a simple error handling strategy that you can use in all your applications.
The Basic Implementation
This is a simple overview of our strategy
- Place the On Error GoTo Label line at the start of our topmost sub.
- Place the error handling Label at the end of our topmost sub.
- If an expected error occurs then handle it and continue.
- If the application cannot continue then use Err.Raise to jump to the error handling label.
- If an unexpected error occurs the code will automatically jump to the error handling label.
The following image shows an overview of how this looks
The following code shows a simple implementation of this strategy:
' https://excelmacromastery.com/ Public Const ERROR_NO_ACCOUNTS As Long = vbObjectError + 514 Sub BuildReport() On Error Goto eh ' If error in ReadAccounts then jump to error ReadAccounts ' Do something with the code Done: Exit Sub eh: ' All errors will jump to here MsgBox Err.Source & ": The following error occured " & Err.Description End Sub Sub ReadAccounts() ' EXPECTED ERROR - Can be handled by the code ' Application can handle A1 being zero If Sheet1.Range("A1") = 0 Then Sheet1.Range("A1") = 1 End If ' EXPECTED ERROR - cannot be handled by the code ' Application cannot continue if no accounts workbook If Dir("C:\Docs\Account.xlsx") = "" Then Err.Raise ERROR_NO_ACCOUNTS, "UsingErr" _ , "There are no accounts present for this month." End If ' UNEXPECTED ERROR - cannot be handled by the code ' If cell B3 contains text we will get a type mismatch error Dim total As Long total = Sheet1.Range("B3") ' continue on and read accounts End Sub
This is a nice way of implementing error handling because
- We don’t need to add error handling code to every sub.
- If an error occurs then VBA exits the application gracefully.
A Complete Error Handling Strategy
The above strategy has one major drawback. It doesn’t provide any information about the error. It is better than having no strategy as it prevents the application crashing. But that is the only real benefit.
VBA doesn’t fill Err.Source with anything useful so we have to do this ourselves.
In this section, I am going to introduce a more complete error strategy. I have written two subs that perform all the heavy lifting so all you have to do is add them to your project.
The purpose of this strategy is to provide you with the Stack* and line number when an error exists.
*The Stack is the list of sub/functions that were currently in use when the error occurred.
This is our strategy
- Place error handling in all the subs.
- When an error occurs, the error handler adds details to the error and raises it again.
- When the error reaches the topmost sub it is displayed.
We are simply “bubbling” the error to the top. The following diagram shows a simple visual of what happens when an error occurs in Sub3
The only messy part to this is formatting the strings correctly. I have written two subs that handle this, so it is taken care of for you.
There are the two helper subs, RaiseError and DisplayError. You can download the library below:
An Example of using this strategy
Here is a simple coding example that uses these subs. In this strategy, we don’t place any code in the topmost sub. We only call subs from it.
' https://excelmacromastery.com/ Sub Topmost() On Error Goto EH Level1 Done: Exit Sub EH: DisplayError Err.source, Err.Description, "Module1.Topmost", Erl End Sub Sub Level1() On Error Goto EH Level2 Done: Exit Sub EH: RaiseError Err.Number, Err.source, "Module1.Level1", Err.Description, Erl End Sub Sub Level2() On Error Goto EH ' Error here Dim a As Long a = "7 / 0" Done: Exit Sub EH: RaiseError Err.Number, Err.source, "Module1.Level2", Err.Description, Erl End Sub
The result looks like this:
If your project has line numbers the result will include the line number of the error:
Error Handling in a Nutshell
- Error Handling is used to handle errors that occur when your application is running.
- You write specific code to handle expected errors. You use the VBA error handling statement On Error GoTo [label] to send VBA to a label when an unexpected error occurs.
- You can get details of the error from Err.Description.
- You can create your own error using Err.Raise.
- Using one On Error statement in the top most sub will catch all errors in subs that are called from here.
- If you want to record the name of the Sub with the error, you can update the error and rethrow it.
- You can use a log to record information about the application as it is running.
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Many thanks for these informations related to the diiferents codes of VBA error.
This was quite informative and very handy on error handling in VBA. Thanks a ton.!!!!
You’re welcome Pavan.
this is good info, all in one place, thank you! regarding your line numbers in the section above – i agree it is too hard to manually type line numbers, so i am looking for a tool as you suggest. do you recommend a tool?
I used MZ Tools. It has a lot of useful features.
Thank you so much for your excellent description of error handling, it is by far the best I have read and makes the use of On Error Goto -1 very clear. This is a very important concept.
I took a long time to understand VBA Error handling whilst using MS Access and eventually produced pretty much identical code to handle errors, which I am in the process of refining as I read your text to remind me (again) how it works! There’s some very wacky error handling recommendations out there, some are just plain nuts. I think yours is fundamentally sound. (eg Some even push and pull the current procedure name off a stack to record the call stack… etc…)
Here’s a few comments about your technique and code that I hope you find useful (I appreciate you are producing code that people can learn from, but I hope these are reasonable suggestions)
1. To make it easier to debug, such that the coder can halt execution on the line raising the error, do the following:
A. In the RaiseError sub. Before the Err.Raise statement. Add this line “If debugging Then Debug.Assert False”
B. Also set a public boolean constant called debugging to true.
C. Further, in every procedure, after the “RaiseError”, I would add “Exit Sub” and on the next line “Resume” . The Resume will never normally get executed… but…
D. When an error occurs the Debug.Assert statement halts execution, the coder can then use DEBUG>STEPOUT to view the error handler in the procedure with the error.
To view the line that raised the error, they right click on the “Resume” statement, and choose “Set Next Statement” when they then DEBUG>STEPOVER the execution point goes to the line that causes the error to be raised.
2. Using a module level constant to provides the Module name that is used in the error handler of every procedure in the module would be an improvement, perhaps passing the module name as a separate parameter to the handler to make the code clear. (So if you move code you don’t have to revisit each error handler)
3. Sorry but, I don’t like the way you have used MARKET text in sSource to indicate when the procedure has only been called once. It would be more elegant and simpler to use a module level boolean variable.
‘ Finally. The handler works well until:
‘ 4. A statement in the Topmost procedure raises an error and it is handled by DisplayError. The LineNumber is not reported to the user.
‘ 5. A coder uses “On Error Resume Next” before a line that calls a procedure that uses RaiseError.
‘ The RaiseError “Error Location is” text then stops being a call stack, but becomes a call trace.
‘ I have written code to reset the RaiseError procedure which I can share with you…
‘ Also the first error just gets lost and the second error raised is reported.
Thanks so much for your page it totally clarified how error handling works and has enabled me to re-write my code for the better. –
If you would like the excel workbook I produced that has the error handing changes I mention and test / example code just Email me!
Thanks for your comments, I’m glad you have enjoyed the post.
I have updated the code slightly to make it more robust.
1. The “Resume Next” is a nice technique to find the line. This would be great in a scenario where there is a lot of code and it takes time to produce the error again. The Debugging variable should be a Conditional Compilation Parameter rather than a variable.
2. I think “Using a module level constant to provides the Module name” is not that useful. It adds a lot of unnecessary code for little benefit.
3. I’ve updated the code which now uses a boolean.
4. Updated the code to report the line number.
5. I think “On Error Resume Next” is best avoided except for use with specific lines of code.
Great article Paul.
What is meant by 0 in the following line of code: On Error GoTo 0?
The line “On Error GoTo 0” stops at the line with the error. The zero doesn’t mean anything in particular.
I’ve got a very short question, but the issue bugs me tremendously: sometimes, I’ll get a overflow-error when using Debug.Print in loops. The quirky thing is, it sometimes just is a matter of positioning the code block elsewhere and it’ll run fine. Nothing else seems to help – no application.wait before it or just debug.printing in rare cases (e. g. when a variable has a certain value). It just overflows, and throws an error, but without the Debug.Print the code works just fine, so it’s not like I’m handing the variable something unfathomable. I’ll get it even with strings and don’t know what’s the matter and how I can prevent it.
Maybe you’ve encountered this and can give me advice on how to avoid this.
I can’t help without seeing your code and knowing the line where the error occurs.
Are you on Mac? There is a known bug with this for VBA on Mac.
Thank you for an EXCELLENT Webinar and web page.
Proper error handling has always been a murky area until now.
But it’s never too late to learn… (I’m past 80 now!)
Can you help with some advice about RETRO-FITTING proper Error Handling to an existing project?
Since your webinar I’ve thought about this and believe I may have a workable plan to automate retro-fitting the scheme you outline into my current project. In principle it could be ANY project. The aim is to automate the process of making existing code compatible with your error handling procedures.
To scale my project a little for you, it’s not trivial. It has:
** 20 standard code modules, ranging from 30 subs in 2,200 lines (87KB) to 5 subs in 50 lines (<2KB). Of the 50 modules, half exceed 10Kb when exported.
** 8 Userforms, several with many small event subs – a real pain if they get unexpected errors! The largest Userform module is 2000 lines long, with over 100 subs, so tedious to do manually.
** 5 CLS custom class modules
** 6 worksheet CLS modules with critical event code; other sheets have no event code
1) Would it be an abuse of these pages to post details of this automation plan?
NB No code exists; only the germ of an idea.
2) Could you spare some time to review the approach, and maybe improve the planned approach?
3) Do you think others might be interested to follow such a thread?
If not, can you suggest a better forum for such a discussion?
Hi Paul, Thank you for your error handling strategy. I’ve been using it successfully and am a fan. I’ve noticed a strange situation where the err object I am using loses all internal values (i.e., the error number, description etc) in certain circumstances. For example, I encounter an error condition in my code and raise an err object which a description of “Don’t do this”. The err object propagates up the call chain, but at one point it loses the description and becomes “Automation error”.
After some testing I’ve been able to reproduce this behaviour and it seems to be present when the following call stack is encountered:
ThisWorkbook.MethodA calls ThisWorkBook.MethodB (err Raised in here)
When the code gets back the to Error Handling in MethodA, it has completely lost all the internal details of the err object raised in Method B.
If I change the call stack to:
ThisWorkbook.MethodA calls Module1.MethodB (err Raised)
All is good, and the err internals remain as the err object is propagated back up the stack.
Do you know what may be going on here?
A second error is occurring. The err object stores then details of the last error so this is why your first error gets overwritten.
an “Automation Error” normally occurs when VBA is referencing an object like a workbook and that workbook gets closed. If VBA tries to access it you will see an “automation error”.
I was searching for an error handling when a Library (reference is MISSING).
I use an excel userform wit a (dll) reference but when other users are opening the excel and don’t have the reference then it mus be close the excel automatically. So the users arer not involved with the the vba code error.
Do you have any information about this problem?
Kind Regards Bernard
When you distribute a VBA workbook to users it is better to use late binding instead of early binding.
Early binding means using the reference library.
Late binding means using CreateObject. Most of the time this solves the reference library errors on a users computer.
Thanks for an excellent guide Paul! I originally came seeking a way of logging errors (which I found) but in the process this cleared up a few questions I had about how errors get passed back to the topmost sub and I love the complete error handling strategy as a more elegant way of doing this. I’m working on quite a complex project and that will definitely come in handy if you don’t mind me “borrowing” it 🙂
Glad you like it Shirley. Feel free to use.
Thanks Paul! Really helps me to implement error handling strategy in my project. Just consider one think to regarding “clean up” objects referred in sub or function after error raise. Let’s follow this scenario
1) I have refer to file using FileSystemObject, I open file for read or write
2) after that some occurs later in my sub
3) Program jumps to error handler label
4) RaiseError procedure is called and immediately terminate sub.
This would mean file is still open and if we run program once again, I would receive error that file is used. GoTo CleanUp label would not work here as once we reraise error we immediately exit sub/function
Sub SampleProcedure(path as String)
Dim fso As FileSystemObject
Dim inFile As TextStream
On Error GoTo CatchError:
Set fso = New FileSystemObject
Set inFile = fso.OpenTextFile(path, ForReading)
‘ some code here
‘if program run without error code below is executed
Set inFile = Nothing
Set fso = Nothing
DisplayError Err.Source, Err.Description, “Module1.SampleProcedure”
RaiseError Err.Number, Err.Source, “Module1.SampleProcedure”, Err.Description
With DisplayError is not a problem as I can use GoTo CleanUp
Thanks a million!
Very helpful and well explained.
I was struggling with error handling within a loop and this helped me to get my error handling code sorted out.
You’re welcome Thomas.
I’ve tried to incorporate your general error handling approach. However I failed simply because I try my very best to avoid them by extensive precondition checkig. And when a precondition to run the code is not meet I display a proper error message. I.e. it is not VBA throwing the error but my code. Example checking a parameter v As Variant:
If VarType(v) = vbObject Then
sError = “Object v is Nothing”
If v Is Nothing Then Goto on_error
ErrHdlr sMod, sProc, sError
It appears that your approach is unable to integrate this kind of “user errors”.
“it is not VBA throwing the error but my code”. Your code is not actually throwing an error message. It is simply going to a label.
To throw an error message use the Err.Raise function. This is will work with my Error Handling framework.
As others have said, great tutorial, thanks so much for publishing it. Saved me COUNTLESS hours trying to drill into Msoft help! 😀
Thank you for great tutorial, I learned a lot. However I am struggling with interesting problem.
When I call Err.Raise with source and description without On Error GoTo… in the main sub, I get an error message with these strings.
When I call Err.Raise with source and description WITH On Error GoTo… in the main sub, the DisplayError sub gets empty strings instead of Err.Source and Err.Description.
Ah, typical. Investigating for like a week and half an hour after asking you I figured it out. After catching an error I called another Sub, that had its own error handling, so I guess it reset the error.
Glad you got it sorted
On Error Goto -1
Huh. Learn something new every day. Great post!
This is the best article I have found on this subject! Thank you for all your effort Paul.
You’re welcome Michelle. Glad you like it.
Thank you for this article.
I have a main SUB that calls different other Sub. For example:
If Statement then
However, if I have an error during SUB1, how can I exit the SUB Main?
Thank you in advance.
I hope I was clear enough.
You can use err.raise to generate an error in Sub1. You can then catch this in the error handler in Main.
Hi Paul, I am trying to use your error handle in my spreadsheet, but having problems. Does it only work for subs in modules? Does not want to run in worksheet subs. Also, if I just have one sub in a module, do I put the RaiseError on one line then DisplayError on the next line? That is how I have it but it breaks in the RaiseError sub where you have the comment to be sure the DisplyError is in the top most sub, but that isn’t the case here. Hope you can help, thanks!
Thanks for this informative article. I don’t have a topmost sub. Instead I have a dozen or so top-level subs (each called from the ribbon via callbacks).
How could I adapt your code to have one ‘display error’ sub that each top-level sub calls (rather than lots of duplicate code), please?
When ever I exit from a textbox (Excel vba UsedForm) it takes value of other textbox automatically. Although I have put validation, but it does not work.
I asked for the best VBA Error handling on the web and I received this. I’m very slowly getting better with VBA but the worthless Microsoft Help drives me crazy. They made absolutely NO effort to provide any help to folks trying to learn VBA. I’ll try your new help again and hope I can learn how to move further ahead.
Great article and tips Paul!!
Just want to add that VBA Telemetry could also help developers to track VBA errors in real-time online by connecting VBA with Microsoft Azure.
Did you maybe try it?
I’m the author of this VBA Telemetry tool
Thanks for posting the article, it helps VBA beginners grasp much of the idea and usage of error handling logic in VBA development.
I have some questions based on your article above:
1. It is based on the below code snippet you posted:
Dim count As Long, i As Long
‘ Continue if error as we will check the error number
On Error Resume Next
For i = 0 To 9
‘ generate error for every second one
If i Mod 2 = 0 Then Error (13)
‘ Check for error
If Err.Number 0 Then
count = count + 1
Err.Clear ‘ Clear Err once it is counted
Debug.Print “The number of errors was: ” & count
My question is, you said Err.Clear does not reset the error (it merely clears the error description and number), and you also said once an error occurs, the error handling behavior goes back to default, which is On Error Goto 0, with that said, in the For loop, once the first error (13) is raised, shouldn’t the error handling behavior goes back to default, and hence the second time error (13) is raised, then the code would stop there ? Why in this case the On Error Resume Next behavior is still valid/active ?
2. You said Err.Raise must be used with vbObjectError to specify the error number, and the error number must be between 513 and 65535. I tried doing Err.Raise 13 , “something”, “something”, it seemed to work just fine, so not sure what you meant by “must be used with vbObjectError”.
3. You said error would be cleared once exit from a sub, then how does the logic below work ?
Place the On Error GoTo Label line at the start of our topmost sub.
Place the error handling Label at the end of our topmost sub.
If an expected error occurs then handle it and continue.
If the application cannot continue then use Err.Raise to jump to the error handling label.
If an unexpected error occurs the code will automatically jump to the error handling label.
It seems the example you gave had the error generated in a child Sub propagated to the parent Sub and handled by the parent Sub “On Error Goto [Label]” code segment, doesn’t that mean the error in the child Sub was not cleared on Sub exit ?
Where’s the download link???
IF you cannot see the download it’s most likely because you are using a pop-up blocker like uBlock Origin.
This is absolutely a fantastic post (can’t believe I never saw until today). Most people think I know a thing or two about VBA, but this will now be my go to location when my code . Thanks Paul.
Glad you like it!
Question :How do I make the code below iterate repeatedly .Now it iterates through ErrorHandler only once
before displaying Run Time Error 13
On Error GoTo ErrHandler
On Error GoTo ExitNow
Dim Vlong As Long
Vlong = “Hi”
Use “Resume Next”
I would point out that the Logger() function does not include a test for if the log file already exists.
The file will be created at line
“Open sFilename For Append As #filenumber”
however the length of the file is tested before this, so a missing file will cause errors.
Worse, as I’m calling the logger inside of an error handling block, this can cause the behaviour of my error handling to change unexpectedly.
I handled it thus:
Sub Logger(sType As String, sSource As String, sDetails As String)
On Error GoTo eh
Dim sFilename As String
sFilename = “C:\temp\logging.txt”
‘ Archive file at certain size
If FileExists(sFilename) Then
If FileLen(sFilename) > 20000 Then
FileCopy sFilename _
, Replace(sFilename, “.txt”, Format(Now, “ddmmyyyy hhmmss.txt”))
‘ Open the file to write
Dim filenumber As Variant
filenumber = FreeFile
Open sFilename For Append As #filenumber
Print #filenumber, CStr(Now) & “,” & sType & “,” & sSource _
& “,” & sDetails & “,” & Application.UserName
#If vardebug > 0 Then
Debug.Print “Error ” & Err.Number, Err.Source, Err.Description
Public Function FileExists(fPath As String) As Boolean
Dim sFileExists As String
Let sFileExists = Dir(fPath)
FileExists = sFileExists “”
The Logger I created is really just a starting point. A full Logger would require a lot more functionality.
Thanks for all your posts and videos. Sometimes when capturing errors the Error Number given looked like “-2147016642”. Finally found the answer to making those errors readable. Thought you might like this solution.
‘-2147016642 -> 20 001 620 076 -> Path not found
Debug.Print “ErrIs: “, Error(ErrToOct(-2147016642))
Function ErrToOct(ErrNum As Double)
ErrToOct = Right(Oct(ErrNum), 3)
And for reporting:
Dim ErrNumIs As String
ErrNumIs = Err.Number
If ErrNumIs 65536 Then ErrNumIs = Error(ErrToOct(.errnum))