VBA On Error GoTo

Excel VBA On Error GoTo

Errors are part and parcel of any coding language and VBA macros in no different from this. In my opinion, finding why the error occurs is 90% of the job done and 10% lies in how to fix that error. In every coding language coders use their own way of error handling errors in their coding, so does we too use in VBA coding. Often times we need to ignore the error or often times we may want to go to specific thing when the error occurs. “On Error” is the statement we need to use in VBA to handle errors in excel VBA.

This statement has three types of statements and below are the list.

In this article, we will see how these three statements are used in VBA coding to handle any sort of errors.

Examples of On Error GoTo Statement in Excel VBA

Below are the examples of excel VBA on error GoTo statement.

VBA Statement #1 – On Error Resume Next

As the statement itself says “On Error Resume Next” means whenever the error occurs in the code “resume” next line of the code by ignoring the error line code. Now take a look at the below code.

In the below code I have mentioned the worksheet names and asked to enter the value in the first cell as “Error Testing”.

Code:

Sub On_Error_Resume_Next()
  Worksheets("Ws 1").Select
  Range("A1").Value = "Error Testing"

  Worksheets("Ws 2").Select
  Range("A1").Value = "Error Testing"

  Worksheets("Ws 3").Select
  Range("A1").Value = "Error Testing"

  Worksheets("Ws 4").Select
  Range("A1").Value = "Error Testing"

End Sub

Now I have below worksheets in my workbook.

I will run the code and see what happens.

We got “Subscript Out of Range” error, click on “Debug” to see in which line we got the error.

So in the line “Worksheets(“Ws 3”).Select” we got an error, this is because in our workbook there is no worksheet named as “Ws 3”, so encountered an error.

In such cases we may want to ignore the error and resume the execution of the code to the next line, this is where our “On Error Resume Next” error handler comes into the picture.

All we need to do is to add the line “On Error Resume Next” at the start of the macro.

Now execute this code and it will not show any error message because whenever code encounters an error it will ignore the error and resume to next line of code.

VBA Statement #2 – On Error GoTo 0

This is not an error handler rather error message enabler after we disable the error message by using “On Error Resume Next” statement.

One you use the “Resume Next” statement VBA macros starts to ignore any kind of error it occurs and keeps continuing with the next line of codes. But we don’t want this to happen all the time because some errors we need to ignore intentionally other we need a notification.

If any specific set of code throws an error in that block of code only we need to ignore error other parts of the code we don’t want to ignore the error.

Look at the below image for the usage of “On Error GoTo 0” statement in VBA.

So now errors will be ignored until code finds the error notify enabler “On Error GoTo 0”. Once this line of code executes macros back to normal and starts throwing error messages as usual.

VBA Statement #3 – On Error GoTo Label

We have seen how to ignore the error and how to enable back the error notify. Now using this method we can go to a specific line of code.

In this method “Label” means we can give any name to this label and the same label should be given at the required line of code as well.

For an example look at the same code from the above example.

Now let’s execute the code line by line by pressing the F8 function key.

Now macro will read the error handler statement, press F8 key execute the first 2 worksheet code.

Now macro is about to execute the third worksheet code which is not there in the workbook, press F8 key and see what happens.

Because macro encountered an error in the below line of code it has jumped to the error handler label “ErrorMessage” which was described through “On Error GoTo [Label]” statement.

Now message box will show the message as “Error Occurred & Exiting the Macro”.

Things to Remember

• In VBA, On Error GoTo 0 will enable the error notification once again, so don’t forget to add this after supplying error handler.
• You need to absolutely sure of in which part of the code you want to ignore the error, so enclose the error handler to only for that block of code.

Recommended Articles

This has been a guide to VBA On Error GoTo. Here we discuss how to use VBA On Error GoTo statement with its three types to handle any sort of errors along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Month Example
• Examples of VBA CreateObject
• VBA Replace Function
• CLng in VBA
• VARTYPE in VBA
• OverFlow Error in VBA
• Type Mismatch Error in VBA
• 1004 Error in VBA
• IFERROR in VBA