Excel VBA Range Names: How to Find, Create, and Use Them

VBA & macros · Updated November 21, 2026

In Excel VBA, a range name is a defined name that refers to a cell, a group of cells, or sometimes a formula or value. To refer to a named cell range in a macro, use an explicit workbook reference such as ThisWorkbook.Names("Sales").RefersToRange; this makes clear which workbook the name belongs to and returns the actual Range object.

For example, if the workbook contains a defined name called Sales that refers to Data!B2:B20, VBA can use that name instead of repeating the cell addresses. The key detail is that a defined name is not always a range: some names represent formulas or constants, so they cannot all be converted to a Range object.

What a range name means in Excel

An Excel range name, also called a defined name, is a label for a cell reference or another expression. Instead of writing a formula or macro around a hard-to-read address such as $B$2:$B$20, you can give that area a meaningful name such as Sales. Names can make formulas and VBA code easier to read, and they can reduce the number of places where an address has to be updated when a worksheet layout changes.

Names are managed in the workbook, but they are not the same thing as worksheet tab names, table names, or VBA variable names. A VBA variable exists while code runs; a defined name is stored in the workbook and can be used by formulas, the Name Box, and VBA. Excel also allows names to refer to formulas such as a calculated value, so the word name does not guarantee that the item identifies cells.

Defined names generally begin with a letter, an underscore, or a backslash, and cannot contain spaces. They cannot look like a cell address, such as A1, and Excel treats names as case-insensitive. These rules matter when creating a name in code: an invalid name or a name that conflicts with an existing definition can cause the macro to fail.

How VBA resolves a name

VBA uses the Range object to work with cells. A worksheet-qualified reference such as Worksheets("Data").Range("A1") identifies a cell on a particular worksheet. A name can also be passed to Range, but an unqualified call such as Range("Sales") depends on the active sheet and the names visible in that context. That makes it less reliable in a macro that might run while another sheet is active.

For a workbook-scoped name, referencing the Names collection and then its RefersToRange property is explicit:

Sub ReadNamedRange()
    Dim salesRange As Range
    Dim total As Double

    ' Read the workbook-scoped name Sales.
    Set salesRange = ThisWorkbook.Names("Sales").RefersToRange
    total = Application.WorksheetFunction.Sum(salesRange)

    MsgBox total
End Sub

This example assumes a workbook-scoped name called Sales points to cells that contain values suitable for summing. ThisWorkbook means the workbook containing the VBA code. ActiveWorkbook instead means whichever workbook is currently active, which may not be the workbook the macro was written for. Using ThisWorkbook is usually safer for a workbook-specific procedure.

RefersToRange returns a Range object only when the name refers to a cell or range. If Sales is instead defined as a constant, or as a formula such as a calculation that returns a number, this property raises an error. In that case, use the Name object’s RefersTo property to inspect the name’s definition, or evaluate the expression as appropriate rather than assuming it is a range.

Workbook scope and worksheet scope

Excel names can have workbook scope or worksheet scope. A workbook-scoped name can ordinarily be used from any sheet in that workbook. A worksheet-scoped name is associated with one particular sheet, and a same-named workbook name may also exist. Excel can resolve the worksheet-level name in preference to the workbook-level one when a formula is used on that sheet. This is a frequent source of confusion when a macro appears to retrieve a different range than expected.

The Name Manager displays the scope for each defined name. In Excel for Microsoft 365 on Windows, open Formulas > Defined Names > Name Manager. Check both the name and the Scope column before relying on a name in VBA. If two definitions share a label, make the reference unambiguous by selecting the intended worksheet or workbook context instead of depending on whichever sheet happens to be active.

For a worksheet-scoped name, work through the worksheet that owns it. For example, assuming the Data worksheet has a local name TaxRate, the following retrieves that sheet’s range:

Sub ReadLocalName()
    Dim dataSheet As Worksheet
    Dim taxCell As Range

    ' Use the name belonging to the Data worksheet.
    Set dataSheet = ThisWorkbook.Worksheets("Data")
    Set taxCell = dataSheet.Range("TaxRate")

    MsgBox taxCell.Value
End Sub

When auditing names, the Name Manager is useful for checking scope and the Refers To definition. You can also use the Name Box, to the left of the formula bar, to select a name and see the cells it refers to. The Name Box is convenient for navigating, but it does not show as much definition and scope detail as the Name Manager.

Create a named range in VBA

You can create a name through the Excel interface or with the Names collection in VBA. In the interface, select the cells, enter a valid name in the Name Box, and confirm it. For more control over its scope and definition, use Formulas > Defined Names > Define Name. The Name Manager is preferable when you need to inspect or edit several existing definitions.

This macro creates a workbook-scoped name Sales for cells B2:B20 on a worksheet named Data:

Sub CreateSalesName()
    Dim targetRange As Range

    ' Define the cells that Sales will refer to.
    Set targetRange = ThisWorkbook.Worksheets("Data").Range("B2:B20")

    ' Create a workbook-scoped name.
    ThisWorkbook.Names.Add Name:="Sales", _
        RefersTo:="=Data!$B$2:$B$20"
End Sub

The example assumes the worksheet is named Data and that a workbook-scoped name called Sales does not already exist. If the sheet name contains spaces or punctuation, the sheet reference in the RefersTo expression needs the appropriate single quotes around the sheet name. If the name already exists, adding it again may produce an error; decide whether the macro should update the existing definition, delete it and recreate it, or stop with a clear message.

