🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Named Range in Excel — Names.Add, RefersTo, and Why Your Reference Drifts

|

VBA Named Range in Excel — Names.Add, RefersTo, and Why Your Reference Drifts

TL;DR — A named range is a stored formula, not a label. Range("B2:B50").Name = "SalesData" is the one-line way to create one; Names.Add Name:="TaxRate", RefersTo:="=Sheet1!$B$1" is the long form. The two things that bite everyone: RefersTo needs a leading = and absolute $ signs (leave the $ off and the name silently drifts to a different cell every time you use it), and a name has a scope — workbook-wide or one sheet. Read a name with Range("TaxRate").Value or the [TaxRate] shorthand.

' One-line: workbook-scoped, absolute — the common case
Range("B2:B50").Name = "SalesData"

' Long form, with an explicit scope and RefersTo formula
ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"

Debug.Print Range("TaxRate").Value          ' read the value the name points at
Range("SalesData").Interior.Color = vbYellow ' use the name like any Range

Everyone reaches for named ranges to stop scattering Range("$B$2") through a macro, and then hits a string of small mysteries: the name refers to the wrong cell, or to literal text, or the same name means two different things on two sheets, or a name survives a deleted column and poisons every formula that used it. All of them come from one idea worth holding onto: a name is a stored RefersTo formula that Excel re-evaluates on every use — so the rules that govern formulas (the leading =, the $ absolutes, the sheet qualifier) are exactly the rules that govern names.

What you'll learn

  • The mental model — a name is a stored formula, not a nickname
  • Creating a name — the one-line Range.Name versus Names.Add with RefersTo
  • Why $ decides between a stable name and one that drifts with the active cell
  • Scope — workbook-wide versus one sheet, and the collision it causes
  • Reading and using a name, and constant names that have no cells
  • Cleaning up #REF! names and the hidden-name bloat they leave behind

The mental model: a name is a stored formula

When you create a named range, Excel does not tag the cell. It stores a RefersTo formula and resolves it every time the name appears. SalesData is not "cell B2:B50" frozen in place — it is the formula =Sheet1!$B$2:$B$50, evaluated on demand. Read Range("SalesData") and Excel runs that formula and hands you whatever it resolves to now.

That is why everything below is really about writing the RefersTo formula correctly:

ThisWorkbook.Names.Add Name:="SalesData", RefersTo:="=Sheet1!$B$2:$B$50"
Debug.Print ThisWorkbook.Names("SalesData").RefersTo   ' =Sheet1!$B$2:$B$50

RefersTo is a string that must start with =, exactly like typing into the Name Manager. Leave the = off — RefersTo:="Sheet1!$B$2" — and you do not get an error; you get a name that refers to the literal text "Sheet1!$B$2", which is almost never what you meant. Hold "it is a formula" and the = stops being a mystery.

Creating a name: the one-liner and the long form

For the common case — an absolute, workbook-scoped name — assign .Name to a range and you are done:

Range("B2:B50").Name = "SalesData"      ' workbook scope, absolute, one line

Reach for Names.Add when you need to set the scope explicitly, refer to a name by formula, or store a constant rather than cells:

ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1"   ' workbook scope
Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9" ' sheet scope
ThisWorkbook.Names.Add Name:="VAT", RefersTo:="=0.2"              ' a constant, no cells

Both are fine; the difference is control. Range.Name = is the fastest correct thing for a plain range; Names.Add is what you use the moment scope or a non-range target matters.

Why $ decides between a stable name and a drifting one

This is the named-range bug people cannot explain: "my name points at a different cell every time I run the macro." The cause is a relative name — a RefersTo without $ signs.

' DRIFTS — relative reference, resolved against the ACTIVE cell
ThisWorkbook.Names.Add Name:="Prev", RefersTo:="=Sheet1!A1"

' STABLE — absolute reference, always the same cell
ThisWorkbook.Names.Add Name:="Anchor", RefersTo:="=Sheet1!$A$1"

A relative name is stored relative to wherever the active cell was when it was created, and Excel re-anchors it to the active cell on every use — so Prev might resolve to A1, then D5, then Z99, depending on the selection. Relative names are a real, occasionally useful feature (a name meaning "the cell one to the left"), but if you did not do it on purpose, it reads as a haunting. Write $ on both the column and the row unless you specifically want the name to move. When you use Range.Name =, you get absolute automatically — one more reason it is the safer default.

Scope: workbook-wide versus one sheet

Every name lives in a scope. ThisWorkbook.Names.Add (and Range.Name =) creates a workbook-scoped name, visible from anywhere. Worksheets("Jan").Names.Add creates a worksheet-scoped name, visible only on that sheet — which lets Jan and Feb each have their own Region name pointing at their own data.

The trap is mixing them up:

Worksheets("Jan").Names.Add Name:="Region", RefersTo:="=Jan!$A$1:$A$9"
' From a standard module, this does NOT see the sheet-local name reliably:
' Debug.Print Range("Region").Address   ' may error or hit a different Region
Debug.Print Worksheets("Jan").Range("Region").Address   ' qualify it -> works

