• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA On Error GoTo

Home » VBA » VBA Error Handling Functions » VBA On Error GoTo

By Sharmila Reddy Leave a Comment

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.

  1. VBA On Error Goto 0
  2. VBA On Error Goto [label]
  3. VBA 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.

Examples of On Error GoTo Statement in Excel VBA

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

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

VBA On Error Resume Next Example 1

Now I have 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

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

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 as “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 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.

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.

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.

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 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 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 “On Error GoTo [Label]” statement.

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

VBA On Error GoTo Example 3-3

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
0 Shares
Share
Tweet
Share

Filed Under: VBA, VBA Error Handling Functions

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

* Please provide your correct email id. Login details for this Free course will be emailed to you

WallStreetMojo

Download VBA On Error GoTo Statement Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More