Wauvel

Excel like a finance pro.

← All VBA snippets

Read and set named ranges from code

Set a model's assumptions by name, and a macro becomes a scenario runner.

1What it does

A well-built model names its assumption cells — GrowthRate, GrossMargin, Headcount — so formulas read `=Revenue*GrossMargin` instead of `=B4*$C$2`. Naming them also makes them addressable from code by name, independent of where they sit. That turns a macro into a scenario runner: set three named inputs, recalculate, read the named output, record it, next scenario. Because it never refers to a cell address, moving the assumption block to another sheet doesn't break a thing.

2The code

Sub RunScenarios()
    Dim scenarios As Variant, i As Long, out As Long
    ' name, growth, margin
    scenarios = Array( _
        Array("Downside", 0.02, 0.34), _
        Array("Base", 0.06, 0.38), _
        Array("Upside", 0.11, 0.41))

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim keepGrowth As Variant, keepMargin As Variant

    ' Remember the live assumptions so the model is left as we found it.
    keepGrowth = wb.Names("GrowthRate").RefersToRange.Value
    keepMargin = wb.Names("GrossMargin").RefersToRange.Value

    out = 2
    With wb.Worksheets("Scenarios")
        .Range("A1:C1").Value = Array("Scenario", "Net income", "Cash at year end")
        For i = LBound(scenarios) To UBound(scenarios)
            wb.Names("GrowthRate").RefersToRange.Value = scenarios(i)(1)
            wb.Names("GrossMargin").RefersToRange.Value = scenarios(i)(2)
            Application.Calculate

            .Cells(out, 1).Value = scenarios(i)(0)
            .Cells(out, 2).Value = wb.Names("NetIncome").RefersToRange.Value
            .Cells(out, 3).Value = wb.Names("YearEndCash").RefersToRange.Value
            out = out + 1
        Next i
    End With

    ' Put the model back.
    wb.Names("GrowthRate").RefersToRange.Value = keepGrowth
    wb.Names("GrossMargin").RefersToRange.Value = keepMargin
    Application.Calculate
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. 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 → Module and 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

  • Run base, upside and downside and record the outputs side by side.
  • Set the model's inputs from a settings sheet without knowing cell addresses.
  • Sweep one assumption across a range of values for a sensitivity table.

4See it in action

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

Named inputs (live model)
GrowthRate6.0%
GrossMargin38.0%
Scenarios sheet
(empty)

The macro sets GrowthRate and GrossMargin by NAME, recalculates, and reads NetIncome — no cell addresses anywhere.

5Pitfalls

The macro leaves the model on the last scenario, and the next person reads upside numbers as the plan.

Fix: Save the live values first and restore them at the end, as above — ideally under an error-handling Cleanup label.

A name refers to a formula or constant rather than a cell, and RefersToRange errors.

Fix: Only names pointing at ranges have a RefersToRange. Check the name in the Name Manager (Ctrl + F3).

Calculation is manual, so the outputs read stale values.

Fix: Application.Calculate after setting inputs and before reading outputs.

A sheet-scoped name isn't found at workbook level.

Fix: Scoped names live on the sheet: ws.Names("GrowthRate"). The Name Manager shows each name's scope.

6No-code alternatives

  • A data table — Sensitivity across one or two inputs with no code at all — see the tip. The macro is for scenarios that change several inputs at once.
  • Scenario Manager — Stores named input sets without code and builds a summary, up to 32 cells.

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.