A sheet-scoped name must be reached through its sheet — Worksheets("Jan").Range("Region") — not with a bare Range("Region") from a module. Decide deliberately: a single constant for the whole file (TaxRate) is workbook scope; a region that repeats per sheet (Region on each monthly tab) is sheet scope, and you qualify it every time. See VBA Worksheet for addressing sheets.

Reading, using, and the constant-name gotcha

A range name behaves like any Range. Read it, write it, format it:

Range("TaxRate").Value = 0.19            ' write into the named cell
Debug.Print Range("SalesData").Cells.Count
Set rng = ThisWorkbook.Names("SalesData").RefersToRange  ' the Range object

Range("TaxRate").Value reads the value; [TaxRate] is a shorthand for the same thing (it is Evaluate("TaxRate")). But watch the last line above: .RefersToRange only works when the name refers to cells. If you stored a constant — Names.Add Name:="VAT", RefersTo:="=0.2" — there is no range behind it, so Range("VAT") and .RefersToRange raise an error, while [VAT] and Evaluate("VAT") correctly return 0.2. Know which kind of name you have before you treat it as cells. For how a name resolves as a formula, see VBA Formula.

Cleaning up #REF! names and hidden-name bloat

Delete the rows or columns a name covers and the name does not die — its RefersTo becomes =#REF!, a live landmine that breaks every formula referencing the name. Names also travel when you copy a sheet, quietly accumulating until a workbook carries thousands of hidden, broken names and slows to a crawl. Both are why auditing names is a maintenance task, not a one-time setup:

Dim nm As Name
For Each nm In ThisWorkbook.Names
    If InStr(1, nm.RefersTo, "#REF!") > 0 Then
        Debug.Print "broken: " & nm.Name & " -> " & nm.RefersTo
        nm.Delete                      ' remove the landmine
    End If
Next nm

Loop ThisWorkbook.Names, flag any whose RefersTo contains #REF!, and Delete them. Run the same loop with nm.Visible = False in the condition to find the hidden names a pasted sheet dragged in. A name is cheap to create and easy to forget — treat the name list as something you clean, not just fill.

How ExcelMaster helps

The named-range mistakes that cost real time are not typos — they are the relative name that drifts because a $ was missing, the sheet-scoped name a module cannot see, the .RefersToRange that blows up on a constant name, and the #REF! name nobody noticed until a report went out wrong. Each one runs; it just resolves to the wrong place.

ExcelMaster writes names the way a careful developer would. Ask it to "name the tax rate cell and use it in the calculation," and it creates an absolute, workbook-scoped name, references it everywhere instead of hardcoding $B$1, and picks sheet scope only when the region genuinely repeats per tab. It reads named values with the right call for range-names versus constant-names, and it can audit and clear the broken #REF! names a workbook has collected. You name what you mean; it wires the reference so an inserted row never silently breaks it.

Frequently asked questions

How do I create a named range in VBA?

The shortest way is to assign the Name property of a range: Range("B2:B50").Name = "SalesData", which creates a workbook-scoped, absolute name. For more control use Names.Add: ThisWorkbook.Names.Add Name:="TaxRate", RefersTo:="=Config!$B$1". The RefersTo string must start with =, and you almost always want absolute $ references so the name does not drift.

What is the difference between workbook and worksheet scope for a name?

A workbook-scoped name (ThisWorkbook.Names.Add or Range.Name =) is visible from every sheet and every module. A worksheet-scoped name (Worksheets("Jan").Names.Add) is visible only on that sheet, so different sheets can reuse the same name for their own data. Reach a sheet-scoped name through its sheet: Worksheets("Jan").Range("Region"), not a bare Range("Region").

How do I get the range a name refers to in VBA?

Use ThisWorkbook.Names("SalesData").RefersToRange to get the Range object, or just Range("SalesData") for a workbook-scoped name. To read its value, Range("SalesData").Value or the [SalesData] shorthand. .RefersToRange only works for names that point at cells — a constant name like =0.2 has no range and must be read with Evaluate or [Name].

Why does my named range point to #REF! in VBA?

Because the cells it referred to were deleted. Deleting the rows or columns a name covers does not delete the name; instead its RefersTo becomes =#REF!, and every formula using the name breaks. Audit them by looping ThisWorkbook.Names and checking whether nm.RefersTo contains #REF!, then call nm.Delete on the broken ones.

How do I delete a named range in VBA?

Call .Delete on the name: ThisWorkbook.Names("SalesData").Delete, or loop ThisWorkbook.Names and delete by a condition (for example, any whose RefersTo contains #REF!). Deleting the name does not touch the cells; it only removes the defined name, which is how you clear out the broken and hidden names that accumulate when sheets are copied.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-01.

Related guides: VBA Range · VBA Cell Value · VBA Formula · VBA Worksheet · VBA Offset