WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Learn VBA » VBA On Error GoTo

VBA On Error GoTo

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA On Error GoTo

Errors are part and parcel of any coding language, and VBA macros are 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 handling errors in their coding, so do we too use in VBA coding. Often times, we need to ignore the error, or often times 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.

VBA On Error GoTo

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

  1. On Error Goto 0
  2. On Error Goto [label]
  3. On Error Resume Next

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

How to use VBA On Error Statements?

You can download this VBA On Error GoTo Statement Template here – VBA On Error GoTo Statement Template

#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

VBA On Error Resume Next Example 1

Now I have the below worksheets in my workbook.

VBA On Error Resume Next Example 1-1

  • I will run the code and see what happens.

VBA On Error Resume Next Example 1-2

VBA On Error Resume Next Example 1-3

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

VBA On Error Resume Next Example 1-4

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.

VBA On Error Resume Next Example 1-5

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 the next line of code.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

#2 – On Error GoTo 0

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

One you use the “Resume Next” statement, VBA macros start 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 errors in other parts of the code. We don’t want to ignore the error.

VBA On Error GoTo Example 2

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.

#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 example, look at the same code from the above example.

Error Message Example 2-1

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

Error Message Example 3

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

Error Message Example 3-1

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

Error Message Example 3-2

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 [Label]” statement.

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

VBA On Error GoTo Example 3-3

Things to Remember

  • Vba On Error GoTo 0 will enable the error notification once again, so don’t forget to add this after supplying an error handler.
  • You need to be absolutely sure of in which part of the code you want to ignore the error, so enclose the error handler 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 excel template. Below you can find some useful excel VBA articles –

0 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>