Excel like a finance pro.
VBA · Errors & debugging
The Err object
Holds the number and description of the last error.
When to use it
The Err object holds the number, description, and source of the most recent runtime error. Read it inside handlers and after Resume Next blocks.
The code
- Code
MsgBox Err.Number & ": " & Err.Description
Worked examples
Report an error
MsgBox Err.Number & ": " & Err.Description → Shows what went wrong
Inside a handler.
Check and clear
On Error Resume Next x = 1 / 0 If Err.Number <> 0 Then MsgBox "division failed": Err.Clear → Checks after a risky line
Clear it after handling.
Raise your own
Err.Raise 1001, , "Rate must be positive" → Throws a custom error
Validation in functions.
Worth knowing
- Common numbers: 9 subscript out of range (bad sheet name), 91 object not set, 1004 application error, 13 type mismatch.
- Err.Clear after Resume Next handling, or the next check sees the old error.
- Custom errors use numbers above 512 plus vbObjectError.
Where it goes wrong
- Err.Number is 0 after a handler runs Resume, which confuses logging.
- Descriptions are generic for 1004.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.