VBA Err Object in Excel — Number, Description & Raising Your Own Errors

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-08

TL;DR — When a runtime error happens, VBA fills one global object — Err — with everything it knows about the crash. You read it inside a handler to decide what to do; you can also write to it with Err.Raise to throw your own error:

Sub ReadPrice()
    On Error Resume Next
    Dim price As Double
    price = CDbl(Range("A1").Value)          ' fails if A1 isn't numeric
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Err.Clear                            ' reset so the next check is clean
    End If
    On Error GoTo 0
End Sub

The whole object in one place:

Err.Number        ' the error code — 0 means "no error"
Err.Description   ' the human-readable message
Err.Source        ' who raised it (project / object name)
Err.Raise 513     ' THROW an error yourself
Err.Clear         ' wipe Err back to a clean state

The mental model: Err is the black box of the last crash

Err is a single, always-present, global object. Think of it as the flight recorder of your macro: the instant a runtime error occurs, VBA writes three facts into it before control jumps to your handler —

Err.Number — the numeric code (1004, 13, 9…). 0 means "nothing has gone wrong."
Err.Description — the same text you'd see in the red dialog ("Type mismatch", "Subscript out of range").
Err.Source — which project or object raised it.

There's only one Err object for the whole session, and it holds the most recent error. That's why when you read it matters as much as what you read — and why it gets wiped automatically (more on that below).

The one rule: `Err.Number` is the only reliable "did it actually fail?"

After On Error Resume Next, your code keeps running whether or not the line worked. The only way to know which happened is to read Err.Number:

On Error Resume Next
Set wb = Workbooks("Budget.xlsx")     ' is this book already open?
If Err.Number = 0 Then
    MsgBox "Already open."
Else
    Err.Clear
    Set wb = Workbooks.Open("C:\Data\Budget.xlsx")
End If
On Error GoTo 0

Resume Next without an Err.Number check is wasted: you've told VBA to ignore failures, but you never find out one happened. The check is the handling. This is the companion rule to On Error Resume Next — the two only work as a pair.

You can also branch on the specific code, because different numbers mean different problems:

Select Case Err.Number
    Case 0:    ' no error
    Case 9:    MsgBox "That sheet or index doesn't exist."   ' Subscript out of range
    Case 13:   MsgBox "That value isn't the right type."     ' Type mismatch
    Case 1004: MsgBox "Excel refused that operation."        ' generic automation error
    Case Else: MsgBox "Unexpected error " & Err.Number & ": " & Err.Description
End Select

The trap: Err clears itself when you least expect it

Err does not stay populated until you're done with it. VBA resets it to Number = 0 automatically on any of these:

an On Error statement of any kind (including On Error GoTo 0)
a Resume or Resume Next
Exit Sub / Exit Function
the next runtime error (it's overwritten, not appended)

So if you need the number or description after any of those — to log it, to show it later, to pass it up — capture it into your own variables the moment your handler starts:

ErrHandler:
    Dim errNum As Long, errMsg As String
    errNum = Err.Number                     ' grab it NOW
    errMsg = Err.Description
    On Error Resume Next                     ' ← this line would wipe Err...
    Application.ScreenUpdating = True        ' ...so cleanup can't read Err anymore
    LogToSheet errNum, errMsg                ' use the saved copies, not Err.*

This single gotcha is behind most "my error log shows error 0" bug reports.

Raising your own errors with `Err.Raise`

Reading Err is half the object. The other half is writing to it — deliberately throwing an error when your rules are violated, not just when VBA's are:

Function NetPrice(gross As Double, taxRate As Double) As Double
    If taxRate < 0 Or taxRate > 1 Then
        Err.Raise vbObjectError + 513, "NetPrice", _
                  "taxRate must be between 0 and 1, got " & taxRate
    End If
    NetPrice = gross / (1 + taxRate)
End Function

Err.Raise Number, Source, Description generates a real runtime error — the caller's On Error handler catches it exactly like a built-in one. Use vbObjectError + n (with n from 513 to 65535) for custom errors; that constant offsets you clear of the 0–512 range VBA reserves for system codes, so you never collide with a real one.

The opinion: returning an error code is hope; raising one is enforcement

Plenty of VBA "validates" by returning a sentinel — -1, False, an empty string — and trusting every caller to remember to check it. They won't. Six months later someone calls your function, ignores the -1, and divides by it.

Err.Raise removes the choice. A raised error cannot be silently ignored — it either hits a handler or stops the macro. That's the whole point. Reading Err.Number makes you competent at error handling; raising your own errors for bad inputs and business rules is what makes your code defensive instead of merely polite. If a value would corrupt the result downstream, don't return a flag and hope — raise.

Common Err mistakes (and the fix)

Symptom	Cause	Fix
Log always shows "error 0"	Read `Err` after `Resume`/`On Error`/cleanup wiped it	Save `Err.Number`/`Err.Description` into variables first
`Resume Next` "doesn't catch" anything	Never checked `Err.Number` after the risky line	Add `If Err.Number <> 0 Then …`
Custom error collides with a system one	Used a raw small number like `Err.Raise 5`	Use `Err.Raise vbObjectError + 513` and up
Second check fires on a stale error	Forgot `Err.Clear` after handling the first	Call `Err.Clear` once you've dealt with it
`Err.Description` is blank	Read it after `Err.Clear` or a reset	Read description before clearing

Stop hand-rolling error plumbing — describe the rule instead

Half of robust VBA is this: read Err.Number, save the description, raise your own errors for bad data, clear, repeat. It's necessary and it's tedious. ExcelMaster Agent lets you say "validate the tax rate is between 0 and 1 and stop with a clear message if not" in plain English — and the generated Python raises and reports the error for you, with the file backed up first. Try it free →

FAQ

What is the Err object in VBA? Err is a global object that holds information about the most recent runtime error. Its main members are Err.Number (the error code, 0 = no error), Err.Description (the message), and Err.Source (what raised it). You read it inside an error handler to decide what to do.

How do I check the error number in VBA? Read Err.Number. After On Error Resume Next, test If Err.Number <> 0 Then to see whether the previous line failed. A value of 0 means no error occurred. You can Select Case Err.Number to react differently to specific codes like 9 (subscript out of range) or 13 (type mismatch).

When does the Err object reset? Automatically on any On Error statement, on Resume/Resume Next, on Exit Sub/Exit Function, and when the next error overwrites it. If you need the values afterwards, copy Err.Number and Err.Description into your own variables immediately. You can also reset it manually with Err.Clear.

How do I raise a custom error in VBA? Use Err.Raise Number, Source, Description. For custom errors, use vbObjectError + n where n is between 513 and 65535, e.g. Err.Raise vbObjectError + 513, "MyFunc", "Invalid input". This avoids the 0–512 range reserved for system errors and lets the caller's handler catch it like any built-in error.

What is the difference between Err.Clear and On Error GoTo 0? Err.Clear resets the Err object's properties to a clean state but leaves your error trapping mode unchanged. On Error GoTo 0 resets the trapping mode back to the default (stop on error) — and as a side effect also clears Err. Use Err.Clear to reuse Err within the same handler; use On Error GoTo 0 to stop ignoring errors.