Excel VBA Create Named Range: Code, Scope, and Tips

VBA & macros · Updated November 21, 2026

To create a named range with Excel VBA, add a name to the workbook’s Names collection and set its RefersTo property to the range address. For example, ThisWorkbook.Names.Add Name:="SalesData", RefersTo:="='Data'!$A$2:$A$100" creates a workbook-scoped name for cells A2:A100 on the worksheet named Data.

In a macro, it is usually safer to build the reference from a Range object so the code can use the worksheet’s actual name and address. The examples below show how to create workbook- and worksheet-scoped names, update an existing name, and choose a suitable reference for changing data.

What a named range does

A named range gives a cell or group of cells a descriptive identifier. Instead of referring to a range as Data!$A$2:$A$100, a formula, chart, or macro can refer to it as SalesData. Names can also refer to a single cell, a constant value, or a formula; the term “named range” is commonly used even when the name does not refer to multiple cells.

Names are useful when a workbook has formulas or code that would be hard to understand from cell addresses alone. They can make a calculation’s intent clearer, provide a stable reference for validation lists, and give VBA a convenient way to find a range. A name does not, by itself, protect cells or guarantee that the referenced data is correct.

Create a workbook-scoped name with VBA

This example creates a name available throughout the workbook. It points to cells A2:A100 on a worksheet called Data. The code builds the reference from the worksheet and range, so it does not rely on the active sheet or on a hard-coded workbook filename.

Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module using Insert > Module, and run the procedure from the editor or with Alt+F8. On Mac, the editor shortcut is usually Option+F11, sometimes with Fn as well, depending on the keyboard.

Sub CreateSalesDataName()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim target As Range
    Dim escapedSheetName As String
    Dim refersToFormula As String

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set target = ws.Range("A2:A100")

    ' Escape apostrophes in a worksheet name for a formula reference.
    escapedSheetName = Replace(ws.Name, "'", "''")
    refersToFormula = "='" & escapedSheetName & "'!" & target.Address

    ' Add a workbook-level name.
    wb.Names.Add Name:="SalesData", RefersTo:=refersToFormula
End Sub

ThisWorkbook means the workbook containing the VBA code. That is often the intended target, but it is not necessarily the workbook currently selected in Excel. If the code is stored in Personal.xlsb or an add-in, ThisWorkbook refers to that file instead. In that situation, assign the intended workbook explicitly rather than assuming the active workbook is the right one.

The reference is assembled as a formula such as ='Data'!$A$2:$A$100. Quoting the worksheet name also handles names containing spaces, and doubling an apostrophe handles names such as Manager's Data. The range’s Address property returns an absolute A1-style address by default.

Choose workbook or worksheet scope

Scope determines where Excel can resolve an unqualified name. A workbook-scoped name can be used from any worksheet in that workbook. A worksheet-scoped name belongs to one sheet and can be useful when several sheets need separate names that happen to use the same text.

  • Workbook scope: add the name through wb.Names.Add. This is the typical choice for a range used across multiple sheets, in workbook formulas, or by general-purpose VBA.
  • Worksheet scope: add it through the worksheet’s Names collection, such as ws.Names.Add. Excel treats it as local to that sheet.

Here is the corresponding worksheet-scoped version. It uses the same reference construction but adds the name to the Data worksheet’s collection.

Sub CreateLocalSalesDataName()
    Dim ws As Worksheet
    Dim target As Range
    Dim escapedSheetName As String
    Dim refersToFormula As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("A2:A100")

    ' Build a quoted reference to the worksheet range.
    escapedSheetName = Replace(ws.Name, "'", "''")
    refersToFormula = "='" & escapedSheetName & "'!" & target.Address

    ' Add the name with worksheet scope.
    ws.Names.Add Name:="SalesData", RefersTo:=refersToFormula
End Sub

A workbook-level and a worksheet-level name can share a name. When both exist, a formula entered on that worksheet generally resolves the local name first; a workbook-level reference can be qualified with the workbook name in Excel’s name syntax when you need to distinguish it. This can confuse both users and code, so avoid duplicate names unless the local override is deliberate. In VBA, inspect the relevant Names collection and the name’s full qualified name when resolving ambiguity.

Prevent errors when the macro runs again

Names.Add creates a name; it does not serve as an automatic “update this name” command. Running the creation macro again while that workbook-scoped name already exists can produce a duplicate-name error. Decide whether the macro should stop, preserve the current name, or replace it. Replacing is appropriate only when the macro is meant to control that name’s definition.

This version deletes the workbook-scoped name if it exists, then creates it again. The error suppression is limited to the delete attempt so a missing name does not prevent creation; errors in the later steps are not silently ignored.

Sub ReplaceSalesDataName()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim target As Range
    Dim escapedSheetName As String
    Dim refersToFormula As String

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set target = ws.Range("A2:A100")

    ' Ignore only the error raised when the name is not present.
    On Error Resume Next
    wb.Names("SalesData").Delete
    On Error GoTo 0

    escapedSheetName = Replace(ws.Name, "'", "''")
    refersToFormula = "='" & escapedSheetName & "'!" & target.Address
    wb.Names.Add Name:="SalesData", RefersTo:=refersToFormula
