WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA On Error

VBA On Error

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

Excel VBA On Error Statement

VBA On Error statement is a type of error handling mechanism which is used to guide the code to do what if it encounters any type of error, generally when a code encounters an error the execution stops but with this statement in the code the execution of the code continues as it has set of instructions to do when it encounters an error.

Anticipating the error in the code makes you a pro in VBA coding. You can’t make the code 100% efficient. Even if you are confident about your code one or another way, it may throw up an error.

It almost an impossible task to identify and handle every kind of error, but we have different ways of handling an error in VBA. While writing the code, you may not anticipate the kind of error code can throw up, but if any error comes, you will up spending more time debugging than writing the code itself.

What is an Error?

An error is nothing but a line of code cannot be executed because of the functionality or the wrong code. So try to anticipate the error and handle it.

For example, if you try to delete the sheet which is not there, then obviously we cannot execute that line of code.

An error is of three types one is compiled error due to undeclared variables. The second one is data entry error due to wrong entries by the coder, and the third one is run time error due to VBA cannot recognize the line of code. For trying to access or work on a worksheet or workbook, which is not there.

But we have a statement in VBA to handle all these kinds of errors, i.e., “On Error” statement.

VBA On Error

Types of On Error Statements

The key point of handling errors in VBA is the “On Error” statement. For example, On Error, “resume next line,” “go to or jump to another line,” etc.…

On Error statement has three kinds of statements to it.

  1. GoTo 0 means whenever the run time error occurs, excel or VBA should display the error message box saying the kind of error it has encountered. As soon as VBA executes the code, it disables all the error handlers in that particular block on the code.
  2. Resume Next means whenever the error occurs, this statement instructs the excel to ignore that error and move on to (resume next) the next line of code without displaying any error messages. It does not mean it will fix the error; rather, it just ignores the error.
  3. GoTo [label] means whenever VBA encounters an error, go to the assigned label. This makes the code to jump to the specific line provided by the coder.

Top 3 Ways to Handle Errors in VBA

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

#1 – On Error Resume Next

Assume you are dividing the value of 20 by 0, and you have declared the variable to assign the result of the division to it.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub OnError_Example1()

Dim i As Integer

i = 20 / 0

End Sub

On Error Example 1

If you run this code, it will throw the below error.

On Error Example 1-1

So you cannot divide any number by zero value. Run time error number is 11, i.e., Division by Zero.

Now I will add one more line to the code.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer

i = 20 / 0

j = 20 / 2

End Sub

Visual Basic Application Example 1-2

Now I will add the statement On error resume next at the top.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer

On Error Resume Next

i = 20 / 0

j = 20 / 2

End Sub

On Error Example 1-3

Now, if I execute this code, it won’t give me any error messages; rather, it will execute the next line of code, i.e., j = 20 / 2.

#2 – On Error GoTo Label

I have declared three variables.

Code:

Sub OnError_Example1()
Dim i As Integer, j As Integer, k As Integer

On Error Example 1-4

For all these three variables, I will assign a division calculation.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

i = 20 / 0

j = 20 / 2

k = 10 / 5

Visual Basic Application Example 1-5

The result of all these three calculations will be shown in the message box.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

i = 20 / 0

j = 20 / 2

k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

On Error Example 1-6

Now I will try to execute this code since the calculation of “I” is not proper. We will get run time error 11.

On Error Example 1-7

Now I will add the “On Error Resume Next” statement.

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

On Error Resume Next

i = 20 / 0

j = 20 / 2

k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

Visual Basic Resume Next 1-8

If I execute this, it will skip the “I” calculation and execute the remaining two calculations, and the result is as follows.

On Error Example 1-9

Now instead of “On Error Resume Next,” I will add “On Error GoTo KCalculation.”

Code:

Sub OnError_Example1()

Dim i As Integer, j As Integer, k As Integer

On Error GoTo KCalculation:

i = 20 / 0

j = 20 / 2

KCalculation:
k = 10 / 5

MsgBox "The value of i is " & i & vbNewLine & "The value of j is " & j & _
vbNewLine & "The value of k is " & k & vbNewLine

End Sub

VBA On Error Example 1-10

Note: Here, “KCalculation” is the label name I had given; you can give your own label name without any space.

Now, if I execute this line of code, it will not jump to the next line. Rather it will jump to the label name I have entered, i.e., “KCalcualtion.” Here it will ignore the error given by “I,” and also, it will not execute “j” calculation, but straight away, it jumps to “KCalcualtion.”

#3 – Print Error Number in VBA

At the end of the code, we can also print the error number in a separate message box. The following line of code will do this job.

Code:

Err.Number

Visual Basic Application Print err 1-11

Now I will run this code first message box will show the calculation results.

Visual Basic Application output 1-12

Click on OK. It will show one more message box to show the error number.

VBA On Error Example 1-12

We go 11; as a result, i.e., Division by Zero.

We can also get the error description instead of the number. We just need to change the code. Below is the code.

Code:

Err.Description

VBA On Error Example 1-13

It will show description like this.

VBA On Error Example 1-14

Things to Remember

  • After entering “On Error Resume Next” at the end of code, don’t forget to add the statement “On Error GoTo 0.”
  • The label name should be the same in both places.
  • Label names need not be defined well in advance.
  • In the end, always see what was the error occurred through the separate message box.

Recommended Articles

This has been a guide to VBA On Error. Here we learn the 3 types of error statements  – 1) On Error GoTo 0, 2) On Error Resume Next, and 3) On Error GoTo Statement along with some examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • Excel VLOOKUP Errors
  • Formula of Standard Error
  • Formula Errors in Excel
  • Excel VBA ISERROR Function
  • VBA Set Range
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. 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

WallStreetMojo

Free Excel Course

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

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

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

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

Login

Forgot Password?

WallStreetMojo

Download VBA On Error Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More