In Excel VBA, a named range is a defined name that refers to a cell, a range of cells, or sometimes a formula or constant. You can create and manage one with the workbook’s or worksheet’s Names collection, then refer to a range by its name in code, for example with Range("SalesData").
What a named range means in Excel
A name gives a meaningful label to a cell reference. Instead of writing Data!$B$2:$B$20 repeatedly, you might define that range as SalesData. A VBA procedure can then use the label, which makes code easier to read and helps separate the meaning of a range from its current location.
Despite the common phrase “named range,” an Excel name does not have to refer to a range. It can refer to a single cell, a formula, or a constant. For instance, a name might refer to a tax-rate value or to a formula that calculates a result. This distinction matters in VBA: a name that refers to cells can be converted to a Range object, but a name that refers only to a constant cannot.
Names can be created in the Excel interface as well as in VBA. In Microsoft 365 for Windows, use Formulas > Defined Names > Name Manager to inspect existing names, or choose Formulas > Defined Names > Define Name to create one. VBA can then use the same names stored in the workbook.
Workbook scope and worksheet scope
Every Excel defined name has a scope. A workbook-scoped name is available throughout that workbook. A worksheet-scoped name is local to one sheet, so the same short name can be used on another sheet without replacing it. The Name Manager shows scope, and VBA exposes names through workbook and worksheet collections.
When two names have the same text but different scopes, Excel resolves an unqualified reference in the context of the active worksheet. That can make code dependent on which sheet is active. Use a workbook-level name for a value intended to be shared across sheets, or qualify a local name with its worksheet when the distinction matters. Avoid relying on whichever sheet happens to be active.
A name’s scope is not the same as the cells it refers to. A workbook-scoped name can point to cells on one sheet, while a locally scoped name can point elsewhere in the workbook. Decide scope based on where you want the name to be visible, not just where its cells are located.
Create a workbook-level name in VBA
Use ThisWorkbook.Names.Add to create a name in the workbook that contains the VBA code. The example assumes a worksheet named Data and data in cells B2:B20. The RefersTo argument is written as an Excel formula, so it begins with an equals sign.
Sub CreateSalesDataName()
Dim newName As Name
' Create a workbook-level name for the data cells.
Set newName = ThisWorkbook.Names.Add( _
Name:="SalesData", _
RefersTo:="='Data'!$B$2:$B$20")
End Sub
The worksheet name is enclosed in single quotes inside the reference. They are required for sheet names containing spaces or certain punctuation, and are also valid for the simple name Data. Dollar signs make the reference absolute, so it continues to point to the same cells rather than shifting with the active cell. If the sheet or cells do not exist as written, Excel cannot resolve the reference as intended.
ThisWorkbook means the workbook containing the running VBA project. ActiveWorkbook, by contrast, means the workbook currently active in Excel. If a macro opens or switches between workbooks, using ActiveWorkbook without intending to can create a name in the wrong file.
Create a worksheet-scoped name
To make a name local to a particular sheet, add it through that worksheet’s Names collection. This example defines LocalRate for cell B2 on the Data sheet. In the Name Manager, a local name is represented with its sheet scope, such as Data!LocalRate.
Sub CreateLocalRateName()
Dim ws As Worksheet
Dim newName As Name
Set ws = ThisWorkbook.Worksheets("Data")
' Add a name scoped to the Data worksheet.
Set newName = ws.Names.Add( _
Name:="LocalRate", _
RefersTo:="='Data'!$B$2")
End Sub
If the name already exists in that scope, adding it again can produce a duplicate-name error. For a reusable macro, check whether the name exists and decide whether to update it, replace it, or report the conflict. Do not silently delete names unless that behavior is intentional; another formula or macro may depend on them.
Read and use a named range
For a name that refers to cells, the RefersToRange property returns the corresponding Excel Range object. The following example assumes the workbook-level name SalesData already exists. It displays the fully qualified address, including the workbook and worksheet, which can help confirm that the name points where expected.
Sub ShowSalesDataAddress()
Dim target As Range
' Resolve the workbook-level name to its cells.
Set target = ThisWorkbook.Names("SalesData").RefersToRange
MsgBox target.Address(External:=True)
End Sub
Once you have a Range object, you can use ordinary range operations: read or write its values, format it, or loop through its cells. For example, target.ClearContents removes cell contents but leaves formatting. Treat any change as a real workbook edit; a named reference does not make an operation safer or reversible.
You can also use a name in an Excel formula from VBA, but distinguish that from fetching its cells. A named formula may be useful in a formula assignment, while RefersToRange is appropriate only when the name resolves to a range. If the name represents a constant or a formula that returns a value rather than a reference, attempting to use RefersToRange can raise an error.
Inspect, change, and delete names
A Name object has properties such as Name and RefersTo. The latter exposes the reference or formula as text. You can inspect it before changing a macro’s behavior, or assign a new reference when the named area needs to expand. This example updates the existing workbook-level name; it assumes that the name is already present.
Sub ExpandSalesDataName()
Dim namedItem As Name
' Point the existing name to a larger range.
Set namedItem = ThisWorkbook.Names("SalesData")
namedItem.RefersTo = "='Data'!$B$2:$B$25"
End Sub
To remove a name, call its Delete method, for example ThisWorkbook.Names("SalesData").Delete. Deleting the definition does not mean you have safely removed every dependency: formulas that use the name may become invalid. Review formulas and other macros first. Name Manager is a convenient visual check for names, their scope, and their references.
Name rules and reference behavior
Excel names have rules that affect what VBA can create. Names cannot contain spaces, and they cannot be written like a cell address such as A1. Names are not case-sensitive for ordinary lookup, so SalesData and salesdata should not be treated as distinct labels. Use clear, consistent names and avoid names that are easily confused with sheet names or cell references.
Absolute references such as $B$2:$B$20 are usually the least surprising choice for a named data range. Relative references can behave differently depending on the active cell and the context in which the name is evaluated. If a name appears to point to a different location when used from another cell or sheet, inspect its RefersTo definition and check whether its reference is relative.
Names can also refer to dynamic formulas. For example, a name may be defined to calculate a range or result rather than store a fixed address. VBA can use such a name, but the appropriate way to retrieve it depends on what the name evaluates to. Do not assume every item in Names can be treated as a normal rectangular range.
Common errors and how to avoid them
- Name not found: Confirm the spelling and scope. A local name may not be available through an unqualified workbook-level lookup, and a similarly named item may exist on another sheet.
- Subscript or object error: Check that the target worksheet and workbook-level name exist, and qualify references with
ThisWorkbookor the intended worksheet rather than relying on the active workbook. - RefersToRange fails: The name may represent a constant or a formula rather than a cell reference. Inspect its
RefersToproperty and use a method suitable for the value it represents. - Duplicate name error: The name may already exist in the same scope. Decide whether to update the current definition or use a different name; do not assume a second call to
Names.Addreplaces it. - Unexpected target cells: Check the sheet name, absolute versus relative references, and the name’s scope in Name Manager. A valid name can still point to the wrong range.
For larger automation, it is sensible to validate the workbook structure before changing cells: confirm the expected sheet exists, verify the named reference, and handle missing or invalid names deliberately. This makes the macro’s assumptions visible and reduces the chance that a workbook layout change leads to edits in an unintended location.
Named ranges versus hard-coded addresses
A hard-coded address can be perfectly appropriate for a small, stable task. It is direct and easy to understand when the target is fixed and used once. A named range becomes more valuable when the same logical data is referenced in several formulas, procedures, or user instructions. The name communicates purpose, whereas an address communicates only position.
Names do not automatically make a macro robust. If a name points to the wrong cells, code that uses it will also target the wrong cells. And if a range grows over time, a fixed name definition such as B2:B20 will not necessarily include new rows. Consider whether the workbook’s data is better represented by an Excel Table, whose structured references expand with the table, or by a name whose reference is deliberately maintained by the workbook’s design.
Macro safety and platform notes
VBA runs in desktop Excel, not in Excel for the web. Excel for the web can preserve a workbook containing macros, but it cannot run or edit the VBA project. Microsoft 365 for Mac supports VBA, although the ribbon layout and VBA editor shortcuts can differ from Windows; the Excel object model examples above are intended for desktop Excel and should be checked against any Mac-specific workflow.
Before running a macro that creates, changes, or deletes names—or edits the cells those names point to—save a separate copy of the workbook. VBA actions cannot be undone with Ctrl+Z. Only enable macros in files you trust, and save the workbook in a macro-enabled format such as .xlsm if you need to retain its VBA project.
Frequently Asked Questions
How do I refer to a named range in VBA?
For a range name available in the current worksheet context, you can use Range("SalesData"). To be explicit about a workbook-level definition, use ThisWorkbook.Names("SalesData").RefersToRange and assign the result to a declared Range variable.
How do I create a named range with VBA?
Call Names.Add on the intended workbook or worksheet collection, supplying a name and a RefersTo formula. Use a workbook collection for a workbook-level name and a worksheet collection for a local name, and make sure the reference points to the intended cells.
What is the difference between a workbook name and a sheet name?
A workbook-scoped defined name is available across the workbook. A worksheet-scoped defined name is local to one sheet and can coexist with a same-text name scoped to another sheet. Neither is the same thing as the worksheet tab name.
Why does RefersToRange give an error?RefersToRange requires the name to refer to cells. If the name defines a constant or a formula that returns a value rather than a range reference, there is no range object to return. Check the definition in Name Manager or inspect the name’s RefersTo property.
Can I use a named range in Excel for the web?
You can work with workbook names and formulas in Excel for the web, but you cannot run VBA macros there. To execute the VBA code, open the workbook in desktop Excel with macros enabled and the file in a suitable macro-enabled format.
Related
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Message Box: How to Use MsgBox in MacrosIn Excel VBA, use the MsgBox function to show a message, optionally display buttons or an icon, and…
- Excel VBA: How to Hide a SheetTo hide a worksheet with VBA, set its Visible property to xlSheetHidden, for example: ThisWorkbook.Worksheets("Report").Visible = xlSheetHidden. Use…
- Excel VBA: Keep a UserForm on TopTo keep an Excel VBA UserForm above other windows on Windows, call the Windows API function SetWindowPos with…
- Turn On AutoFilter in VBA: Code and ExamplesTo turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter…
- Excel VBA: How to Close a UserForm After a Button ClickTo close an Excel VBA UserForm when a button is clicked, put Me.Hide in that button’s Click event…
- Excel VBA Current Date: Date, Now, Formatting, and ExamplesIn Excel VBA, use Date to get the computer’s current date, without a time; use Now to get…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.