Wauvel

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

A custom function (UDF) you can use in a cell

Define your own worksheet function. Paste into a Module, then use =GrossMargin(B2, B3) in any cell.

Difficulty

Advanced
Workbook

1What it does

A User-Defined Function (UDF) is your own function, written in VBA, that you can type into a cell like any built-in. This one returns gross margin % and cleanly returns #DIV/0! when revenue is zero. Use UDFs to package logic that's awkward or repetitive as a plain formula.

2The code

'==========================================================
'  Wauvel - GrossMargin: your own worksheet function (UDF)
'  Use it in any cell:  =GrossMargin(revenue, cogs)
'  Pair it with a lime-fill + 0.0% format for the Wauvel look.
'==========================================================
Function GrossMargin(revenue As Double, cogs As Double) As Variant
    If revenue = 0 Then
        GrossMargin = CVErr(xlErrDiv0)   ' clean #DIV/0! instead of an error
    Else
        GrossMargin = (revenue - cogs) / revenue
    End If
End Function

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm) — or download the workbook: a branded Excel file with this code on a sheet, sample data to run it on, and a step-by-step setup guide.

New to macros? Set up in 5 minutes▾
  1. 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. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 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. 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. 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

  • Wrap a gnarly calculation behind a simple function name.
  • Standardize a metric so every sheet computes it the same way.
  • Do things plain formulas can't (string parsing, custom rounding).

4See it in action

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

=GrossMargin(B2, B3)
Result60.0%

5Pitfalls

The function returns #NAME?.

Fix: Put it in a standard Module (not a sheet or ThisWorkbook), and keep the file .xlsm.

It slows the workbook down.

Fix: Avoid volatile patterns; UDFs recalc a lot. Prefer a native formula when one exists.

6No-code alternatives

  • A plain formula — Often enough: =IFERROR((B2-B3)/B2, 0) needs no VBA.
  • LAMBDA — Microsoft 365 lets you define reusable functions without VBA.

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.

Get my free report →