To read a workbook-level named range in Excel VBA, get its Range object and read its Value or Value2 property: Set target = ThisWorkbook.Names("TaxRate").RefersToRange, then use target.Value2. For a name scoped to a worksheet, specify the worksheet when you look it up; the right method depends on the name’s scope and what it refers to.
What a named range means in VBA
An Excel defined name gives a cell, cell range, formula, or constant a readable label. For example, a workbook might define the name TaxRate for cell B2, or SalesData for a table of values. In VBA, a name is represented by an Excel Name object. If it refers to cells, the RefersToRange property supplies the corresponding Range object.
Once you have that Range object, use its properties to get the contents, display, or other information about the cell or cells. Keep the object and its value separate: Set target = ... assigns a Range object to an object variable, while result = target.Value2 assigns the cell contents to a regular variable.
This distinction also answers a common misconception: Names("TaxRate") does not itself return the cell value. It returns a Name object. You must resolve that name to a range, then read a property of the range.
Read a workbook-level name
For a name defined with workbook scope, the Names collection of the workbook containing the code is a direct way to find it. In the example below, assume the workbook has a workbook-level name called TaxRate that refers to one cell. The code prints that cell’s underlying value in the Immediate window in the VBA editor.
Sub ReadWorkbookName()
Dim target As Range
' Resolve the workbook-level name to its cell.
Set target = ThisWorkbook.Names("TaxRate").RefersToRange
Debug.Print target.Value2
End Sub
ThisWorkbook means the workbook where the VBA code is stored. It is not necessarily the workbook currently selected in Excel. That distinction matters if the macro is stored in another workbook, such as the Personal Macro Workbook. If the name belongs to a different workbook, qualify that workbook explicitly, for example with a variable referring to the intended Workbook object.
Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. The example only prints a value, but this precaution is important when adapting code to write to cells or change workbook contents.
Read a worksheet-level name
Names can also have worksheet scope. A name scoped to the Inputs sheet is distinct from a workbook-level name with the same visible text. When the reference is made in a worksheet context, Excel can resolve a local name differently from a workbook-level name. Qualify the sheet to make the intended context clear:
Sub ReadSheetName()
Dim target As Range
' Look up the name in the Inputs worksheet context.
Set target = ThisWorkbook.Worksheets("Inputs").Range("InputValue")
Debug.Print target.Value2
End Sub
This approach assumes InputValue is a name that can be resolved from the specified worksheet and that it refers to cells. If a workbook-level and a sheet-level name share a spelling, verify the scope in Formulas > Name Manager and test that the macro resolves the intended reference. Avoid relying on whichever sheet happens to be active: an unqualified Range("InputValue") can resolve in the active sheet’s context rather than the sheet you intended.
To inspect names in Excel, select Formulas > Name Manager. The Scope column identifies whether each name belongs to the workbook or a particular worksheet. On Excel for Mac, the Name Manager is also available from the Formulas tab; menu and ribbon details can vary slightly by release. Excel for the web can display and use defined names, but it cannot run VBA macros. Use desktop Excel for the VBA examples.
Choose Value, Value2, or Text
The property you read affects the type or representation VBA receives. For most automation that needs the underlying cell content, Value2 is a practical default. It returns the cell value without converting dates to VBA Date values or currency-formatted values to the VBA Currency type. A date stored in a cell will generally come back as its Excel serial number, so format or convert it deliberately if the next step needs a date.
Valuereturns a cell’s value and can return a Date or Currency subtype depending on the cell’s contents and formatting.Value2returns the underlying value without those Date and Currency conversions; it is often useful for straightforward data transfer.Textreturns the displayed text. It can be affected by number formatting and column width, so it is usually not the best choice for calculations or data processing.
For instance, if a named cell displays 25%, the underlying numeric value is typically 0.25, while its displayed text is formatted as a percentage. Reading Value2 gives the numeric value; reading Text gives the visible representation. Decide whether your code needs data for calculations or exactly what a person sees on screen.
Handle multi-cell named ranges
A name may refer to several cells instead of one. When a multi-cell Range’s Value or Value2 is assigned to a Variant, VBA receives a two-dimensional array for a contiguous range. The array’s first dimension represents rows and its second represents columns. This lets code process the range without reading each cell individually.
Sub ReadNamedRangeArray()
Dim target As Range
Dim values As Variant
' SalesData must refer to a contiguous multi-cell range.
Set target = ThisWorkbook.Names("SalesData").RefersToRange
values = target.Value2
Debug.Print values(1, 1)
End Sub
In this example, values(1, 1) is the first row and first column of the returned array, not necessarily worksheet cell A1. If the name refers to a single cell, VBA returns a scalar value rather than a two-dimensional array, so array indexing will fail. Check the size or handle single-cell and multi-cell names separately if both are possible.
A multi-area range, such as a name referring to nonadjacent cells, needs additional care. A single Value or Value2 read does not provide one simple rectangular array covering separated areas. Iterate through the areas in target.Areas when the name is intentionally noncontiguous, and decide how your code should treat gaps between them.
Names that do not refer to cells
Not every defined name can be converted to a Range. A name may refer to a formula such as a calculation, a constant, or another expression rather than a cell reference. In those cases, RefersToRange cannot return a Range object and raises an error. This is different from a missing name: a name can exist and still not refer to cells.
For a name that represents a formula or constant, inspect its RefersTo property or evaluate the expression instead of calling RefersToRange. For example, Excel’s Evaluate method can evaluate a name in a suitable workbook or application context, but scope and formula references still matter. If a name can be either a range or a formula, write explicit error handling and decide what result is appropriate for each case rather than assuming every defined name is a range.
Likewise, a name that refers to an external workbook, a deleted cell, or a formula that currently evaluates to an error may need special handling. Inspect the name’s definition in Name Manager before changing the VBA. A broken reference is not fixed merely by using Value2.
Common errors and how to diagnose them
If VBA reports that the item with the specified name was not found, check spelling, workbook, scope, and whether the name exists in the workbook you qualified. A worksheet-scoped name may not be found through the workbook-level lookup you expected. Conversely, using an unqualified range can silently pick up a name from the active sheet and return the wrong cell.
- Confirm the name in Formulas > Name Manager, including its exact spelling and Scope.
- Confirm that the workbook qualifier points to the workbook containing the name;
ThisWorkbookandActiveWorkbookcan refer to different files. - Check whether the name refers to cells. If it is a formula or constant, use an evaluation approach rather than
RefersToRange. - Check whether the name points to multiple cells, because the result may be an array rather than a scalar.
- Use the VBA editor’s Immediate window to inspect a value printed with
Debug.Print.
When a missing name is expected to be possible, handle that case deliberately with a controlled error handler or a lookup routine that checks for the name before using it. Avoid leaving broad On Error Resume Next active across unrelated code: it can hide errors that should be fixed. If you use it for a narrow lookup, restore normal error handling immediately afterward and check whether the lookup succeeded.
When to use Range, Names, or Evaluate
Use Names(...).RefersToRange when you want to retrieve a defined name as an object and then work with its cells. This makes the distinction between finding a name and reading its value explicit. It is a good fit when you may need other Range properties, such as the address, number of rows, or formatting.
Use a qualified worksheet Range("Name") when you know the worksheet context and want concise code for a range name. Use Evaluate when the defined name represents a calculation or constant rather than a cell reference, while keeping in mind that Excel must resolve the expression in the correct context. These methods are not interchangeable: a range lookup expects a cell reference, whereas evaluation can return a calculated result.
For a one-off value, the shortest code may be enough. For maintainable automation, make the workbook and worksheet explicit, use a clearly named variable, and handle the cases where the name is missing or not a range. That extra clarity makes later workbook changes easier to diagnose.
Frequently Asked Questions
How do I get a named range value in one line of VBA?
For a workbook-level name called InputValue that refers to a cell, use Debug.Print ThisWorkbook.Names("InputValue").RefersToRange.Value2. Qualify the correct workbook, and use a worksheet context for a sheet-scoped name.
Does Range.Value return the value or the Range object?Range.Value returns the contents of the cell or cells. The Range object itself is obtained from an expression such as Range("InputValue") or Names("InputValue").RefersToRange.
How can I tell whether a name is workbook-scoped or sheet-scoped?
Open Formulas > Name Manager and inspect the Scope column. Workbook scope makes the name available throughout that workbook; worksheet scope ties it to a particular sheet and can create ambiguity when another scope uses the same name.
Why does RefersToRange give an error?
The name may be missing from the collection you searched, or it may refer to a formula or constant instead of a cell range. Check its scope and definition in Name Manager; use evaluation rather than RefersToRange if the name is not a cell reference.
Can Excel for the web run VBA to read a named range?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; the object model examples here are written for Microsoft 365 desktop Excel on Windows.
Related
- Excel Text to Columns: Can It Split Capital Letters?
- Excel Text to Columns: Split a Cell at Line Breaks
- Excel Text to Columns: How to Split Data Into Fields
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Connection Strings: Providers and ExamplesExcel VBA does not have one universal connection-string provider: choose a provider that matches the data source and…
- Access VBA For Loop: Syntax, Examples, and Common ErrorsIn Access VBA, use a For...Next loop to repeat instructions a set number of times, and use For…
- Creating Macros in Google Sheets: A Practical GuideTo create a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
- Excel UserForm: How to Show It with VBATo show an Excel UserForm, create the form in the Visual Basic Editor, then run a VBA procedure…
- Excel VBA File Open Error: Causes and FixesAn Excel VBA file-open error usually means Excel cannot access the file at the path supplied to Workbooks.Open,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.