Named Range Documenter: Map Every Named Range Before You Delete Anything

TL;DR: You inherit a workbook with 30 named ranges. Before running Delete Broken Named Ranges, you need to know what’s actually there — which ones are live, which ones are #REF!, which ones feed formulas, and which are hidden from Name Manager. This macro builds a complete inventory report in one click.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with named ranges — any inherited, multi-year, or multi-preparer file

What the macro does NOT do:

• It does not modify any cells on any existing sheet. It creates one new “Named-Ranges” sheet and writes only there.
• It does not delete, rename, or change any named ranges. It’s read-only.
• It does not require any input. Run it on any workbook and it produces a report.

Limitations:

• The “Value” column only resolves for single-cell named ranges. Multi-cell ranges show “(range)” and named formulas show “(formula)”.
• Hidden names are identified but the macro does not explain why they’re hidden. Excel hides names for internal purposes (print areas, filter criteria, solvers) — some are yours, some are system-generated.
• If a name evaluates to an error, the “Value” column shows the error type (#REF!, #N/A, etc.) so you know exactly what’s broken.

#The Macro

Option Explicit

Sub DocumentNamedRanges()
    ' ── Named Range Documenter ─────────────────────────
    ' Lists every named range — workbook-level and
    ' sheet-level — on a new "Named-Ranges" sheet.
    ' Columns: Name, Refers To, Scope, Hidden, Value.
    ' Companion to Delete Broken Named Ranges: run
    ' this first to understand what you have.
    '
    ' Zero input. Zero configuration. Run on any workbook.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Named-Ranges"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim nm As Name
    Dim wsOut As Worksheet
    Dim r As Long, total As Long
    Dim broken As Long, hidden As Long
    Dim scopeLabel As String
    Dim val As Variant
    Dim valDisplay As String

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Create output sheet ────────────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set wsOut = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets( _
            ThisWorkbook.Worksheets.Count))
    wsOut.Name = OUT_SHEET

    ' ── Headers ────────────────────────────────────────
    wsOut.Range("A1:E1").Value = Array( _
        "Name", "Refers To", "Scope", "Hidden", "Value")
    wsOut.Range("A1:E1").Font.Bold = True
    wsOut.Range("A1:E1").Interior.Color = RGB(50, 50, 50)
    wsOut.Range("A1:E1").Font.Color = vbWhite

    ' ── Scan every named range ─────────────────────────
    r = 2
    total = 0
    broken = 0
    hidden = 0

    For Each nm In ThisWorkbook.Names
        total = total + 1

        ' ── Scope ──────────────────────────────────────
        If TypeName(nm.Parent) = "Workbook" Then
            scopeLabel = "Workbook"
        Else
            scopeLabel = nm.Parent.Name
        End If

        ' ── Evaluate value ─────────────────────────────
        On Error Resume Next
        val = Evaluate(nm.Name)
        On Error GoTo CleanUp

        If IsEmpty(val) Then
            valDisplay = "(empty)"
        ElseIf IsError(val) Then
            valDisplay = CVErrToString(val)
            broken = broken + 1
        ElseIf IsArray(val) Then
            valDisplay = "(range)"
        ElseIf IsNumeric(val) Then
            valDisplay = Format(val, "#,##0.00")
        Else
            valDisplay = CStr(val)
        End If

        ' ── Write row ──────────────────────────────────
        wsOut.Cells(r, 1).Value = nm.Name
        wsOut.Cells(r, 2).Value = "'" & nm.RefersTo
        wsOut.Cells(r, 3).Value = scopeLabel
        wsOut.Cells(r, 4).Value = IIf(nm.Visible, "No", "Yes")

        If Not nm.Visible Then hidden = hidden + 1

        ' Value column — color-code errors
        wsOut.Cells(r, 5).Value = valDisplay
        If IsError(val) Then
            wsOut.Cells(r, 5).Font.Color = RGB(200, 50, 50)
            wsOut.Cells(r, 5).Font.Bold = True
        End If

        ' Alternate row shading
        If total Mod 2 = 0 Then
            wsOut.Range("A" & r & ":E" & r). _
                Interior.Color = RGB(248, 248, 250)
        End If

        ' Flag hidden names
        If Not nm.Visible Then
            wsOut.Cells(r, 4).Interior.Color = RGB(254, 240, 138)
        End If

        r = r + 1
    Next nm

    ' ── Summary row ────────────────────────────────────
    wsOut.Cells(r, 1).Value = total & " name(s) total"
    wsOut.Cells(r, 1).Font.Bold = True
    wsOut.Cells(r, 1).Font.Italic = True
    wsOut.Range("A" & r & ":E" & r). _
        Borders(xlEdgeTop).LineStyle = xlContinuous

    If broken > 0 Then
        r = r + 1
        wsOut.Cells(r, 1).Value = broken & " broken (#REF! or error)"
        wsOut.Cells(r, 1).Font.Color = RGB(200, 50, 50)
        wsOut.Cells(r, 1).Font.Bold = True
    End If

    If hidden > 0 Then
        r = r + 1
        wsOut.Cells(r, 1).Value = hidden & " hidden from Name Manager"
        wsOut.Cells(r, 1).Font.Bold = True
    End If

    ' ── Format output ──────────────────────────────────
    With wsOut
        .Columns("A").ColumnWidth = 30
        .Columns("B").ColumnWidth = 55
        .Columns("C").ColumnWidth = 22
        .Columns("D").ColumnWidth = 10
        .Columns("E").ColumnWidth = 18
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    ' ── Report ─────────────────────────────────────────
    Dim msg As String
    msg = total & " named range(s) documented."

    If broken > 0 Then
        msg = msg & vbCrLf & broken & " are broken (#REF! or error)."
    End If
    If hidden > 0 Then
        msg = msg & vbCrLf & hidden & " are hidden from Name Manager."
    End If

    msg = msg & vbCrLf & vbCrLf & _
          "See the '" & OUT_SHEET & "' sheet."

    MsgBox msg, vbInformation, "Done"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

