Put On Error GoTo and a label at the top of the procedure, Exit Sub before the label, and the handling code after it:
Sub ImportData()
On Error GoTo ErrHandler
'your code here
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
Without Exit Sub, the code runs on into the handler and shows the error message even when nothing went wrong. End the handler with Resume Next to carry on from the line after the error, or with Resume to retry the failing line once the cause is fixed.
For one line you expect might fail, switch errors off around that line only: On Error Resume Next, the risky line, then On Error GoTo 0. Check If Err.Number <> 0 Then straight after the line to see whether it worked.
A handler deals with one error at a time. A second error inside the handler stops the macro with the normal error message. On Error GoTo -1 clears the first error so another handler can take over.
Leaving On Error Resume Next on for a whole procedure hides every failure, so the macro finishes with wrong results and no message. While testing, set Tools → Options → General → Break on All Errors in the Visual Basic Editor to see every error, even inside handlers.
Just like any other programming language, VBA is no exception when it comes to errors, and you have to deal with them no matter what. They may come from different sources, like bad coding, impossible operations (like zero division), or unexpected errors.
The best way to deal with them is to have a proper understanding of all the possible outcomes that you could get with your code. Look at the example below, where we have a VBA code that calculates the square root of the value you have in the active cell.
Sub Square_Root() ActiveCell.Value = ActiveCell.Value ^ (1 / 2) End Sub
But if the active cell has a value other than a number, you’ll get a run-time error, just like below.

And a negative number gives you a run-time error as well (error 5, Invalid procedure call or argument), as VBA can’t calculate the square root of a negative number. That’s why you need error handling, and this guide covers all of it.
Error Settings in VBA (Error Trapping)
In the VBA options, you can configure how VBA deals with errors before you start writing code. To open these settings, go to Tools ➤ Options ➤ General ➤ Error Trapping. Here you have three options that you can use.

- Break on All Errors: If you have this option activated, VBA will stop the code for all types of errors, even if you have used all kinds of error handling techniques.
- Break in Class Module: With this option, VBA stops on every error that isn’t handled by an error handler. And if you’re using objects such as UserForms or class modules, it will also break within those objects and highlight the exact line where the error is.
- Break on Unhandled Errors: This is the default setting. It stops the code for all the errors where you are not using any error handling technique. (But if you’re using objects such as UserForms, this will not highlight the line causing the error in the object; it will only highlight the line that’s referring to that object.)
Types of VBA Errors
To understand VBA errors, you can split them into four categories, and below is the explanation of these types of errors.
1. Syntax Errors
While writing VBA code, you need to follow a particular syntax, and when you skip it or don’t write it the way it should be, you get a SYNTAX error (also called a language error). It’s like the typos you make while writing your code.
Well, VBA helps you by pointing out these errors by showing an error message. You just need to make sure you have “Auto Syntax Check” activated in your VB editor.
Go to Tools ➤ Options (Editor tab) and make sure to tick “Auto Syntax Check”. With this, whenever you make a SYNTAX error, VBA will show an error message.

But if “Auto Syntax Check” is disabled, VBA still highlights the line of code with the error (in red) but won’t show the error message.
2. Compile Errors
It comes when you write code to perform an activity, but that activity is not valid or can’t be performed by VBA. The best example is where you have a code using the IF statement but forgot to add END IF at the end of the statement, and now when you run it, VBA shows you a compile error message.

Apart from this, there are some other examples of compile errors:
- Using For without Next (For Next).
- Select without End Select (Select Case).
- Not declaring a variable when you have “Option Explicit” enabled.
- Calling a Sub/Function that does not exist.
3. Runtime Errors
A runtime error occurs at the time of executing the code. Remember the example I shared with you above, where the code calculated the square root of a number.

When a runtime error occurs while running code, it stops the code and shows you an error dialog box, and that box tells you the nature of the error. Let’s say you have written a code that opens a workbook from the location which you have specified but now that workbook has been moved or deleted by someone.

So, when you run the code, VBA will show you a runtime error as it can’t find the file at that location. The message you get with a runtime error describes the reason, which helps you understand what went wrong.

