• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA Error Handling

Home » Excel » Blog » VBA » VBA Error Handling

VBA Error Handling

Excel VBA Error Handling

Error Handling is a very useful & significant mechanism for programming languages like VBA. Error control or prevention is an aspect of Error handling which means taking effective & significant measures inside a VBA script to avoid the occurrence of error pop up message

Different Types of Errors in VBA

  1. Syntax Error or Parsing error
  2. Compile or Compilation Error
  3. Runtime Error
  4. Logical Error

The above errors can be rectified with the help of below mentioned debug & different ‘On Error’ Statements inserted in between a code.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

On Error Resume Next

On Error Goto 0

On Error Goto <Label>

On Error Goto -1

VBA Error Handling with the help of Different ‘ON ERROR’ Statements

You can download this VBA Error Handling Excel Template here – VBA Error Handling Excel Template

Example #1 – VBA Compile Errors

When there is an error in a statement or syntax of VBA code, when you mistype a code by mistake, it will be highlighted in red color depending on the setting options in tools (If you have selected Auto syntax check).

VBA Error Handling

A popup message box of compile error will appear when you run the code with incorrect syntax.

Code:

Sub SYNTAX_ERROR()
MsgBox this is my first program
End Sub

VBA Error Handling1

“COMPILE ERROR: VARIABLE NOT DEFINED” is the most common error which appears as a popup message. when the referencing variable is not defined, this error occurs.

Code:

Sub VBA_FORMAT1()
A = 19049.83
A = Format(A, "STANDARD")
MsgBox A
End Sub

VBA Error Handling2

In the above example, I have not declared the variable type as String therefore, this error occurs. So, I need to declare a variable as Dim A As String.

Popular Course in this category
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,695 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

Code:

Sub VBA_FORMAT1()
Dim A As String
A = 19049.83
A = Format(A, "STANDARD")
MsgBox A
End Sub

VBA Error Handling 3

Example #2 – VBA Runtime Error

When an impossible mathematical statements or terms present in a statement, then this runtime error occurs.

Code:

Sub RUNTIME_1()
MsgBox 6 / 0
End Sub

VBA On Error Resume Next Example1-10

Example #3 – VBA Logical Errors or Bugs

These errors are very difficult to track, neither will get highlighted nor pop-up error message appears. it will result in unexpected actions & incorrect results.

Example: When two variables are present in code, it may contain an incorrect one. In this case, logical error occurs.

How to Prevent Errors in VBA?

Let’s check out How to Prevent the above Different Types of Errors in VBA Excel.

Step 1: To open a VB Editor window Select or click on Visual Basic in the Code group on the Developer tab or you can directly click on Alt + F11 shortcut key.

Developer Tab

Step 2: To create a blank module, under the Microsoft excel objects, right-click on sheet 1(VB_ERROR HANDLING) & Insert Module so that a new blank module gets created.

Insert Module

VBA Error Handling With Debug Option

It’s better to compile code before we run it. To follow compilation, the below steps need to be followed. In the VB menu toolbar, under the Debug option, we need to select a compile VBA project. When you click on it, it checks the code step by step, once it finds the error, it will highlight it & popup message appears, thereby you need to correct it. once it is corrected you need to compile to find the next error in the code.

VBA Error Handling With Debug Option

Note: With the help of the compile option, we can only rectify the compile & syntax error.

VBA Error Handling with the help of Different ‘ON ERROR’ Statements

1. On Error Resume Next

Here, the error will be ignored and code will move on.

In the below-mentioned example, 6 can’t be divided by zero, if you run it without entering On Error Resume Next statement, then below mentioned runtime error occurs.

Code:

Sub RUNTIME_1()
MsgBox 6 / 0
End Sub

ON ERROR RESUME NEXT

If On Error Resume Next is entered at the top of code after Sub statement, it ignores runtime error and moves on to the next statement, which results in the output of 6/2 i.e. 3 (Popup message box with result of it).

