VBA Error Handling

In VBA, when we work with codes, we may encounter many different types of errors, and how to troubleshoot them is known as error handling. Now, one can make some errors in the syntax, which Excel highlights. But, when some error is out of the range, or something does not exist, Excel gives us a pop-up for the same. Therefore, it is important to know which error code is for what to identify the error in code.

While executing any set of codes in Excel VBA, we get some errors. Some of these errors are syntax errors; some are non-executable errors. The syntax error is when made by the user is highlighted in red 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, one must handle the other run-time errors while executing any set of codes. First, let us give an example of how the other run-time error occurs. Have a look at the below code:

When executed, it is a sample code that will return, which one has 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. So, let us execute the above code and see the error we will get.

It is the error we get while executing the given code. Now, how we handle this error is made by error handling.

There are two methods for handling errors which are:

1. On Error Goto
2. On Error Resume Next.

As explained above, we get many types of errors in VBA. Some are syntax, and some are run-time. Already, the syntax errors are highlighted in red color. For example, refer to the below screenshot.

While the other is run-time errors. Excel will do the following things: either show an error, ignore it, or follow a certain set of instructions. To perform such tasks, we need to give instructions called error handling.

Example #1

For the first example, let us take the first code we took as the demonstration. The above example shows that the code gives a run-time error at the second msgbox function.

Write the following code after opening the subfunction:

Code:

Sub Sample()
On Error Resume Next
MsgBox 4 / 2
MsgBox 4 / 0
MsgBox 4 / 1
End Sub

When we execute the code above, we see that the line of code with the error does not execute. So, Excel skips that line and resumes on the next line.

There is another method to handle the error: the VBA Goto Statement. We provide Excel as a destination when it finds an error. Instead of the previous error handling code, we inserted and wrote the following code:

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:

Code:

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.

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.

It is the first msg box result and as we know that we have an error in the next line of our code, let us see what Excel will give.

The err.description above in the code helps us to show what error has occurred in our code.

Example #2

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. We will now handle these errors using the two methods explained above.

Write the following code after opening the subfunction.

Code:

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

When we execute our code, we can see that it ignores the second line and displays the value for C.

The above error handler was a resume next. Now, we will use the "Go to," wherein we will tell our destination to go when it encounters an error. Write down the following code:

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:

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

We need to define the destination Bx and what it should do when encountering an error. So, write down the following code:

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

When we execute the code, we can see that Excel first gives us the value for C.

In another step, it will give us the prompt we provided when it encounters an error.

Like this, we handle the normal runtime errors in Excel VBA.

There are a few things we need to remember about error handling:

1. "On Error Resume Next" ignores the error.
2. "On Error GoTo" gives Excel a destination when it encounters an error.
3. It uses the description to show the exact error that occurred to the user.