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:

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

Explanation

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.

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

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

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

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

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

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

Code:

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
ErrorHandler:
MsgBox “An error has occurred: ” & Err.Description
Err.Clear
End Sub

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

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

Examples

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.

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.

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

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

Step 4: Get the search term from the user.

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)

Print the result after that.

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

Code:

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
ErrorHandler:
Debug.Print “An error has occurred: ” & Err.Description
Err.Clear
End Sub

Step 5: Run the code and view the output:

For error handling:

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.

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

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

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

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

Code:

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
ErrorHandler:
MsgBox “An error has occurred: ” & Err.Description
Err.Clear
End Sub

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

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:

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)

Download Template

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

Recommended Articles

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 –

• VBA Type Mismatch Error
• VBA 1004 Error
• VBA GoTo STATEMENT