VBA On Error GoTo

Errors are part and parcel of any coding language. VBA macros are no different from this. In our opinion, finding why the error occurs is 90% of the job, and 10% lies in how to fix them. In every coding language, coders use their way of handling errors, as we use in VBA coding. Often, we need to ignore the error, or we may want to go to specific things when the error occurs. “On Error” is the statement we need to use in VBA to handle errors.

This statement has three types of statements. Below is the list:

1. On Error Goto 0
2. On Error Goto
3. On Error Resume Next

This article will show how these three statements are used in VBA coding to handle any errors.

#1 - On Error Resume Next

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

In the below code, we 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, we have the below worksheets in my workbook.

• We will run the code and see what happens.

• We got the “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. It is because, in our workbook, there is no worksheet named “Ws 3”, so we encountered an error.

In such cases, we may want to ignore the error and resume the execution of the code to the next line. Again, 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. It will not show any error message because whenever the code encounters an error, it will ignore it and resume to the next line of code.

#2 - On Error GoTo 0

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

Once you use the “Resume Next” statement, VBA macros start to ignore any error it occurs and keeps continuing with the next line of codes. But we do not want this to happen all the time because some errors we need to ignore intentionally. Others we need a notification.

If any specific set of code throws an error in that block of code, only we need to ignore errors in other parts of the code. We do not want to ignore the error.

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

So, it will ignore errors until the code finds the error notify enabler “On Error GoTo 0” once this line of code executes, Macros go back to normal and start throwing error messages as usual.

#3 - On Error GoTo Label

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

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

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

Now, let us execute the code by pressing the F8 function key.

Now, the Macro will read the error handler statement. Next, press the F8 key to execute the first two worksheet codes.

Now, the Macro is about to execute the third worksheet code, which is not there in the workbook. Press the 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 the “On Error GoTo ” statement.

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

• VBA "On Error GoTo 0" will enable the error notification again, so do not forget to add this after supplying an error handler.
• It would help if you were sure which part of the code you want to ignore the error, so enclose the error handler only for that block of code.

This article has been a guide to VBA On Error GoTo. Here, we discuss using the VBA On Error GoTo statement with its three types to handle errors, practical examples, and a downloadable Excel template. Below you can find some useful Excel VBA articles: -

• VBA Replace Function
• CLng in VBA
• Type Mismatch Error in VBA
• IFERROR in VBA