To make a worksheet-scoped name, add it through that worksheet’s Names collection instead of ThisWorkbook.Names. For instance, dataSheet.Names.Add associates the definition with the Data worksheet. Keep the scope intentional: adding many local names with repeated labels can make later maintenance harder, even if each name works on its own sheet.

Use names to make macros easier to maintain

A useful name describes the role of a range rather than its current address. If a macro reads a tax rate, the name TaxRate is clearer than a reference to B2. If the layout changes and the definition is updated, code that uses the name can remain unchanged. A name is especially helpful when the same input or output is used in several procedures.

Names are not a substitute for checking the data they refer to. A named range can be deleted, changed to a different address, or defined with the wrong scope. A macro that assumes a fixed number of rows may also fail to process new data even if the named range itself remains valid. For recurring lists, an Excel table can be a better structure because its data area expands as rows are added.

Before running a procedure that depends on names, consider whether it should verify that the name exists and whether it refers to the expected cells. A simple macro may reasonably stop if the workbook has been edited. A macro used by other people may need clearer error handling, such as a message naming the missing definition, so a user can correct the workbook rather than encountering an unexplained run-time error.

Dynamic ranges and Excel tables

A name can refer to a fixed cell address or to a formula that adjusts as the data changes. Fixed references are easy to understand, but a name pointing to B2:B20 will not automatically include a new record entered in B21. For a list that grows, an Excel table is often easier to maintain: select the data and choose Insert > Table, then use the table and column references in formulas or VBA. Table names and defined names are related concepts, but they are separate objects.

Defined names can also refer to formulas, including formulas that return a range. Excel 365 has newer functions that can return dynamic arrays, but compatibility depends on the Excel version used by everyone who opens the workbook. If a name refers to a formula rather than a direct cell address, check that the target Excel version supports the formula and verify how VBA interacts with the result. RefersToRange is not a universal way to retrieve every formula result.

For VBA that needs a changing set of rows, a table’s DataBodyRange can be a more explicit choice than a manually maintained name. It represents the table’s data rows, excluding the header. Code should account for an empty table, because a table with no data rows may not provide a usable data range. Choose the approach that matches the workbook: named cells for stable inputs, names for meaningful reusable references, and tables for structured records that grow.

Common errors and how to prevent them

Most problems with named ranges come from a mismatch between the name’s actual definition and the way the macro expects to use it. The error may occur when looking up the name, converting it to a range, or reading the cells. Check the name in Name Manager before changing code; this separates a broken definition from a VBA reference issue.

  • Name not found: Confirm the spelling, workbook, and scope. A name stored in another open workbook is not necessarily available through ThisWorkbook.Names.
  • Wrong range returned: Look for both workbook-level and worksheet-level definitions with the same label, and avoid relying on the active sheet.
  • RefersToRange error: Check whether the definition is a formula or constant rather than a direct reference to cells.
  • Invalid name when creating: Remove spaces, avoid a cell-address-like label, and check whether a definition with that name already exists.
  • Range does not include new records: Review whether the name is a fixed address; consider using an Excel table or updating the definition.

When the code creates or changes definitions, save a separate copy of the workbook before running the macro. Macros cannot be undone with Ctrl+Z, and a procedure may alter workbook structure or data before an error occurs. To keep VBA code in the workbook, save it in a macro-enabled Excel file such as .xlsm, and only enable macros from sources you trust.

Alternatives to a defined range name

Use a direct cell address when the reference is short-lived, local to one procedure, and unlikely to move. Use a worksheet-qualified address when the target is known and a defined name would add little clarity. Use an Excel table for rows of related records that are routinely added, filtered, or sorted. Use a defined name for a meaningful cell or range that is reused or should be easy for workbook users to locate.

These choices can coexist. A table may hold transaction records while a workbook-scoped name identifies an important input cell elsewhere. The goal is not to name every range; it is to make references understandable and dependable. A short, descriptive name with deliberate scope is generally more useful than a large collection of vague or duplicated names.

Frequently Asked Questions

How do I refer to a named range in Excel VBA?
For a workbook-scoped name that refers to cells, use ThisWorkbook.Names("NameHere").RefersToRange and assign the result to a Range variable. Replace NameHere with the actual defined name. For a worksheet-scoped name, use the relevant worksheet’s Range property.

What is the difference between a named range and a VBA variable?
A named range is a definition stored in the Excel workbook and may be used in formulas or VBA. A VBA variable is a temporary programming value declared in code. A variable can hold a Range object returned from a named range, but it does not create or replace the workbook definition.

Can a named range refer to a formula instead of cells?
Yes. A defined name may refer to a formula or constant as well as a cell reference. RefersToRange works only when the definition resolves to a range; it is not appropriate for every defined name.

Why does VBA return the wrong range for a name?
Check whether a worksheet-scoped name and a workbook-scoped name share the same label. Also check which sheet is active and whether the code uses an unqualified Range call. Explicitly refer to the correct workbook or worksheet to avoid ambiguous resolution.

Will a named range automatically expand when I add rows?
Not if it refers to a fixed address such as B2:B20. The definition must be designed or updated to include additional rows. For data that grows regularly, an Excel table is often simpler to maintain than a fixed-size named range.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.