VBA Error Handling

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,

vba error example image1

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.

vba error example image2

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:

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

Explanation

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,

vba error example 3

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?

You can download this VBA Error Handling Excel Template here – VBA Error Handling Excel Template

Example #1

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,

Code:

Sub Sample()
On Error Resume Next
MsgBox 4 / 2
MsgBox 4 / 0
MsgBox 4 / 1
End Sub
vba error example 1.1

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.

vba error example 1.2

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.read more. 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,

Code:

Sub Sample()
On Error GoTo az
MsgBox 4 / 2
MsgBox 4 / 0
MsgBox 4 / 1
End Sub
vba error example 1.3

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
vba error example 1.4

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
vba error example 1.5

Now when we run this code, we see the result displayed.

vba error example 1.6

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.

vba error example 1.7

The err.description above in the code helps us to show exactly what the 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.

example 2.1

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,

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
example 2.2

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

example 2.3

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,

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
example 2.4

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
example 2.5

Now we need to define the destination Bx what it should do when it encounters 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
example 2.6

Now when we execute the code, we can see that excel first gives us the value for C.

example 2.7

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

example 2.8

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.read more VBA.

Things to Remember

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. The description is used to show the exact error that occurred to the user.

Recommended Articles

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 –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *