VBA On Error

VBA On Error statement is a type of error handling mechanism used to guide the code to do what if it encounters any error. Generally, when a code encounters an error, the execution stops. Still, with this statement in the code, the execution of the code continues as it has a set of instructions to do when it encounters an error.

Anticipating the error in the code makes you a pro in VBA coding. However, you cannot make the code 100% efficient. Even if you are confident about your code one way or another, it may throw an error.

It is almost impossible 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 spend more time debugging than writing the code itself.

An error is nothing but a line of code that one cannot execute 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 a compiled error due to undeclared variables. The second is a data entry error due to wrong entries by the coder, and the third is a run time error because VBA cannot recognize the line of code. For trying to access or work on a worksheet or workbook which is not there.

But we have a statement in VBA to handle all these kinds of errors, i.e., the “On Error” statement.

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 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 means whenever VBA encounters an error, go to the assigned label. It makes the code jump to the specific line provided by the coder.

#1 - On Error Resume Next

Assume you are dividing the value of 20 by 0. 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. Therefore, the run time error number is 11, i.e., “Division by zero.”

Now, we 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

We 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 we execute this code, it will not give us any error messages. Rather, it will execute the next line of code, i.e., j = 20 / 2.

#2 - On Error GoTo Label

We have declared three variables.

Code:

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

For all these three variables, we will assign a 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

It will show the result of all these three calculations 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

We will try to execute this code since the " I " calculation is improper. Unfortunately, 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 we execute this, it will skip the "I" calculation and execute the remaining two calculations, and the result is as follows.

Now, instead of "On Error Resume Next," we 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

If we execute this line of code, it will not jump to the next line. Rather it will jump to the label name we have entered, i.e., "KCalcualtion." Here, it will ignore the error given by "I." Also, it will not execute the "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, we will run this code. The 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 a result, i.e., Division by Zero.

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

Code:

Err.Description

It will show a description like this.

• After entering "On Error Resume Next" at the end of the code, do not forget to add the statement "On Error GoTo 0."
• The label name should be the same in both places.
• We need not define label names well in advance.
• In the end, always see what the error occurred through the separate message box.