Handling Run-Time Errors
Run-time errors—a missing file, a division by zero, a wrong data type—normally halt a macro with a debug dialog. Structured error handling lets your code intercept these errors gracefully, clean up, and inform the user instead of crashing. VBA's mechanism centers on the On Error statement and the built-in Err object.
Cricket analogy: A wicketkeeper standing back is insurance for a missed edge; error handling is that safety net that catches a run-time failure before it becomes a byes disaster and the innings collapses.
On Error Statements
On Error GoTo label redirects execution to a labeled handler when any run-time error occurs after it. A typical procedure places the On Error GoTo near the top, puts the handler at the bottom after an Exit Sub, and labels it (for example ErrHandler:). On Error GoTo 0 disables the current handler, restoring the default break-on-error behavior.
Cricket analogy: A designated night-watchman comes in when a wicket falls at a bad time; On Error GoTo is that pre-arranged plan to send play to a specific responder the moment failure strikes.
Sub OpenDataFile()
Dim wb As Workbook
On Error GoTo ErrHandler
Set wb = Workbooks.Open("C:\Data\report.xlsx")
MsgBox "Opened " & wb.Name
Exit Sub ' skip the handler on success
ErrHandler:
Select Case Err.Number
Case 1004
MsgBox "File not found or cannot be opened."
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
End SubThe Err Object
When an error fires, the global Err object holds details: Err.Number (0 means no error), Err.Description (a human-readable message), and Err.Source. Inside a handler you inspect Err.Number to react to specific errors—say 53 for 'File not found'. Err.Raise lets you throw your own custom errors, and Err.Clear resets the object.
Cricket analogy: A dismissal comes with a coded reason—lbw, caught, bowled—that tells the scorer exactly what happened; Err.Number is that code letting the handler react to the specific failure.
The Err object is global and persists until it is cleared. Err.Clear, an Exit Sub or Exit Function, a Resume, or another On Error statement all reset Err.Number to 0. Always check or clear Err promptly so a stale error number from earlier does not mislead a later check.
On Error Resume Next and Cleanup
On Error Resume Next tells VBA to ignore an error and continue with the next statement—useful for operations expected to fail sometimes, but dangerous if used broadly because it silently swallows bugs. Inside a handler, Resume retries the failing line, Resume Next continues after it, and a label like Resume CleanUp jumps to shared cleanup code that restores settings such as Application.ScreenUpdating.
Cricket analogy: Playing on and taking the free hit after a no-ball is like Resume Next—you skip past the fault and continue—but ignoring every appeal blindly risks missing a real dismissal, just as blanket Resume Next hides genuine bugs.
Sub SafeDivide()
Dim a As Double, b As Double, result As Double
a = 10: b = 0
On Error Resume Next
result = a / b ' would raise error 11 (division by zero)
If Err.Number <> 0 Then
result = 0
Err.Clear
End If
On Error GoTo 0 ' restore normal error handling
MsgBox "Result: " & result
End SubOn Error Resume Next is the most abused statement in VBA—wrapping a whole procedure in it hides real bugs and makes failures invisible. Scope it to the single risky line and turn it off with On Error GoTo 0 immediately after. Also, always route both the success path (via Exit Sub) and the error path through cleanup code that restores settings like Application.ScreenUpdating = True.
- On Error GoTo label redirects run-time errors to a labeled handler; On Error GoTo 0 restores default behavior.
- Place the handler after an Exit Sub so it only runs on error.
- The Err object exposes Number, Description, and Source; branch on Err.Number for specific errors.
- On Error Resume Next ignores errors and continues—scope it narrowly and disable it right after.
- Resume retries the failing line, Resume Next continues after it, and Resume label jumps to cleanup.
- Always run cleanup code that restores settings on both the success and error paths.
Practice what you learned
1. What does On Error GoTo 0 do?
2. Which Err property gives the numeric error code?
3. What is the danger of using On Error Resume Next over an entire procedure?
4. Inside a handler, what does the bare Resume statement do?
5. Where should an error handler typically be placed in a procedure?
Was this page helpful?
You May Also Like
Conditionals in VBA
Learn how VBA evaluates Boolean expressions to branch code using If...Then...Else, ElseIf chains, and Select Case.
Subs and Functions in VBA
Understand VBA's two procedure types—action-performing Subs and value-returning Functions—plus arguments, scope, and UDFs.
Loops in VBA
Master repetition in VBA with counter-driven For...Next, collection-driven For Each, and condition-driven Do loops.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics