An Excel macro named range is a descriptive name, such as SalesAmounts, that VBA can assign to a cell or range and use instead of a hard-to-read address. In Excel for Microsoft 365 on Windows, you can create one in VBA with the Names.Add method, then refer to it by name in a macro or worksheet formula.
What a Named Range Means in a Macro
A named range is an identifier attached to a cell, a group of cells, or, in some cases, a formula or constant. For example, a workbook might call the cells D2:D20 on a Data sheet SalesAmounts. A macro can then refer to that name rather than having the address D2:D20 embedded in several places.
The name does not contain the data itself. It points to a reference, and the workbook stores that reference along with the name. If the name refers to cells, changes to the cells’ values appear wherever a formula or macro reads the named range. A name can also refer to a formula, so “named range” is a familiar shorthand even when a defined name does not point to a literal block of cells.
For example, with sales amounts in Data!D2:D20, a worksheet formula entered in a cell can use =SUM(SalesAmounts). If the cells contain 10, 20, and 30, with the remaining cells blank, the result is 60. A VBA procedure can use the same name to read, format, copy, or otherwise process that data.
Why Use Names Instead of Cell Addresses?
Names make automation easier to read and maintain. A macro line that refers to SalesAmounts communicates intent more clearly than one that refers to D2:D20. If the data moves and you update the name’s reference, the macro can continue to use the same name. This is especially useful when a workbook has several reports, input areas, or repeated automation tasks.
A name can also centralize a workbook setting. For instance, a workbook-level name called ReportDate can point to the cell holding a reporting date. Multiple procedures can read the same named cell rather than each relying on a fixed address. Names do not make a macro automatically more reliable, however: a misspelled name, a deleted name, or a name with the wrong scope can still cause errors.
Create a Named Range in Excel First
You can create a name in the Excel interface without writing code. Select the cells, then use Formulas > Define Name. In the New Name dialog, enter a name, check the range shown in Refers to, choose the scope, and select OK. To inspect or edit names later, go to Formulas > Name Manager.
For the example, select Data!D2:D20 and define the name SalesAmounts with Workbook scope. That creates the name the VBA example below will use. You can also type a name into the Name Box, located to the left of the formula bar, after selecting the intended cells; this is a quick way to create a simple workbook-level name.
Excel names must follow naming rules. A name must begin with a letter, underscore, or backslash, cannot contain spaces, and cannot be confused with a cell reference such as A1. Use underscores or capitalization to make multiword names readable, for example Sales_Amounts. Names are generally not case-sensitive, so SalesAmounts and salesamounts refer to the same name.
Create a Named Range with VBA
To create a workbook-level name in a macro, use the workbook’s Names collection and its Add method. The following example defines SalesAmounts for D2:D20 on a sheet named Data:
Sub CreateSalesAmountsName()
Dim ws As Worksheet
Dim target As Range
' Use the worksheet and cells in this workbook.
Set ws = ThisWorkbook.Worksheets("Data")
Set target = ws.Range("D2:D20")
' Add a workbook-level name that refers to the selected cells.
ThisWorkbook.Names.Add Name:="SalesAmounts", _
RefersTo:="='" & ws.Name & "'!" & target.Address
End Sub
In the Visual Basic Editor, insert the procedure in a standard module, then run it while the intended workbook is open. The code uses ThisWorkbook, which means the workbook containing the macro, rather than whichever workbook happens to be active. The sheet name and range in the example must match the workbook; change them if your layout differs.
Running this procedure a second time can produce an error because the name already exists. Before rerunning it, check Formulas > Name Manager. If you want a repeatable procedure, deliberately decide what should happen when the name exists: update its reference, remove and recreate it, or leave it unchanged. Do not delete a name automatically unless you are sure that replacing it is appropriate.
Use a Named Range in a Macro
Once the workbook-level name exists, VBA can access the referenced cells through a Range object. For example, this procedure reads the total of the named cells and writes it to a specified cell:
Sub WriteSalesTotal()
Dim total As Double
' Read the named range in the workbook containing this macro.
total = Application.WorksheetFunction.Sum( _
ThisWorkbook.Names("SalesAmounts").RefersToRange)
' Put the result on the Summary sheet.
ThisWorkbook.Worksheets("Summary").Range("B2").Value = total
End Sub
This example assumes SalesAmounts already exists and refers to numeric values, and that the workbook has a Summary sheet. It uses the same range as the worksheet formula example: if D2:D20 contains 10, 20, and 30 and the other cells are blank, the macro writes 60 to Summary!B2. If the name is missing or points to a formula rather than a cell range, RefersToRange may not work as intended.
For a single cell, the same approach can read or set its value. For a multi-cell range, you can apply formatting or loop through its cells. A name is only a way to locate the target; it does not dictate what the macro does with that target.
Workbook Scope and Worksheet Scope
Scope determines where Excel recognizes a name. A workbook-scoped name is available throughout the workbook and is usually the best choice for a name that a general-purpose macro needs to use. The example creates a workbook-level name through ThisWorkbook.Names.Add.
A worksheet-scoped name is associated with one particular sheet. Excel allows a local name and a workbook name to have the same visible text; in a context where both apply, the local name can take precedence. This can make a macro appear to use the wrong cells if it relies on an unqualified name. In Name Manager, check the Scope column as well as the name and its Refers to value.
When a procedure needs a specific sheet’s local name, qualify the reference rather than relying on whichever sheet is active. For maintainable automation, avoid duplicate names across scopes unless there is a clear reason for them. If a name is meant to be shared by several procedures or sheets, use workbook scope and a distinctive name.
Fixed and Dynamic References
The example points to a fixed range, D2:D20. That is straightforward when the data size is stable, but it will not automatically include new rows added below row 20. If the list grows, update the name’s Refers to reference or design the workbook so the data is stored in an Excel table.
Tables are often a practical alternative for expanding datasets. Select the data and use Insert > Table; formulas and macros can then work with the table and its columns. Tables expand as rows are added in normal use, reducing the need to revise a fixed cell range. A named range can still be useful when you want a concise label for a particular input or output area.
Names can also refer to formulas, including formulas that calculate a reference. Such setups can make a name adapt to changing data, but they are more complex to maintain than a simple fixed reference. If a macro expects a range, verify that the name actually resolves to cells. A name that refers to a calculated value rather than a range cannot necessarily be used as a Range object through RefersToRange.
Common Problems and How to Diagnose Them
- Subscript out of range: The worksheet name in the code may not match the tab name exactly, or the code may be targeting a different workbook than expected. Check the spelling and confirm that the sheet belongs to
ThisWorkbook. - Name already exists: The macro has been run before, or another process created the name. Inspect Name Manager and decide whether to update, retain, or deliberately replace the existing definition.
- Name not recognized: Check spelling and scope in Name Manager. A worksheet-level name may not be available where a workbook-level name is expected.
- Wrong cells are changed: Review the name’s Refers to entry and confirm that it points to the intended sheet and addresses. Also check for a local name that shadows a workbook-level name.
- New rows are ignored: The name refers to a fixed address. Expand the reference or use an Excel table if the dataset is expected to grow.
- Macro cannot run: The file may not be macro-enabled, or Excel may block macros under its security settings. Confirm that the workbook is saved as an Excel Macro-Enabled Workbook (
.xlsm) and only allow code from a trusted source.
Saving, Running, and Platform Notes
Save a workbook containing VBA as .xlsm; saving it as a standard .xlsx file does not preserve its VBA project. On Windows, Alt+F11 opens the Visual Basic Editor in Excel. The Mac shortcut can depend on the keyboard and function-key settings; the Visual Basic Editor is also available from Excel’s Tools menu. Excel for the web can open a macro-enabled workbook but cannot create, run, or edit VBA macros, so use the desktop Excel app for this procedure.
Macros can make changes that Ctrl+Z cannot undo. Before running a macro that changes names or cell contents, save a separate copy of the workbook. Do not lower macro security or enable all macros just to run unfamiliar code. Follow your organization’s policy and use only workbooks and procedures you trust. These precautions matter even for small tasks because VBA can modify or delete workbook content.
Frequently Asked Questions
Can a macro create a named range automatically? Yes. VBA can add a name with the workbook’s Names collection and the Add method, specifying the name and the reference. Check first whether the name already exists so a repeated run does not fail or overwrite something unexpectedly.
How do I see the named ranges in a workbook? In desktop Excel, choose Formulas > Name Manager. Review the Name, Value, Scope, and Refers to columns to confirm what the name points to and whether it applies to the workbook or one worksheet.
Can I use a named range in both a cell formula and VBA? Yes, provided the name is available in the relevant scope. For example, the worksheet formula =SUM(SalesAmounts) and a VBA reference to ThisWorkbook.Names("SalesAmounts").RefersToRange can use the same workbook-level range.
Why does my macro say the name already exists? Excel will not add another name with the same workbook-level identifier in the same scope. The name may have been created manually or by an earlier macro run. Inspect it in Name Manager and choose whether to reuse it or change its definition.
Do named ranges expand when I add rows? Not when the name refers to a fixed address such as D2:D20. Add rows within an Excel table or revise the name’s reference if the target range needs to grow; verify the resulting reference before relying on it in automation.
Related
- Excel: Import Multiple CSV Files Into One Worksheet
- Excel Insert Blank Row Every Other Row: Methods That Work
- Excel Import CSV With Headers: Steps, Options, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Double Quotes in Strings: How to Escape a QuoteTo include a double quote inside a VBA string, type two double quotes in a row: VBA treats…
- On Error in Excel VBA: How to Handle Runtime ErrorsOn Error is the VBA statement that controls what a procedure does when a runtime error occurs in…
- Excel UserForm Controls: A Practical Guide to Types and UseExcel UserForm controls are the buttons, text boxes, lists, and other interface elements you add to a VBA…
- Excel VBA Global Variables: Scope and ExamplesIn Excel VBA, a global variable is a value that procedures throughout the same VBA project can access.…
- Excel VBA Wait: Pause Macros, Timing, and AlternativesTo pause an Excel VBA macro until a particular time, use Application.Wait, for example Application.Wait Now + TimeSerial(0,…
- How to List All Files in a Folder Using Excel VBATo list files directly inside a folder in Excel for Microsoft 365 on Windows, run a VBA macro…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.