Error Handling in VBA
Learn how to identify runtime errors, handle unexpected situations, use On Error statements, and create reliable Excel automation macros.
1. Introduction to Error Handling
An error occurs when VBA cannot execute an operation as expected. For example, a macro may try to access a worksheet that does not exist, divide a number by zero, or convert invalid text into a number.
Error handling allows a program to respond to certain errors, display useful messages, clean up resources, and exit gracefully instead of stopping unexpectedly.
Without Error Handling
An unhandled runtime error interrupts execution and may leave the user unsure what went wrong.
With Error Handling
The macro can report the problem, restore settings, and stop or continue according to the chosen strategy.
2. Types of Errors in VBA
| Error Type | Description | Example |
|---|---|---|
| Syntax Error | Code violates VBA syntax rules. | Missing End If. |
| Compile Error | VBA cannot compile the procedure. | Calling an undeclared procedure incorrectly. |
| Runtime Error | An error occurs while the code is executing. | Accessing an invalid object or worksheet. |
| Logical Error | The code executes but produces an incorrect result. | Calculating a discount using the wrong percentage. |
On Error handles runtime errors. It does not fix
syntax, compilation, or logical errors automatically.
3. How Error Handling Works
A procedure executes its normal instructions until an error occurs. If an appropriate handler is active, VBA transfers control to that handler.
Conceptual flow: the exact execution path depends on the active handler and the code's recovery logic.
4. Understanding On Error Statements
VBA provides three important forms of error handling.
A. On Error GoTo Label
Transfers control to a named error-handling section when a runtime error occurs.
Sub GoToErrorExample()
On Error GoTo ErrorHandler
Dim result As Double
result = 10 / 0
MsgBox result
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
End Sub
Explanation: The error occurs during division.
VBA transfers control to ErrorHandler. The
Err object provides information about the error.
B. On Error Resume Next
Tells VBA to continue with the next statement after a runtime error. It is useful only when the failure is expected and the result is checked immediately.
Sub ResumeNextExample()
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Sales")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "The Sales worksheet does not exist.", vbExclamation
Else
MsgBox "The Sales worksheet was found.", vbInformation
End If
End Sub
Explanation: If the worksheet cannot be found,
the assignment fails and ws remains Nothing.
The code then checks the object reference.
On Error Resume Next active across
large sections of code. It can hide errors that should be fixed.
C. On Error GoTo 0
Disables the current procedure's enabled error handler and
restores normal runtime error behavior. It is commonly used
after a small, intentional Resume Next section.
Sub DisableErrorHandlerExample()
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Report")
On Error GoTo 0
If ws Is Nothing Then
MsgBox "Report sheet not found."
Exit Sub
End If
ws.Range("A1").Value = "Ready"
End Sub
5. Understanding the Err Object
The built-in Err object contains information about
the most recent runtime error.
| Property / Method | Purpose |
|---|---|
Err.Number |
Returns the error number. |
Err.Description |
Returns the error description. |
Err.Source |
Identifies the source of the error when available. |
Err.Clear |
Clears the stored error information. |
Sub DisplayErrorDetails()
On Error GoTo ErrorHandler
Dim result As Long
result = CLng("ABC")
Exit Sub
ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description, vbExclamation
Err.Clear
End Sub
The conversion of "ABC" to a number causes a
runtime conversion error. The handler displays the error details.
6. Practical Example: Safe Division
Consider a macro that divides total sales by the number of employees. A zero employee count must be handled before division.
Sub CalculateAverageSales()
Dim totalSales As Double
Dim employeeCount As Long
Dim averageSales As Double
totalSales = 50000
employeeCount = 0
If employeeCount <= 0 Then
MsgBox "Employee count must be greater than zero.", vbExclamation
Exit Sub
End If
averageSales = totalSales / employeeCount
MsgBox "Average Sales: " & Format$(averageSales, "0.00"), vbInformation
End Sub
Step-by-Step Explanation
- Declare variables for total sales and employee count.
- Assign the values used in the calculation.
- Check whether the employee count is valid.
- Exit the procedure if the count is zero or negative.
- Calculate and display the average only when valid.
7. Error Handling with Cleanup
Some macros temporarily change Excel settings, open files, or allocate resources. A cleanup section helps restore settings even if an error occurs.
Sub SafeMacroWithCleanup()
Dim previousScreenUpdating As Boolean
Dim ws As Worksheet
previousScreenUpdating = Application.ScreenUpdating
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("A1").Value = "Process completed"
CleanExit:
Application.ScreenUpdating = previousScreenUpdating
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
Resume CleanExit
End Sub
How it works:
- The original screen-updating setting is saved.
- The macro performs its operation with screen updating disabled.
- If an error occurs, the handler displays the error message.
Resume CleanExittransfers execution to the cleanup label.- The original screen-updating setting is restored.
8. Raise a Custom Error
You can use Err.Raise to report a business-rule
violation through VBA's normal error-handling mechanism.
Sub ValidateEmployeeSalary()
Dim salary As Double
On Error GoTo ErrorHandler
salary = -5000
If salary < 0 Then
Err.Raise vbObjectError + 1001, _
"ValidateEmployeeSalary", _
"Salary cannot be negative."
End If
MsgBox "Salary is valid."
Exit Sub
ErrorHandler:
MsgBox "Validation failed: " & Err.Description, vbExclamation
End Sub
Explanation:
vbObjectError + 1001 creates a custom error number
in the user-defined error range. The handler displays the
validation message.
9. Common Error-Handling Mistakes
| Mistake | Why It Is a Problem | Better Approach |
|---|---|---|
| Ignoring every error | Real failures may go unnoticed. | Handle only expected errors and check outcomes. |
Missing Exit Sub |
Normal execution may fall through into the handler label. | Exit the procedure before the handler section. |
| Not restoring Excel settings | Excel may remain in an unintended state. | Use a cleanup label and restore saved settings. |
| Showing vague messages | The user cannot understand what failed. | Provide meaningful descriptions and relevant error details. |
| Using error handling for every validation | Predictable input problems become harder to understand. | Validate inputs before performing operations. |
10. Practice Exercises
Exercise 1: Worksheet Validation
Write a macro that checks whether a worksheet named
Employees exists and displays an appropriate message.
Exercise 2: Number Conversion
Accept a value from a user, validate it, convert it to a number, and handle conversion errors.
Exercise 3: File Handling
Create a macro that attempts to open a workbook and reports a useful message if the file cannot be opened.
Exercise 4: Custom Error
Raise a custom error when an employee's bonus is negative and handle it using an error-handling section.
Important Points to Remember
On Error GoTotransfers control to a handler.On Error Resume Nextcontinues after runtime errors.On Error GoTo 0disables the enabled handler.Err.NumberandErr.Descriptiondescribe an error.- Use validation to prevent predictable errors.
- Use cleanup sections to restore temporary application settings.
- Do not silently suppress errors that need attention.
Summary
Error handling helps VBA programs respond to unexpected
runtime problems. The main techniques include
On Error GoTo,
On Error Resume Next,
On Error GoTo 0, and the built-in
Err object.
Reliable macros combine input validation, meaningful error messages, controlled recovery, and cleanup logic to keep Excel automation predictable and maintainable.