Excel VBA Error Handling
In VBA when we work with codes we may encounter many different types of error and how to troubleshoot these errors is known as Error handling, now there can be some errors which are made in the syntax which excel itself highlights it but when there is some error which is out of the range or something which does not exist excel gives us a pop up for the same, it is important to know which error code is for what error in order to identify the error in code.
In Excel VBA, while executing any set of codes, we get some sort of errors. Some of these errors are syntax errors; some are errors that are nonexecutable. The syntax error is when made by the user is highlighted in red color by excel itself. But when there is any other sort of run time error, how do we handle it, and how do we get beyond this is what we will cover in this article.
Apart from syntax errors, the other run time errors need to be handled while executing any set of codes. First, let me give an example of how the other runtime error occurs. Have a look at the below code,
This is a sample code, when executed, will return what is written in the msgbox function. But as we can see that in the second line of the code, there is 4/0, which is not possible in mathematical terms, so it will return a run time error. Let us execute the above code and see the error we will get.
This is the error we get while executing the given code. Now how do we handle this error is made by Error Handling.
There are two methods for handling errors which are:
- On Error Goto, and
- On Error Resume Next.
As explained above, we get many types of errors in VBA. Some are syntax, and some are run time. Syntax errors are already highlighted in Red color, for example, refer below screenshot,
While the other is run time errors. Basically, excel will do the following three things, either it will show an error or ignore that error, or it will show a certain set of instructions. To perform such tasks, we need to give instructions, and this is called Error Handling.
How to Handle Errors in VBA Code?
For the first example, let us take the first code we took as the demonstration. In the above example, we have seen that the code gives run time error at the second msgbox function.
Write the following code after opening the subfunction,
Sub Sample() On Error Resume Next MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 End Sub
Now when we execute the code above, we see that the line of code that has the error is not executed. Excel skips that line and resumes on the next line.
There is another method to handle the error is VBA Goto Statement Goto StatementVBA GoTo statement authorizes you to jump to a line of code by skipping all the errors in between. There are two kinds of GOTO statements one is to select any range of the worksheet in the specified workbook and another one is error handler.. We provide excel as a destination to go to when it finds an error. Instead of the previous error handling code, we inserted, write down the following code,
Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 End Sub
We are giving excel Az as a destination to go to if it finds an error. Now after the msgbox, write another code as below,
Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 Done: Exit Sub
Now we need to define the destination az as what it should do when excel finds an error in the code.
Sub Sample() On Error GoTo az MsgBox 4 / 2 MsgBox 4 / 0 MsgBox 4 / 1 Done: Exit Sub az: MsgBox "This is an error " & Err.Description End Sub
Now when we run this code, we see the result displayed.
This is the first msg box result and as we know that we have an error in the next line of our code, let’s see the result what excel will give.
The err.description above in the code helps us to show exactly what the error has occurred in our code.
We have learned how to handle errors in our codes. Let us look at another example of how to handle errors. Consider the following code as our second example.
We have a somewhat similar error from example 1. The error is in line d = i/b. Now we will handle these errors by using the two methods explained above.
Write the following code after opening the subfunction,
Sub Sample2() On Error Resume Next bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d End Sub
Now when we execute our code, we can see that it ignores the second line and just displays the value for C.
The above error handler was a resume next, and now we will use the Go to wherein we will tell we excel a destination to go when it encounters an error. Write down the following code,
Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d
The bx is a destination given when it encounters an error after msgbox D write down the following code,
Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d DOne: Exit Sub
Now we need to define the destination Bx what it should do when it encounters an error, So write down the following code,
Sub Sample2() On Error GoTo bx Dim i As Integer, b As Integer, c As Integer, d As Integer i = 2 b = 0 c = i + b MsgBox c d = i / b MsgBox d DOne: Exit Sub bx: MsgBox " This is another Error " & Err.Description End Sub
Now when we execute the code, we can see that excel first gives us the value for C.
Now in another step, it will give us the prompt we provided it when it encounters an error.
This is how we handle the normal runtime errors in ExcelErrors In ExcelErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference. VBA.
Things to Remember
There are a few things we need to remember about Error Handling:
- On Error Resume Next, Ignores the error.
- On Error GoTo Gives excel a destination when it encounters an error.
- The description is used to show the exact error that occurred to the user.
This has been a guide to VBA Error Handling. Here we learn how to handle the error in VBA using two methods, 1) On Error GoTo 2) On Error Resume, along with practical examples and a downloadable template. Below are some useful excel articles related to VBA –