And when a runtime error occurs, it stops the execution of the code. If you click on the “Debug” button, it shows you the line of code that has the error by highlighting it in yellow. Or you can click on the “End” button to stop the code and close the error message.
4. Logical Error
It’s not an error but a mistake while writing code. These types of errors can sometimes drive you nuts while finding and correcting them.
Let’s say you write code, and while declaring a variable, you use the wrong data type, or you use the wrong calculation steps. In this case, your code will run without any error message, but the result will be wrong, and you won’t find this error easily. The best way to deal with this kind of problem is to run each line of code one by one.
Using Debug Tools in VBA
VBA provides you with a set of tools to debug your code and remove bugs from it.
1. Compile VBA Project
In Visual Basic Editor, there’s an option that you can use instantly after completing your code. This compile option scans each line of your code and shows a message box if there is an error in your code.
Note: The Compile VBA Project option only traces syntax and compile errors, not runtime errors, as these errors only arise when the code is running. To use Compile VBA Project, go to Debug ➤ Compile VBA Project.

Once you run “Compile VBA Project” and you have no error in your code, the options will be greyed out.
2. Run Each Line of Code One by One
This is how I do it. When I complete a code, I simply run it line by line to check if there’s an error occurring. It may take time, but it helps you find all the errors (syntax, compile, and runtime).
On the “Debug Toolbar”, there’s a button “Step Into” which you can use to execute code line by line, or you can simply press F8 to execute a single line and then press it again to execute the next line in the code.

