VBA error handling

Treat a run-time error as a fact about the world, and leave guilt out of it. A file is missing, a cell holds text where a number was expected, a division hits zero. Leave the error unhandled and VBA halts with a debug dialog, which you can't have in a tool a colleague runs. On Error lets you decide what happens instead.

The structured pattern is On Error GoTo. Jump to a labelled handler, do the work, exit before you reach the handler, and let the handler clean up:

Public Function SafeDivide(a As Double, b As Double) As Variant
    On Error GoTo ErrHandler
    SafeDivide = a / b
    Exit Function                     ' <-- normal path leaves before the handler
ErrHandler:
    SafeDivide = CVErr(xlErrDiv0)     ' return a #DIV/0! to the cell, no crash
End Function

You can't skip the Exit Function (or Exit Sub) before the label. Without it, a successful run falls straight into the error code. Inside the handler, the Err object describes what happened: Err.Number (0 means no error), Err.Description, Err.Source. Calling Err.Clear resets it. Resume Next carries on at the line after the one that failed. Resume retries the failed line, which only makes sense once you've fixed the cause. On Error GoTo 0 turns handling off again, so later bugs surface loudly.

When is On Error Resume Next safe to use?

Then there's On Error Resume Next, a scalpel for one line. It tells VBA to ignore an error and carry on to the next line. It's legitimate for one thing only: an operation you expect might fail, whose failure you check for immediately. Deleting a defined name that may not exist is one example. Probing an object is another:

Dim ws As Worksheet
On Error Resume Next
Set ws = Worksheets("Temp")     ' may not exist
On Error GoTo 0                 ' <-- turn it back OFF immediately
If ws Is Nothing Then Worksheets.Add.Name = "Temp"

The rule is iron. Pair every On Error Resume Next with an On Error GoTo 0 a line or two later, and check the outcome in between. Leaving Resume Next switched on for a whole procedure is the most dangerous line in VBA. It swallows every error that follows, so your macro sails past a real bug and produces silent nonsense. You'll meet it again as common mistake #1.

How do you debug VBA code?

Debugging is how you see inside a running program. The Immediate window (Ctrl+G in the VBE, or View ▸ Immediate) is where Debug.Print sends its output. Sprinkle Debug.Print r, c.Value, total inside a loop and watch the values stream by without disturbing the sheet. You can also type a live query there, and ? dict.Count prints the count on the spot. To step through code, set a breakpoint (click the grey margin, or F9) and run. Execution pauses on that line. Then F8 steps into the next line one at a time, Shift+F8 steps over a called procedure, and hovering over a variable shows its value. Debug ▸ Add Watch pins an expression in the Watch window so you see it update at every step, and the Locals window lists every in-scope variable automatically. A bare Stop statement is a code-level breakpoint. Put it all together and you have the workflow that finds almost any bug. Set a breakpoint before the suspect line, step with F8 and watch the variables. The step where a value goes wrong is your bug.