Code:

Sub RUNTIME_2()
On Error Resume Next
MsgBox 6 / 0
MsgBox 6 / 2
End Sub

VBA Error Handling

2. On Error GoTo 0 and Error GoTo -1

‘On Error GoTo 0’ will stop the code on the specific line that causes the error and shows a message box that describes or indicates the error.

Code:

Sub onError_Go_to_0()
On Error GoTo 0
Kill "C:TempFile.exe"
Range("A1").Value = 100 / "PETER"
End Sub

VBA Error Handling9

Usually, it showcases default error checking behavior, it is significant when it is used along with ‘On Error Resume Next’.

Usually, you can observe Runtime Error Message box, contains ‘Continue’, ‘End’, ‘Debug’ and ‘Help’ options. let’s check out the uses of each of them.

  • Continue option will ignore the exception and continue the code if it is possible.
  • The end option terminates the program.
  • Debug option will highlight the statement where the error has occurred. which helps you to debug or correct the code.
  • Help option will take you to open the Microsoft MSDN help page.

On Error GoTo 0 with On Error Resume Next

Code:

Sub onError_Go_to_0_with_Resume_next()
On Error Resume Next
Kill "C:TempFile.exe"
On Error GoTo 0
Range("A1").Value = 100 / "PETER"
End Sub

VBA Error Handling10

In the above code, it will ignore errors until it reaches On Error GoTo 0 statement. After On Error GoTo 0 statement, the code goes back or proceed to normal error checking and triggers the expected error ahead. when I run the above code, it will showcase the division error i.e. type mismatch (numeric value can’t be divided by text).

On Error GoTo 0 disables any error trapping currently present in the VBA code i.e. turn off the error handling in the main code whereas On Error GoTo -1 clears the error handling and sets it to nothing which helps or allows you to create another error trap.

3. On Error GoTo < LABEL

VBA to transfer the program control to the line followed by the label if any runtime errors are encountered i.e. code jumps to the specified label. Here, the code statements between the exception line and the label will not be executed.

This method is more suitable & significant for exiting the program gracefully if any major fatal error occurs during the execution.

In the below mentioned VBA code as soon as the error occurs at line 3, the program transfers the control to the Line 6 i.e. label (Popup message appears as “Exception handler”).

Code:

Sub OnError_Go_to_Label()
On Error GoTo Error_handler:
MsgBox 9 / 0
MsgBox "This line will not be executed"
Exit Sub
Error_handler: MsgBox "exception handler"
End Sub

Exception handler

Here you can notice that ‘Exit Sub’ should be used just before the ‘Error_handler:’ label, this is done to ensure that the Error handler block of code should stop or doesn’t execute if there is no error. Now, you can save your workbook as an “Excel macro-enabled workbook”. By click on save as at the left corner of the worksheet.

Excel macro-enabled workbook

Once again if you open a file, you can click on shortcut key i.e. Fn + Alt +f8, “Macro” dialog box appears, where you can run a saved macro code of your choice or you can click on Fn + Alt + F11 for a full macro window.

Things to Remember

  • Before you write code, you have to ensure that, break on unhandled errors is checked or selected in error. trapping option under general in the tool’s options of the VBA toolbar.
  • It’s a default setting that helps out to stop your code for errors that are not handled.
  • Break on All Errors: It will stop your code on all types of errors.
  • Break-in Class Module: In case an object such as user form is used in the code, it will highlight that the exact line causing the error.

VBA Error Handling13

Recommended Articles

This is a guide to VBA Error Handling. Here we discuss how to use VBA Error Handling in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Declare Array (Examples)
  2. VBA On Error
  3. VBA Unprotect Sheet
  4. VBA Columns | Excel Templates
  5. VBA Environ

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

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

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VBA Error Handling Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
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
EDUCBA
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
EDUCBA
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
EDUCBA
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
EDUCBA

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
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More