3. Breakpoints, the Immediate Window and the Locals Window
When you have a long code, you don’t need to step through it from the first line. Click on the line where you want the code to pause and press F9 (or click in the grey bar to the left of the line) to add a breakpoint. Now run the code, and VBA will stop when it reaches that line. From there, you can press F8 to go line by line.
While the code is paused, open the Immediate Window (Ctrl + G), type a question mark followed by what you want to check (like ?Range(“A1”).Value) and press Enter to see its current value. And to see the values of all the variables at once, go to View ➤ Locals Window. Press F9 again on the same line to remove the breakpoint.
Using the “On ERROR” Statement to Handle VBA Errors
It’s important to check your codes and find possible errors in all the debugging ways you have. But the best and most effective way is to create error-handling statements that can deal with an error and make your code flawless while executing. Let’s learn about these statements. When an error occurs in a VBA code the best possible ways to handle that error can be:
- Let VBA ignore the error and execute the code.
- Let a special set of statements run when an error occurs.
In both solutions, you can use “On Error” statements. Below are four “On Error” statements that you can use. Let’s look at each statement one by one.
1. On Error Resume Next
This simple line of code lets VBA continue executing the code despite the occurrence of an error. The IDEA is simple: Move to the next line of the code if there’s an error found somewhere while executing.
In the below code, you have two lines of code:
- The first line says the value of cell A1 is 25 divided by 0.
- And the second line says the value of cell A2 is 10 divided by 5.
Now there’s a problem with the code you have in line one. As you know, if you divide anything by 0, the result will be an error. So, when you run this code, VBA will show an error message “Run-time error ‘11′ Division by Zero” and stop the execution.
But when you add the “On Error Resume Next” at the very beginning of the code and run the code, VBA simply skips the line of code where the error occurs, continues with the second line, and adds that value in cell A2.
Sub myDivide() On Error Resume Next Range("A1").Value = 25 / 0 Range("A2").Value = 10 / 5 End Sub
So, whenever you want your code to keep running despite an error occurring anywhere, simply use the “On Error Resume Next” statement in your code.
But here’s one more thing you need to note down: It will only skip errors that occur after it.
Let’s say an error occurs at line 5 and you have added “On Error Resume Next” on line 8, then it would not skip that error. So, add it before the lines where you expect an error.
But don’t leave it on for the whole procedure, as it hides every error, even the ones you need to know about. A better way is to use it for just the line that can fail, turn it off with “On Error GoTo 0”, and then check the result. In the code below, VBA tries to find a sheet named “Data”, and if the sheet isn’t there, the variable stays empty (Nothing) and you get a message instead of an error.
Sub CheckSheet()
Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Data")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "There's no sheet named Data in this workbook."
Else
ws.Activate
End If
End Sub
You can also check Err.Number right after the line: if it’s 0, the line ran without an error. And remember, “On Error Resume Next” works only in the procedure where you have it.
2. On Error GoTo 0
It’s the default behavior of VBA that when an error occurs, it stops the execution of the code.
Well, on its own, “On Error GoTo 0” makes no difference in your code. VBA will simply stop the code and show a message with a description of the error. What it does is turn off the error handler that’s enabled in the current procedure. Then why would I bother to use it? Smart question. Let’s use the example you have used above in “On Error Resume Next”.
In this code, whenever an error occurs, VBA will resume at the next line of code and run it, and you won’t see any error message. But let’s say you have more lines in your code and you don’t want to skip errors in those lines.
So, if you enter “On Error GoTo 0” after the line where you expect the error, it will restore VBA’s default error handling, which shows an error message each time an error occurs.
In the code below, VBA skips the error in cell A1, but for the error in cell A3, it shows the error message, as the default error handling is back.
Sub myDivide()
On Error Resume Next
Range("A1").Value = 25 / 0
On Error GoTo 0
Range("A2").Value = 10 / 5
Range("A3").Value = 10 / 0
End Sub
3. On Error GoTo [Label]
Think about a place in a building where you can head to in an emergency. In the same way, using “On Error GoTo [Label]”, you can simply create a separate block of code in your main code to deal with an error.
Actually, “On Error GoTo [Label]” is a far better and more convenient way to deal with errors. In the code below, you have “On Error GoTo myError”, and in this statement, the word “myError” is the label.
Sub Square_Root()
On Error GoTo myError
Range("A1").Value = Sqr(Range("A1").Value)
Exit Sub
myError:
MsgBox "There's some problem with the value you have in cell A1."
End Sub
A label name follows the same rules as a variable name: it starts with a letter and can’t have spaces or characters like “!”, and you write it at the start of a line, followed by a colon. The label also needs to be in the same procedure as the “On Error GoTo” statement.
If you look at the end of the code, you have a block that starts with the label name and then a message box with a message about the error.
Now, if an error occurs, VBA will jump to the label “myError” and run the block of code that you have after that label.
But there’s one thing you need to take care of: Even if an error doesn’t occur, the code after the label will still get executed. There are two things you need to do:
- First, make sure to add your Error label at the end of the code.
- Second, add an “Exit Sub” before the error label.
With this, you’ll benefit in both situations. If an error occurs, VBA jumps to the label you specified and runs only the code after it. And if an error doesn’t occur, the “Exit Sub” statement that you have before the label will exit the procedure without running the error-handling code.
4. On Error GoTo -1
Before we get into this, let me share something with you. When an error occurs and VBA jumps to your error handler, that handler stays active until VBA reaches a Resume, Exit Sub or End Sub statement. While it’s active, the procedure can’t handle another error.
O VBA! Live in Present
To deal with a second error in a VBA code, you need to clear the first error from VBA’s memory. In the below code, you have two “On Error GoTo [Label]” statements that deal with errors from two different blocks of code.
But if you run this code (with text in both cells), when the second error occurs, VBA won’t jump to the label which you have defined and instead shows the error message “Type Mismatch”.
Sub Square_Root() On Error GoTo myError1 Range("A1").Value = Range("A1").Value ^ (1 / 2) myError1: MsgBox "There's some problem with the value you have in the cell A1." On Error GoTo myError2 Range("A2").Value = Range("A2").Value ^ (1 / 2) myError2: MsgBox "There's some problem with the value you have in the cell A2." End Sub
To fix this problem, you can use “On Error GoTo -1”, which clears the current error and resets the active error handler, so the next “On Error GoTo” statement can work.
Sub Square_Root() On Error GoTo myError1 Range("A1").Value = Range("A1").Value ^ (1 / 2) myError1: MsgBox "There's some problem with the value you have in the cell A1." On Error GoTo -1 On Error GoTo myError2 Range("A2").Value = Range("A2").Value ^ (1 / 2) myError2: MsgBox "There's some problem with the value you have in the cell A2." End Sub
Now when you run this code, “On Error GoTo -1” removes the error from the memory, and VBA deals with the error in the second statement as you want.
Both codes also show the messages when there’s no error at all, as they have no “Exit Sub” before the labels. In a real code, the Resume statement (below) is a cleaner way to leave an error handler and continue.
What Else Do I Need to Know to Handle Errors in VBA?
Apart from using error handling techniques, there are a few other things that you can use to deal with errors in a better way.
Resume, Resume Next and Resume [Label]
Inside an error handler, you can use a Resume statement to tell VBA where to go after the error is dealt with. Resume also clears the error and makes the error handler ready for the next error.
- Resume: Runs the line that caused the error again. Use it only when the handler has fixed the cause; otherwise, you get an endless loop.
- Resume Next: Continues with the line right after the one that caused the error.
- Resume [Label]: Continues from a label you specify, like a cleanup block at the end of the code.
In the code below, if there’s a problem with the value in cell A1, VBA shows the error number and description, and then moves on to cell A2.
Sub Square_Root()
On Error GoTo myError
Range("A1").Value = Sqr(Range("A1").Value)
Range("A2").Value = Sqr(Range("A2").Value)
Exit Sub
myError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Next
End Sub
Errors in Called Procedures
When a procedure calls another procedure and the error occurs in the called one, VBA looks for an error handler there first. If it has none, VBA passes the error back to the calling procedure and uses its error handler. So one error handler in the main procedure can catch errors from the procedures it calls.
Sub MainMacro()
On Error GoTo myError
Call SquareRootA1
MsgBox "Done"
Exit Sub
myError:
MsgBox "Error in a called procedure: " & Err.Description
End Sub
Sub SquareRootA1()
Range("A1").Value = Sqr(Range("A1").Value)
End Sub
A Simple Error Handling Template
Here’s a structure that I use for most of my codes. If your code changes settings like screen updating, the cleanup block puts them back whether the code runs fine or an error occurs.
Sub myMacro()
On Error GoTo myError
Application.ScreenUpdating = False
' your code here
cleanUp:
Application.ScreenUpdating = True
Exit Sub
myError:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
Resume cleanUp
End Sub
Err Object
When an error occurs while executing code, you can use the Err object to get details about that error. There are a few properties and methods which you can use with the Err object. Let’s learn them one by one.
Properties
Below are the properties which you can use with the Err object:
- Err.Number: When an error occurs, VBA stores its number in the Err object. In the code below, when an error occurs, the message box shows the error number.
- Err.Description: This property shows the description of the error which can help you to understand the reason for the error.
- Err.Source: This property shows the name of the object or project that generated the error (for an error in your own code, it’s the VBA project name).
- Err.HelpContext: This property returns the help context id for the error in the help file.
- Err.HelpFile: This is a string value with the path of the help file.
Normally, when you are dealing with errors using error handling techniques, you won’t be using the Err object that much in your codes. But below is a simple example of using it.
Sub Square_Root() On Error GoTo myError1 Range("A1").Value = Sqr(Range("A1").Value) Exit Sub myError1: MsgBox "There's some problem with the value you have in the cell A1." & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Description: " & Err.Description End Sub
When you run the above code, and if an error occurs, it will show a message box with the error number and description of the error.
Methods
With the Err object, there are two methods that you can also use.
- Err.Clear: This method clears the error number and error description from VBA’s memory (it’s different from “On Error GoTo -1”, as it doesn’t reset the active error handler). VBA also clears them on its own when it runs Resume Next, Exit Sub or Exit Function inside an error handler.
- Err.Raise: With this method, you can generate a run time error in your code intentionally, and below is the syntax you need to follow:
Err.Raise [number], [source], [description], [helpfile], [helpcontext]
All the arguments are shown in square brackets here, but number is the only required argument; the others are optional. For your own errors, Microsoft suggests adding your number to the vbObjectError constant, so it doesn’t clash with VBA’s own error numbers. In the code below, VBA raises a custom error when cell A1 has a negative number, and the error handler shows its description.
Sub CheckValue()
On Error GoTo myError
If Range("A1").Value < 0 Then
Err.Raise vbObjectError + 513, "CheckValue", "The value in cell A1 can't be negative."
End If
Range("B1").Value = Sqr(Range("A1").Value)
Exit Sub
myError:
MsgBox Err.Description
End Sub
Quick Tips on Error Handling
Here are a few quick tips which you can use to deal with VBA errors in a better way.
- Use “On Error Resume Next” only when you know for sure that an error can occur, and it’s OK to skip the line with the error and move to the next line.
- Turn “On Error Resume Next” off with “On Error GoTo 0” as soon as the line that can fail is done.
- The best way to deal with run-time errors is by using “Error Handler” with “On Error GoTo [Label]”. This ensures that whenever an error occurs, you will know about it, but it won’t show that nasty error message.
- Whenever you use an error handler, make sure to use “Exit Sub” (or “Exit Function” in a function) before it.
- Use the error handler to put back any settings your code changes, like screen updating or calculation mode.
Frequently Asked Questions
What Is the Difference Between On Error GoTo 0 and On Error GoTo -1?
“On Error GoTo 0” turns off the error handler in the current procedure, so VBA goes back to showing its own error messages. “On Error GoTo -1” clears the current error and resets the active error handler, so you can handle another error with a new “On Error GoTo” statement.
How Do I Find the Line That Caused the Error?
When the error message shows up, click the “Debug” button, and VBA highlights the line in yellow. If your error handler hides the message, comment out the “On Error” line for a moment and run the code again, or step through it with F8.
Can I Use Error Handling in a Function?
Yes. It works the same way as in a Sub, but you use “Exit Function” before the error label instead of “Exit Sub”. In the error handler, you can also return a value for the function, like 0 or an error message, so the cell or code that uses the function gets a result.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.