VBA Error Handling

What is Excel VBA Error Handling?

Excel VBA Error Handling refers to code written to handle errors occurring when an application runs. Error handling in Excel VBA is a programming technique used to anticipate and manage errors that may occur during the execution of VBA code. It helps ensure that your VBA macros run smoothly, provides valuable feedback to users, and prevents unexpected crashes or errors in your Excel applications. Consider the following example:

VBA Error Handling Intro

This example shows how the error is handled without stopping the code from running by displaying it in the message box below.

VBA Error Handling Intro - Output
Key Takeaways
  • Always include VBA error handling in your code to gracefully manage unexpected errors.
  • Proper error handling ensures VBA code runs smoothly and avoids abrupt program termination.
  • Use “Err.Number” to identify the error code and “Err.Description” to display error messages to users.
  • Error handling is especially crucial when working with external data sources or database connections.


Error handling in Excel VBA anticipates, detects, and resolves errors during code execution. VBA has three types of errors: Syntax, Compilation, and Runtime. Syntax errors occur when VBA finds something wrong with the Compilation errors, which occur when VBA compiles the code syntax in code. Compilation errors occur when VBA is compiling the code. If there is no specific code to handle an error, it is considered an unexpected error. VBA error handling statements are used to handle these unexpected errors. VBA Error handling 3022 is a form error in Microsoft Access that tells the user that the changes requested to the table were unsuccessful because they would violate a primary key or unique index.

The VBA error handling process occurs when writing code before any errors occur. A few ‘On Error’ statements can be used to handle errors in VBA.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to handle Errors in VBA Code?

Error handling in Excel VBA can be performed by following the steps below:

Step 1: Go to the “Developer” section in the toolbar and click the “Visual Basic” option. Now, the VBA Editor opens to add functions and sub-procedures. Then, click the “Insert” and “Module” buttons to create a new module or blank page.

Developer - Visual Basic
Insert - Module

Step 2: Declare a sub-procedure that handles divide by zero error.

VBA Error Handling - Handle - Step 1

Step 3: Declare the Error condition, Errorhandler, before starting the code.

VBA Error Handling - Handle - Step 3

Step 4: Define three variables to perform division: dividend, divisor, and quotient.

VBA Error Handling - Handle - Step 4

Step 5: Get values for the dividend and divisor from the user.

VBA Error Handling - Handle - Step 5

Step 6: Divide the dividend and divisor and store the value in the result variable.

VBA Error Handling - Handle - Step 6

Step 7: To deal with errors when the user inputs 0 as a divisor, we define the condition Errorhandler.

VBA Error Handling - Handle - Step 7


Sub DivideNumbers()
On Error GoTo ErrorHandler
Dim dividend As Double
Dim divisor As Double
Dim result As Double
dividend = InputBox(“Enter the dividend:”)
divisor = InputBox(“Enter the divisor:”)
result = dividend / divisor
MsgBox “The result is: ” & result
Exit Sub
MsgBox “An error has occurred: ” & Err.Description
End Sub

Step 8: Run the code to divide two numbers. The output is shown below:

VBA Error Handling - Handle - Step 8 - Dividend
VBA Error Handling - Handle - Step 8 - Divisor
VBA Error Handling - Handle - Step 8 - Output

It is how Error Handling in VBA works. Let us see some more.


Let us look at examples of implementing VBA Error Handling with different statements.

Example #1

Suppose we have a table where we want to find an item’s category when we enter the value. It can be done using the VBA Excel Vlookup function. The Vlookup (Vertical Lookup) function in VBA takes in a value of a specific row and returns the value of the value of another item of the same row but a different column.

VBA Error Handling - Example 1

If the value we search for in Vlookup does not exist, it will throw an error. Here, we learn how to deal with VBA error handling Vlookup techniques.

Step 1: Start declaring a subroutine to implement the VLOOKUP function.

VBA Error Handling - Example 1 - Step 1

Step 2: Utilize the VBA GoTo statement to jump to the Errorhandler statement in case of any error.

VBA Error Handling - Example 1 - Step 2

Step 3: Assign three variables to store the search value in lookup, the range of the table, and the result the VLookup function returns.

VBA Error Handling - Example 1 - Step 3

Step 4: Get the search term from the user.

VBA Error Handling - Example 1 - Step 4

Step 5: Using the value from the user, perform the VLookup function, which needs four parameters:

  • Value
  • Range of the table
  • Column Number
  • False or True (False for an exact match, True for an approximate match)
VBA Error Handling - Example 1 - Step 5

Print the result after that.

Step 6: When there’s an error, declare the Errorhandler label.

