VBA On Error Goto 0

Excel VBA On Error Goto 0

VBA On Error GoTo 0 is an error handler statement used to disable the enabled error handler in the procedure. It is referred to as “Error Handler Disabler”.

Error handling in any of the programming languages is a master class that all the coders need to understand. VBA  programming language too, and we also have error handling techniques in this programming language.“On Error Resume Next” enables the error handler, and “On Error GoTo 0” will disable the enabled error handler.

Both “On Error Resume Next” & “On Error GoTo 0” are pairs that need to be used in tandem for the efficiency of the code. To handle the error, we need to start with the statement “On Error Resume Next” and to end this error handler. We need to use the statement “On Error GoTo 0”.

Any line code written between these statements will ignore any kind of error that occurred in the proceedings.

VBA On Error Goto 0

How to use On Error GoTo 0 Statement?

You can download this VBA On Error Goto 0 Excel Template here – VBA On Error Goto 0 Excel Template

For example, look at the below code.

Code:

Sub On_ErrorExample1()

    Worksheets("Sheet1").Select
    Range("A1").Value = 100
    Worksheets("Sheet2").Select
    Range("A1").Value = 100

End Sub

VBA On Error Goto 0 Example1

What the above code does is it will first select the worksheet named “Sheet1,” and in cell A1 it will insert the value 100.

Code:

Worksheets("Sheet1").Select
Range("A1").Value = 100

Then it will select the worksheet named “Sheet2” and insert the same value.

Code:

Worksheets("Sheet2").Select
Range("A1").Value = 100

Now I have the below sheets in my workbook.

 Example1.1

There are no sheets called “Sheet1” & “Sheet2”. When we run the code, it will encounter an error like the below.

 Example1.2

Since there is no sheet named as “Sheet1”, it has encountered a “Subscript out of range” error. To handle this error, I will add an error handler statement “On Error Resume Next” at the top of the macro.

Code:

Sub On_ErrorExample1()

    On Error Resume Next
    Worksheets("Sheet1").Select
    Range("A1").Value = 100
    Worksheets("Sheet2").Select
    Range("A1").Value = 100

End Sub

Example1.3

Now run the code and see what happens.

It won’t give any error messages because the error handler statement On Error Resume Next is enabled.

Imagine the scenario where we need to ignore the error in case of non-availability of the worksheet “Sheet1,” but we need to notify if there is no worksheet called “Sheet2”.

Since we have added On Error Resume Next at the top, it has started to handle the error, but at the same time, we need to specify how many lines we need to ignore this error.

In this example, we just need to ignore the error for the first worksheet but for the second sheet onwards. We need the error to occur if there is no worksheet “Sheet2”. So after the first worksheet code adds the error disable line On Error GoTo 0.

Code:

Sub On_ErrorExample1()

    On Error Resume Next
    Worksheets("Sheet1").Select
    Range("A1").Value = 100
    On Error GoTo 0
    Worksheets("Sheet2").Select
    Range("A1").Value = 100

End Sub

 Example1.4

Now run the code line by line to see the impact by pressing the F8 key.

VBA On Error Goto 0 Example1.5

Now, if you press the F8 key once code execution jumps to the next line, and the active line task will be executed. Now the active line (yellow-colored line) is “On Error Resume Next” error handler, and the error handler will be enabled.

VBA On Error Goto 0 Example1.6

Now any error occurs. It will be ignored until it executes the error handler disables code “On Error GoTo 0” statement.

In the previous attempt, we have encountered errors but press the F8 key one more time and see the magic.

Example1.7

Without giving any kind of error, it has resumed execution of the code even though there is not worksheet “Sheet2” to select. Now press F8 again.

VBA On Error Goto 0 Example1.8

Since there was no Sheet1, it cannot insert the value in the A1 cell as 500 but what it does is it will insert the value of 500 to cell A1 whichever worksheet is active. My active sheet when I am executing the code was “Sheet3”, so the value of 100 is inserted to the cell A1.

 Example1.10

Now the active line of code is “On Error GoTo 0” by pressing the F8 key. This line task will be executed.

VBA On Error Goto 0 Example1.11

Since “On Error GoTo 0” is executed, it has stopped the process of error handling and again starts to show errors if any occurs. Press the F8 key and see the error.

 Example1.12

In the previous case without On Error GoTo 0, it has ignored this error as well, but since we have added an error handler disabler, it has started to show the error again.

Things to Remember here

  • Both On Error Resume Next and On Error GoTo 0 needs to be used as “Error Handler Enabler” and “Error Handler Disabler.”
  • Any line of codes between these two statements encounters an error it will be ignored.
  • If there is On Error GoTo 0 statement, then after the exit of the subprocedure error handler will be disabled.

Recommended Articles

This has been a guide to VBA On Error Goto 0. Here we discuss how to use on error goto 0 statement in VBA excel in handling any sort of errors along with a practical example and a downloadable template. Below you can find some useful excel VBA articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>