VBA On Error

Excel VBA On Error

Anticipating the error in the code makes you a pro in VBA coding. You can’t make the code 100% efficient, even if you are confident about your code one or other way it may throw up an error.

It almost an impossible task to identify and handle every kind of error, but we have different ways of handling an error in VBA. While writing the code you may not anticipate the kind of error code can throw up but if any error comes you will up spending more time in debugging than writing the code itself.

But in today’s article, we will discuss the ways of handling errors in VBA coding. Read on…

What is An Error in VBA?

Assume you are working in your kitchen and suddenly a cockroach (an error) appears in front of you so often. You are scared of that cockroach and you try to kill that but cockroach enters the least possible corners in the kitchen.

Now in order to kill the cockroach, you need to search the entire kitchen (roam around the entire set of code) which is frustrating.

However, better ways of handling these error are using cockroach handler’s chemicals at anticipated (error expected lines) places in the house.

So in VBA too anticipate the error and handle it. An error is nothing but a line of VBA code cannot execute it because of the functionality or the wrong code.

For example, if you try to delete the sheet which is not there then obviously VBA code cannot execute that line of code.

An error has three ways in VBA one is compiled error due to undeclared variables. The second one is data entry error due to wrong entries by the coder, and the third one is run time error due to VBA cannot recognize the line of code. For trying to access or work on worksheet or workbook which is not there.

But we have a statement in to handle all these kind of errors i.e. “On Error” statement.

On Error Statement in Excel VBA

The key point of handling errors in VBA is the “On Error” statement. The error means when the error occurs or happens or found what to do?

For example On Error “resume next line”, “go to or jump to another line”, etc…

On Error statement has three kinds of statements to it.

1. GoTo 0 means whenever the run time error occurs excel or VBA should display the error message box saying you the kind of error it has encountered. As soon as VBA execute the code On Error GoTo 0 it disables all the error handlers in that particular block on the code.
2. Resume Next means whenever the error occurs this statement instructs the excel to ignore that error and move on to (resume next) next line of code without displaying any error messages. VBA On Error Resume Next does not mean it will fix the error rather it just ignores the error.
3. GoTo [label] means whenever VBA encounters an error go to assigned label. This makes the code to jump to the specific line provided by the coder.

How to Use On Error Statement in Excel VBA?

Below are the examples of On Error statements in Excel VBA.

#1 – VBA On Error Resume Next

Assume you are dividing the value of 20 by 0 and you have declared the variable to assign the result of the division to it.

Code:

Sub OnError_Example1()

Dim i As Integer

i = 20 / 0

End Sub

If you run this code it will throw the below error.

So you cannot divide any number by zero value. Run time error number is 11 i.e. Division by Zero.

Now I will add one more line to the code.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer

i = 20 / 0

j = 20 / 2

End Sub

Now I will add the statement “On error resume next at the top.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer

On Error Resume Next

i = 20 / 0

j = 20 / 2

End Sub

Now if I execute this code it won’t give me any error messages rather it will execute the next line of code i.e. j = 20 / 2.

#2 – VBA On Error GoTo Label

I have declared three variables.

Code:

Sub OnError_Example1()
Dim i As Integer, j As Integer, k As Integer

For all these three variables I will assign division calculation.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

i = 20 / 0

j = 20 / 2

k = 10 / 5

The result of all these three calculations will be shown in the message box.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

i = 20 / 0

j = 20 / 2

k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

Now I will try to execute this code since the calculation of “I” is not proper we will get run time error 11.

Now I will add the “On Error Resume Next” statement.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

On Error Resume Next

i = 20 / 0

j = 20 / 2

k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

If I execute this it will skip “I” calculation and execute the remaining two calculations and the result is as follows.

Now instead of “On Error Resume Next” I will add “On Error GoTo KCalculation”

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

On Error GoTo KCalculation:

i = 20 / 0

j = 20 / 2

KCalculation:
k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

Now if I execute this line of code it will not jump to the next line rather it will jump to the label name I have entered i.e. “KCalcualtion”. Here it will ignore the error given by “I” and also it will not execute “j” calculation but straight away it jumps to “KCalcualtion”.

#3 – Print Error Number in VBA

At the end of the code, we can also print the error number in a separate message box. The following line of code will do this job.

Code:

Err.Number

Now I will run this code first message box will show the calculation results.

Click on OK, it will show one more message box to show the error number.

We go 11 as the result i.e. Division by Zero.

We can also get the error description instead of number. We just need to change the code, below is the code.

Code:

Err.Description

It will show the description like this.

Things to Remember Here

• After entering “On Error Resume Next” at the end of VBA code don’t forget to add the statement “On Error GoTo 0”
• Label name should be same in both the places.
• Label name need not be defined well in advance.
• In the end, always see what was the error occurred through the separate message box.

Recommended Articles

This has been a guide to VBA On Error. Here we learn the 3 types of error statements  – 1) VBA On Error GoTo 0, 2) VBA On Error Resume Next and 3) VBA On Error GoTo Statement in Excel VBA along with some simple to advanced examples. Below are some useful excel articles related to VBA –

• INT (Integer) Function in VBA
• VBA Integer Function
• Set in Excel VBA
• Excel VBA Code
• Excel VBA MsgBox
• Use Excel VBA UBOUND
• Use VBA Left Function
• Use VBA Worksheet Function