End Sub

Deleting and recreating a name can affect formulas, validation rules, charts, or other code that depends on it if the new definition is wrong. A name can also be hidden or have a worksheet scope, so deleting wb.Names("SalesData") is not a general-purpose way to remove every name with that visible text. For a production macro, check the intended scope and the current definition before changing anything.

Use a name for changing data

A fixed address such as A2:A100 is easy to understand, but it will not automatically expand when new rows are added below row 100. There are several ways to handle changing data, and the best choice depends on how the workbook is maintained.

  • Use an Excel Table: format the data as a table and give it a meaningful table name. A name can refer to a table column, for example =SalesTable[Sales] when the table is named SalesTable and its column heading is Sales. Table columns expand as table rows are added, making this a clear option for growing lists.
  • Update the fixed range in VBA: calculate the last data row and rebuild the address when the macro runs. This can work well when the macro owns the data layout, but the last-row method must match the sheet’s actual structure.
  • Use a dynamic formula definition: a name can refer to a formula rather than a literal address. Dynamic formulas require careful testing, especially if they rely on volatile functions or assumptions about blank cells.

For a table-column name, VBA can add the structured reference directly. The table and column must already exist, and the text must match their names.

Sub CreateTableColumnName()
    Dim wb As Workbook

    Set wb = ThisWorkbook

    ' SalesTable and its Sales column must already exist.
    wb.Names.Add Name:="SalesValues", RefersTo:="=SalesTable[Sales]"
End Sub

When a table is not suitable, a macro can use CurrentRegion or determine the last used row. Neither is universally correct: CurrentRegion stops at blank rows or columns, while a last-row search can include unrelated content if the chosen column is not dedicated to the list. Verify the resulting address before assigning it to a name.

Inspect and manage names in Excel

In Excel for Microsoft 365 on Windows, open Formulas > Name Manager to review names, their scope, and the Refers To definition. The Name Box, to the left of the formula bar, can also display names and let you select a named range. These interface tools are useful for checking what a macro created without needing to inspect VBA.

The Name Manager can help diagnose a name that points to the wrong sheet, includes an unintended absolute or relative reference, or has a spelling difference. Names may also be hidden or have local scope, which can make them less obvious from a quick glance at the Name Box. The worksheet’s Names collection and workbook’s Names collection are separate places to examine in VBA.

Excel for the web can use many existing names in workbooks, but it does not run VBA macros or provide the desktop VBA editor. Create or modify the name with desktop Excel, or use a non-VBA method if the workbook must be edited entirely in a browser. Excel for Mac supports VBA, though editor access and some keyboard shortcuts differ from Windows.

Common mistakes and troubleshooting

  • Wrong active sheet: code based on ActiveSheet can create a name for whichever sheet happens to be selected. Set a worksheet variable explicitly when the destination matters.
  • Misspelled worksheet or table: Worksheets("Data") fails if the tab is named differently. Structured references likewise require the actual table and column names.
  • Invalid or conflicting name: Excel names have naming rules and can conflict with cell-reference syntax or existing names. Choose clear identifiers without spaces, and check existing workbook and local names.
  • Reference points somewhere unexpected: inspect the Refers To entry in Name Manager. A validly created name can still refer to the wrong range because the address or sheet was incorrect.
  • Code targets the wrong file: distinguish ThisWorkbook from ActiveWorkbook. The former contains the code; the latter is the workbook currently active in the Excel interface.

When a macro reports that a subscript is out of range, check the worksheet name and workbook assignment first. When it reports that the name already exists, decide whether to reuse, update, or remove the existing name rather than repeatedly rerunning an unchanged creation procedure.

Frequently Asked Questions

How do I create a named range in VBA? Use a workbook’s Names collection, such as ThisWorkbook.Names.Add Name:="MyRange", RefersTo:="=Sheet1!$A$1:$A$10". For code that must handle sheet names with spaces or apostrophes, build the RefersTo string from a Worksheet and Range object.

What is the difference between workbook and worksheet scope? A workbook-scoped name is available across the workbook. A worksheet-scoped name belongs to one sheet and may take precedence over a workbook name of the same text when used on that sheet.

How can I change a named range that already exists? Set its RefersTo definition again or delete and recreate it, taking care to target the correct scope. Check dependent formulas and other workbook features before replacing a definition.

Can a VBA named range expand when I add rows? A fixed address will not expand by itself. A table-column reference is often the simplest option for data that grows; alternatively, update the name’s reference in VBA or use a carefully designed dynamic definition.

Can I run the VBA macro in Excel for the web? No. Excel for the web does not run VBA. Use desktop Excel for Windows or Mac to run the macro, or choose a non-VBA approach when working only in the browser.

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.