Just like any other programming language, VBA has no luck 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 it is to have a proper understanding of all the possible outcomes of the result that you could get with code. Look at the below example where we have a VBA code that calculates the square root of the number using the value which you have in the selected cell.
Sub Square_Root()
ActiveCell.Value = ActiveCell.Value ^ (1 / 2)
End SubBut if the active cell has a value other than a number, you’ll get a run-time error, just like below.
Error Settings in VBA (Error Trapping)
In the VBA option, you can configure the setting to deal with errors before you start writing codes. To open the VBA settings, go to Tools ➤ Options ➤ Generals ➤ Error Trapping. Here you have three options which 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 will stop all your codes that are not handled by any technique. And if you’re using objects such as Userforms, 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 that helps you to know about all the errors where you are not using any error handling technique and stop the code for all the unhandled errors. (But, if you’re using objects such as Userforms, this will not highlight the line causing the error in the object but 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 in the way it should be you can face SYNTAX error (also called Language error). It’s like typos that you do while writing your codes.
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 the Tool ➤ Options and make sure to tick the “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 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 missed to add END IF at the end of the statement and now when you run this VBA will show you a compilation 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 when the code calculated the square root of a number.
When a runtime error occurs while running code, it stops the code and shows you the error dialog box and that error box talks about the nature of the error you have. Let’s say you have written a code that opens a workbook from the location which you have specified but now that workbook is relocated or deleted by someone.
So, when you run the code, VBA will show you a runtime error as it can’t find that file on that location. The message you get in a run time error describes the reason which helps you to understand the reason for the error.
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 that error by highlighting it yellow. Or you can click on the “End” button to stop the code to execute and close the error message.
4. Logical Error
It’s not an error but a mistake while writing code. These types of errors sometimes can give you nuts while finding them and correcting them.
Let’s say you write code and while declaring a variable you used the wrong data type, or you have used the wrong calculation steps. In this case, your code will work fine, 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 a set of tools to debug your code and remove bugs from your codes.
1. Compile VBA Project
In Visual Basic Editor, there’s an option that you can use instantly after completing your code. These compile options scan each line of your code and show a message box if there is an error in your code.
Note: Compile VBA option only traces Syntax and Compile errors, not runtime errors as these errors only rise when a 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 to get to about all the errors (Syntax, Compile, and Run-Time).
On the “Debug Toolbar”, there’s a button “Step In” which you can use to execute a 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.
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 the 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. And now, 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 cell A2 is 10 divided by 5
Now there’s a problem with the code which you have inline 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 that line of code where the error occurs and continues with the second line and add that value in cell A2.
Sub myDivide()
On Error Resume Next
Range("A1").Value = 25 / 0
Range("A2").Value = 10 / 5
End SubSo, whenever you want your code to get executed 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, the best way is to add it as the first line of the code in the procedure.
2. On Error GoTo 0
It’s the default behavior of VBA that when an error occurred it stops the execution of the code.
Well, using “On Error GoTo 0” make no difference in your code. VBA will simply stop the code and show a message with a description of the error. 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 will occur VBA will resume to 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 surpass those lines if there’s an error in the code.
So, if you enter “On Error GoTo 0” after the second line of code it will restore the VBA’s default error handler which shows error messages each time an error occurs.
3. On Error GoTo [Label]
Think about a place in a building where you can head up 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 below code, you have “On Error GoTo Oh!Error” now in this line statement the word “Oh!Error” is the label.
If you look at the end of the code where you have a specific starting with the label name and then a code for a message box with a message about the code.
Now, what happens if an error occurs the VBA will jump to the label “Oh!Error” and run the block of code which you have after that label.
But there’s one thing you need to take care of: If an error doesn’t occur even then the label you have in your code will 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. Let’s say if an error occurs and VBA jumps to the label you specified there would only be code from the label itself to code. And if an error doesn’t occur “Exit Sub” statement which you have before the label will exit the procedure without executing the error label.
4. On Error GoTo -1
Before we get into this, let me share something with you. When an error occurs in a code VBA stores that error log in its memory and only clears it when the routine ends.
O VBA! Live in Present
To deal with the 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, when the second error VBA won’t jump to the label which you have defined and instead show 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 SubTo fix this problem you can use “On Error GoTo -1” which makes VBA remove the current error from its storage memory.
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 SubNow 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.
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.
Err Object
When an error occurred while executing of 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 Err object. Let’s learn them one by one.
Properties
Below are the properties which you can use with the Err object:
- Err.Number: With an error occurred there’s a number stored in the Err Object. In the below code, when occurred the message box will show 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 you in which project the error has occurred.
- Err.HelpContext: This property returns the help context id for the error in the help file.
- Err.HelpContext: This is a string value for the location 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 to use 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 SubWhen you run the above code, and if an error occurred, it will show a message box with the error number and description of the error.
Methods
With 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 completely reset the error).
- Err.Raise: With this method, you can generate a run time error in your code intentionally, and below is the syntax which needs to follow:
Err.Raise [number], [source], [description], [helpfile], [helpcontext]
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 about an error to occur and it’s OK to skip the line of code with an error and it’s safe to skip to the next line.
- The best way to deal with run-time errors is by using “Error Handler” with “On Error GoTo [Label]”. This ensures that whenever the error occurs you will know about it, but it won’t show that nasty error message.
- Whenever you use the error handler make sure to use “Exit Sub” before it.