VBA On Error Resume Next

Excel VBA On Error Resume Next

Error Handling is a very useful & significant mechanism for programming languages like VBA Error control or prevention which is an aspect of Error handling which means taking effective & significant measures inside a VBA script to avoid the occurrence of error pop up message. The Excel VBA On Error Resume Next statement ignores the code line that causes an error and continues or routes execution to the next line following the line that caused the error.

Basically, On error resume next is used when you want to ignore the error & continue or resume the code execution to the next cell.

Types of Errors in VBA

Below are the different types of Errors in VBA:

1. Syntax Error or Parsing error.
2. Compile Or Compilation Error.
3. Runtime Error.
4. Logical Error.

The above errors can be rectified with the help of debugging & ‘On Error’ Statements in a code. The Runtime Error can be prevented with the help of On Error Resume Next.

VBA Runtime Error:

Before the explanation of On Error Resume Next, you should be aware of runtime error when impossible mathematical statements or terms present in a statement, then this runtime error occurs.

Examples of Excel VBA On Error Resume Next

Below are the different examples of On Error Resume Next in Excel VBA:

VBA On Error Resume Next – Example #1

Here, an error will be ignored, and the execution of code will move on. In the below-mentioned example, 6 can’t be divided by zero, if you run it without entering On Error Resume Next statement, then below mentioned runtime error occurs.

Code:

Sub RUNTIME_1()
MsgBox 6 / 0
End Sub

If On Error Resume Next in entered at the top of code after SUB statement, it ignores runtime error and moves on to next statement, results in an output of 6/2 (Popup message box with result of it).

Code:

Sub RUNTIME_2()
On Error Resume Next
MsgBox 6 / 0
MsgBox 6 / 2
End Sub

VBA On Error Resume Next – Example #2

I can use On Error Resume Next anywhere in the code from the beginning to the end. In the below-mentioned example, I have to make a 3 calculation i.e.

9/3 =?

9/0 =?

9/2 =?

In the above-mentioned example, you can observe a second calculation where any number can’t be divided by zero, i.e. 9 can’t be divided by zero in the second step. Suppose if you run the macro without entering On Error Resume Next statement, now I can execute the code step by step with the help of step into or F8 key to understand how it works.

Now, I run the above code, by clicking on step Into option or F8 key frequently, step by step. I just copy the above code and start running it step by step, for the first step of calculation message box 3 appears.

Code:

Sub RUNTIME_3()
MsgBox 9 / 3
MsgBox 9 / 0
MsgBox 9 / 2
End Sub

When I run the second line of code, then below mentioned runtime error occurs at the second step of a code, where any number can’t be divided by zero, i.e. 9 can’t be divided by zero in the second step.

Code:

Sub RUNTIME_3()
MsgBox 9 / 3
MsgBox 9 / 0
MsgBox 9 / 2
End Sub

Now, if I even click on debug, it can’t proceed further, where it will take me to the second line of code (It gets highlighted in yellow color), where I need to do the correction. So, here, if further click on Step Into option or F8 key, the third calculation in this code will not get executed.

To rectify or handle this runtime error, I have to use or execute the OnError Resume Next statement above a second code or at the beginning of code below the substatement. so that it will skip that line of code and moves on to the third step of code and calculate the value.

Code:

Sub RUNTIME_30()
MsgBox 9 / 3
On Error Resume Next
MsgBox 9 / 0
MsgBox 9 / 2
End Sub

OR

Sub RUNTIME_31()
On Error Resume Next
MsgBox 9 / 3
MsgBox 9 / 0
MsgBox 9 / 2
End Sub

Now, I have added on error resume next statement to the code, where you can use any one of above code, if you run it step by step, you will get a two-message popup, one is the output first code and third code calculation. On Error Resume Next will ignore the runtime error in the second code and move on to the third code.

  

VBA On Error Resume Next – Example #2

We will now see the Combination of On Error Resume Next with Error GoTo 0. In the below code, it will ignore errors until it reaches On Error GoTo 0 statement. After On Error GoTo 0 statement, the code goes back or proceed to normal error checking and triggers the expected error ahead.

Code:

Sub onError_Go_to_0_with_Resume_next()
On Error Resume Next
Kill "C:TempFile.exe"
On Error GoTo 0
Range("A1").Value = 100 / "PETER"
End Sub

When I run the above code, it will showcase the division error i.e. Type mismatch (numeric value can’t be divided by text).

Now, you can save your workbook as an “Excel macro-enabled workbook”. By clicking on save as at the left corner of the worksheet.

when you open this excel file again, you can use below-mentioned shortcut key i.e.

Function + Alt + F11 short cut key helps you out to access all the created macro code of the workbook. Function + Alt + F8 short cut key helps you out to open a “Macro” dialog box window, which contains all the macro names, where you can run a specific macro code of your choice.

Things to Remember

• Run time error will be silently trapped and stored in the global Err object
• On Error Resume Next usually prevent an interruption in code execution.
• Error object properties (Err Object) get cleared automatically when Resume Next is used in an error-handling routine

Recommended Articles

This is a guide to VBA On Error Resume Next. Here we discuss different types of Error in VBA Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Macros
2. VBA DateDiff
3. VBA Protect Sheet
4. VBA Environ