' ── Helper: Convert CVErr to readable string ──────────
Private Function CVErrToString(ByVal v As Variant) As String
    Select Case CVErr(v)   ' CVErr wraps the error; inner CVErr unwraps
        Case 2000: CVErrToString = "#NULL!"
        Case 2007: CVErrToString = "#DIV/0!"
        Case 2015: CVErrToString = "#VALUE!"
        Case 2023: CVErrToString = "#REF!"
        Case 2029: CVErrToString = "#NAME?"
        Case 2036: CVErrToString = "#NUM!"
        Case 2042: CVErrToString = "#N/A"
        Case Else: CVErrToString = "#ERROR!"
    End Select
End Function

#How It Works

#The difference between scope types

If TypeName(nm.Parent) = "Workbook" Then
    scopeLabel = "Workbook"
Else
    scopeLabel = nm.Parent.Name
End If

Workbook-level names are available from any sheet. Sheet-level names — like 'Sched-A'!Print_Area — only resolve when the containing sheet is active. The Scope column tells you which names live on which sheets. If a sheet-level name shows Sched-A 2024 in the scope column and that sheet was deleted, that’s your broken name.

This distinction matters during cleanup. A broken workbook-level name named “PriorYearRevenue” is dead weight — delete it. A broken sheet-level name scoped to a sheet you plan to restore might be worth keeping.

#Evaluating names without crashing on #REF!

On Error Resume Next
val = Evaluate(nm.Name)
On Error GoTo CleanUp

Evaluate(nm.Name) asks Excel to resolve the name and return its current value. For a single-cell named range like “FA_Cost” pointing to $C$2:$C$8, this returns an array. For a named formula like =SUM(TB_Balances)-SUM(TB_Adjustments), it returns the computed number. For a broken name pointing to a deleted sheet, it returns a CVErr — a VBA error value.

The On Error Resume Next wrapper is necessary because some broken references throw errors that bypass On Error GoTo CleanUp. By swallowing the error locally, we can check the result with IsError and move on without crashing the entire scan.

#What the Value column actually tells you

The value column handles four distinct cases:

• Empty: The name exists but points to nothing. Rare but possible after workbook corruption.
• Error: The name resolves to #REF!, #N/A, #DIV/0!, or another formula error. These are your broken names.
• “(range)”: The name points to a multi-cell range. The value is an array, not a single number — showing one number would be misleading.
• A number or string: The name resolves cleanly. You can see its live value at a glance.

The CVErrToString helper converts cryptic VBA error codes (2000, 2007, 2015…) into the familiar hash-tag errors (#NULL!, #DIV/0!, #VALUE!) you see in Excel cells. This is purely cosmetic — but it makes the report readable without a VBA reference manual.

Select Case CVErr(v)
    Case 2000: CVErrToString = "#NULL!"
    Case 2007: CVErrToString = "#DIV/0!"
    ' ... etc ...
End Select

#Hidden names get a yellow flag

Hidden names (those not visible in Name Manager) get a yellow highlight in the Hidden column. In practice, you’ll see two types of hidden names:

1. System names — like _xlnm.Print_Area and _xlnm._FilterDatabase. These are automatically created by Excel when you set print areas or apply filters. They’re not yours to worry about.

2. User-hidden names — set up by a prior preparer and deliberately hidden, usually for internal references that shouldn’t clutter Name Manager. They might be critical (a named formula driving a key calculation) or abandoned (a leftover from a macro they deleted).

The documenter flags them in yellow so you can triage. System names are white noise. User-hidden names deserve a closer look.

#Why the report sheet matters

Name Manager (Ctrl+F3) shows 20 names at a time in a non-resizable dialog with three visible columns and no text wrapping. You can’t sort by scope, filter for errors, or copy the list to share with a reviewer.

The report sheet solves all of those problems:

• Sort: Filter by the Hidden column to see only hidden names, or sort by Scope to group sheet-level names together.
• Print: Print the report and include it in your review binder as a workpaper artifact — a digital inventory of the named range landscape.
• Compare: Run the macro on last year’s file and this year’s file and put the two reports side by side. Missing names or new names jump out.

#Pairing with Delete Broken Named Ranges

This macro is the reconnaissance phase. Run it first. Review the report. Identify which names are broken, which are system-generated, and which are critical.

Then run Delete Broken Named Ranges — but only after you’ve confirmed that the 12 broken names in the report are safe to delete. The documenter arms you with the information. The deleter executes on your decision.

#Adapt It