VBA On Error

Excel VBA On Error Statement

VBA On Error statement is a type of error handling mechanism which is used to guide the code to do what if it encounters any type of error, generally when a code encounters an error the execution stops but with this statement in the code the execution of the code continues as it has set of instructions to do when it encounters an 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 another 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.

What is an Error?

An error is nothing but a line of code cannot be executed because of the functionality or the wrong code. So try to anticipate the error and handle it.

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

An error is of three types 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 VBA to handle all these kinds of errors i.e. “On Error” statement.

Types of On Error Statements

The key point of handling errors in VBA is the “On Error” statement. 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 the kind of error it has encountered. As soon as VBA executes the code 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) the next line of code without displaying any error messages. It 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 the assigned label. This makes the code to jump to the specific line provided by the coder.

Top 3 Ways to Handle Errors in VBA

#1 – 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 – 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 the number. We just need to change the code, below is the code.

Code:

Err.Description

It will show a description like this.

Things to Remember

• After entering “On Error Resume Next” at the end of code don’t forget to add the statement “On Error GoTo 0”
• The label name should be the same in both places.
• Label names 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) On Error GoTo 0, 2) On Error Resume Next and 3) On Error GoTo Statement along with some examples and downloadable excel template. Below are some useful excel articles related to VBA –

• Excel VLOOKUP Errors
• Formula of Standard Error
• Formula Errors in Excel
• Excel VBA ISERROR Function
• VBA Set Range