Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Handle errors instead of dying halfway through

One label at the bottom that puts Excel back the way you found it, whether the macro finished or fell over.

1What it does

Every other macro on this site assumes nothing goes wrong. In practice one renamed tab or one locked file stops a macro mid-run — and if it had switched off screen updating and set calculation to manual, it leaves them that way. Excel looks frozen, formulas stop recalculating, and the user assumes they broke something. `On Error GoTo` sends any failure to a label at the bottom, that label puts the settings back, and the same code runs whether the macro succeeded or not. It's the difference between a macro you can hand to someone else and one only its author dares run.

2The code

Sub UpdateReport()
    ' Remember what we're about to change, so we can put it back.
    Dim prevCalc As XlCalculation
    prevCalc = Application.Calculation

    ' From here on, ANY error jumps to the Cleanup label below.
    On Error GoTo Cleanup

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Report")   ' error 9 if it was renamed
    ws.Range("A1").Value = "Updated " & Format(Now, "yyyy-mm-dd hh:mm")

    ' ... the real work goes here ...

Cleanup:
    ' Reached on success AND on failure, so the settings always go back.
    Application.ScreenUpdating = True
    Application.Calculation = prevCalc

    If Err.Number <> 0 Then
        MsgBox "Couldn't finish: " & Err.Description & _
               " (error " & Err.Number & ")", vbExclamation
        Err.Clear
    End If
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾

1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q.
3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

Any macro that turns off screen updating or sets calculation to manual.
Anything touching a file, a sheet or a connection that might not be there.
Every macro you hand to someone who can't read the code.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

The “Report” tab was renamed, so line 12 raises error 9 halfway through.

Screen updating	On
Calculation	Automatic

5Pitfalls

`On Error Resume Next` used as a handler. It doesn't handle errors — it ignores them, and it stays on for the rest of the procedure, so every later failure is silent too.

Fix: Use it around the ONE line that's allowed to fail, then `On Error GoTo 0` on the very next line to switch it back off.

The screen stays frozen after a failure, because the error jumped past the line that restores ScreenUpdating.

Fix: Put every restore under the Cleanup label, which the error path actually reaches.

`Err` is empty by the time you read it. Anything that resets it — another `On Error`, `Err.Clear`, leaving the procedure — wipes it first.

Fix: Copy `Err.Number` and `Err.Description` into variables on the first line after the label, before doing anything else.

The Cleanup label runs its error message even on success.

Fix: It only reports when `Err.Number <> 0`. If you'd rather it not run at all on success, put `Exit Sub` immediately above the label.

6No-code alternatives

No handler at all — Better WHILE you're writing it: the debugger stops on the offending line and shows you the state. Add the handler when the macro leaves your desk.
On Error Resume Next — Right for a single line where failure is genuinely fine — deleting a sheet that may not exist. Wrong as a blanket policy.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

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.