VBA Error Handling - Example 1 - Step 6


Sub lookupValue()
On Error GoTo ErrorHandler
Dim lookupValue As String
Dim lookupRange As Range
Dim result As Variant
lookupValue = InputBox(“Enter the lookup value:”)
Set lookupRange = Range(“A2:B10”)
result = WorksheetFunction.VLookup(lookupValue, lookupRange, 2, False)
MsgBox “The result is: ” & result
Exit Sub
Debug.Print “An error has occurred: ” & Err.Description
End Sub

Step 5: Run the code and view the output:

VBA Error Handling - Example 1 - Step 7
VBA Error Handling - Example 1 - Step 7 - Output

For error handling:

VBA Error Handling - Example 1 - handle
VBA Error Handling - Example 1 - handle - Output

Example #2

Consider an example where we need to search for a file in our system and open it. But, if the file does not exist, Excel will throw a “file not found” error. Let us see how to handle the VBA Error handling file not found.

Step 1: Initialize a sub-procedure to open an Excel file.

Example 2

Step 2: Declare a GoTo statement which makes the code jump to the ErrorHandler label.

Example 2 - Step 2

Step 3: Declare a string variable and store the file path the user gives for opening.

Example 2 - Step 3

Step 4: Open the file given by the user and close it. Set the conditions such that any changes made are not saved.

Example 2 - Step 4

Step 5: Define the ErrorHandler label with the error description and clear any previous errors to avoid confusion.

Example 2 - Step 5


Sub OpenFile()
On Error GoTo ErrorHandler
Dim filePath As String
filePath = InputBox(“Enter the file path:”)
Workbooks.Open filePath
ActiveWorkbook.Close SaveChanges:=False
Exit Sub
MsgBox “An error has occurred: ” & Err.Description
End Sub

Step 6: Run the file opening code. The output is shown below:

Example 2 - Step 6

It opens and closes the Excel file popu.xlsx. You can add additional functions to be applied to it if you need to.

For error handling:

Example 2 - Step 6 - file name
Example 2 - Output

Important Things To Note

  • Always include error handling in your VBA code to manage unexpected errors.
  • Use the “On Error” statement to define how VBA should handle errors when they occur.
  • Include error handling around critical operations like file handling, database connections, or external data interactions.
  • Avoid relying solely on specific error numbers, as they may change across different versions of Excel.
  • Be cautious when using “On Error Resume Next” to avoid potential issues that may go unnoticed.
  • Use “On Error GoTo 0” to disable error handling when not needed, restoring the default behavior.
  • Consider using “On Error Resume Next” for non-critical sections but avoid using it globally.

Frequently Asked Questions (FAQs)

1. What are the different types of error handling in VBA?

On Error Resume Next: Ignores errors and continues with the next line of code. It is mainly done when dealing with VBA Error handling in the loop.
On Error GoTo 0: Disables error handling.
On Error GoTo [Label]: Jumps to a specified label when an error occurs.
On Error GoTo ErrorHandler: Jumps to a common error-handling subroutine (ErrorHandler).
Err.Number: Property containing the error number.
Err.Description: Property containing the error description.
Err.Clear: Clears the current error.

2. What is error handling runtime error 1004 in VBA?

Runtime error 1004 in VBA is known as the “Application-defined or Object-defined error.”
It occurs when there is an issue with an Excel object, such as a worksheet or range.
Common reasons include referencing a non-existent or protected range or using an incorrect method or property with an object.
Proper error handling can help diagnose and resolve the cause of this error.

3. What is the best practice for error handling in VBA?

Always Implement Error Handling: Include error handling in your VBA code to handle unexpected errors.
Use On Error GoTo [Label]: Use “On Error GoTo [Label]” to direct code to a specific error-handling routine.
Provide Meaningful Messages: Display clear and descriptive error messages to aid troubleshooting.
Handle Critical Operations: Use error handling around critical operations to prevent unexpected crashes.
Test Error Scenarios: Test your error handling code with different error scenarios during development.
Avoid “On Error Resume Next”: Use “On Error Resume Next” sparingly, as it can hide errors and complicate debugging.
Clear Errors: Use “Err.Clear” to clear errors after handling to prevent unintended error propagation.
Consider Logging: Consider logging errors to track and analyze issues in the application.

Download Template

This article must help understand VBA Error Handling formulas and examples. We can download the template here to use it instantly.

This has been a guide to What Is VBA Error Handling?. We learn how to handle errors in VBA code with examples and points to remember. You can learn more from the following articles –

Reader Interactions

Leave a Reply

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