Usually, yes: when you write a named range directly inside a VBA reference such as Range("TaxRate"), put its name in straight double quotes because VBA is receiving a text string. You do not put quotes around the name in an Excel worksheet formula, and you do not quote a VBA variable that contains the name.
Why VBA Uses Quotes Around a Named Range
In VBA, Range needs an address or range name to identify which cells to work with. Writing Range("TaxRate") passes the characters TaxRate as a string. Excel then looks for a range with that defined name. The quotes belong to VBA’s string syntax; they are not part of the name itself.
This is the same reason a cell address is written as Range("A1"). Both A1 and TaxRate are supplied as text arguments to the Range property. A named range is not a special VBA keyword, so typing Range(TaxRate) does not automatically tell VBA to look for an Excel name called TaxRate. VBA instead tries to interpret TaxRate as a variable, property, or other identifier.
Defined names are useful because code can refer to the meaning of a cell or area rather than its changing address. A macro that uses Range("TaxRate") can remain understandable even if the cells around the tax-rate input are rearranged.
Basic Syntax and a Working Example
Suppose a workbook has a workbook-scoped named range called TaxRate that refers to one cell. This read-only macro retrieves its value and displays it:
Before running any macro, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, even when a later version of the macro makes changes.
Sub ShowTaxRate()
Dim taxRateCell As Range
' Get the range referred to by the workbook-level name.
Set taxRateCell = ThisWorkbook.Names("TaxRate").RefersToRange
' Display the value currently stored in that cell.
MsgBox taxRateCell.Value
End Sub
Here, "TaxRate" is a string key used to look up the name in the workbook’s Names collection. The result of RefersToRange is a Range object, so the code assigns it with Set. This example assumes the name refers to cells. A defined name can instead refer to a formula or constant; in that case, RefersToRange cannot return a cell range.
For a simple reference in a macro, Range("TaxRate").Value is also common. The collection-based version makes the workbook scope explicit and is often a clearer choice when names with the same spelling may exist at different scopes.
When You Should Not Put Quotes Around the Name
If the name is stored in a VBA variable, quote the variable’s contents nowhere in the call. The variable itself is an identifier, while the value inside it is the text that Excel uses as the range name. For example, after setting rangeName to "TaxRate", the call is Range(rangeName), not Range("rangeName"). The latter searches for a defined range literally named rangeName.
This distinction is easy to see in a small pattern:
Range("TaxRate")means “use the name TaxRate.”Range(rangeName)means “use the address or name stored in the variable rangeName.”Range("rangeName")means “look for the literal range name rangeName.”
Likewise, if you create a variable that refers to the Range object, use the variable without quotation marks when accessing its properties: taxRateCell.Value. Quotes around a VBA variable turn its spelling into text, which changes what the code asks Excel to do.
Workbook Scope, Worksheet Scope, and Active Sheets
Excel names can be workbook-scoped or limited to one worksheet. Workbook-scoped names are available throughout the workbook. A worksheet-scoped name is associated with a particular sheet, and another sheet can have a local name with the same spelling. This scope difference matters more than the quotation marks: the quotes tell VBA that the name is text, but they do not decide which definition Excel will use.
An unqualified call such as Range("TaxRate") is evaluated in the context of the active worksheet. If a macro activates another sheet, or if a local name conflicts with a workbook-level name, that can make the result surprising. For dependable code, qualify the worksheet or explicitly retrieve the workbook name.
- For a worksheet-scoped name on Inputs, use a reference such as
ThisWorkbook.Worksheets("Inputs").Range("TaxRate"). - For a workbook-scoped name, use
ThisWorkbook.Names("TaxRate").RefersToRange. - Use
ThisWorkbookwhen the name belongs to the workbook containing the macro. In code stored in another workbook or an add-in, it refers to that other file, not necessarily the workbook being edited.
When a name is local to a worksheet, referring to the intended worksheet directly also avoids relying on whichever sheet happens to be active. If a name might be missing, code should check for that condition and handle it rather than assuming every workbook contains the expected name.
Quotes in Formulas and Sheet References
There are two different layers that can contain quotes: VBA strings and Excel formulas. A formula assigned from VBA is itself text, so the formula goes inside VBA double quotes. The named range inside the formula generally does not need its own quotation marks. For example, VBA may pass the formula text "=SUM(MonthlySales)"; Excel interprets MonthlySales as the defined name when it calculates the formula.
Sheet names are a separate issue. In VBA, a worksheet reference commonly looks like Worksheets("Sales Data").Range("MonthlySales"). The worksheet name and the range name are both strings, so both are in double quotes. Spaces in the sheet name do not mean that the defined range itself should be wrapped in extra quotes. Excel defined names cannot contain spaces, so a name with spaces is usually a worksheet name, a table column header, or a text label rather than a valid defined name.
Do not confuse VBA’s double quotes with the apostrophes used around some sheet names in worksheet formula references, such as 'Sales Data'!A1. The apostrophes are part of Excel’s formula-reference notation for sheet names that need quoting. In VBA, it is usually clearer to qualify the worksheet object and then use its Range property.
Valid Names and Common Misconceptions
Adding quotes in VBA does not repair an invalid or missing defined name. Excel names have rules: for example, they cannot contain spaces and cannot be the same as a cell reference such as A1. Names are also not case-sensitive for ordinary references, so TaxRate and taxrate are treated as the same name. Before debugging VBA, confirm that the name exists and refers to the intended cells.
A frequent misconception is that the quote marks become part of the named range. They do not. In Range("TaxRate"), VBA removes the string delimiters when it evaluates the expression and passes the text TaxRate to Excel. Another misconception is that a named range must always refer to one cell. It can refer to a multi-cell area, and a name can also represent a formula or constant rather than a cell range.
Use the Name Manager to inspect names in Excel for Microsoft 365 on Windows: select Formulas > Name Manager. It shows each name, its scope, and its Refers to definition. To create a name, select the target cell or area and choose Formulas > Define Name. The ribbon paths are similar in Excel for Mac, though the window layout and keyboard shortcuts can differ. Excel for the web does not run VBA macros; use the desktop application to run this code.
Common Errors and How to Diagnose Them
If VBA highlights a name reference or reports that an object is not set, check both the VBA expression and the name’s definition. With Option Explicit enabled, an unquoted word such as Range(TaxRate) can produce a compile error if TaxRate has not been declared as a variable. Without that setting, VBA may treat an undeclared identifier as an empty Variant, which can lead to a different error or an unintended reference.
Other useful checks include:
- Confirm the name appears in Name Manager and is spelled exactly as expected.
- Check whether its scope is Workbook or a particular worksheet.
- Check the Refers to field to see whether it points to cells, a formula, or a constant.
- Qualify the workbook and worksheet instead of relying on the active workbook or active sheet.
- Look for a local name that takes precedence over a workbook-level name on the sheet being used.
If RefersToRange fails, the name may be valid but not refer to a range. If an object reference fails because a name does not exist, the fix is to create or correct the name, or handle its absence in the macro. Adding more quotation marks does not solve either problem.
Choosing the Right Reference Style
Use Range("Name") for a short, readable reference when the intended workbook and worksheet context are clear. Use a qualified worksheet reference when the name is local to a sheet or when the macro must work regardless of the active sheet. Use the Names collection when you want to state explicitly that you are looking up a workbook-level name or need to inspect a name’s definition.
For workbook automation, predictable context is generally more important than minimizing characters. A macro that runs while a different workbook is active can act on the wrong file if it relies on unqualified references. In contrast, ThisWorkbook.Names("TaxRate") clearly identifies the workbook containing the VBA project. If the code is intended to operate on whichever workbook a user selected, that should be a deliberate design choice rather than an accidental consequence of omitting qualification.
Frequently Asked Questions
Do named ranges need double quotes in VBA?
Usually, yes, when the name is typed directly as the argument to a reference such as Range("TaxRate"). The quotes mark the name as a text string. They are not part of the name itself.
Should I write Range(TaxRate) or Range(“TaxRate”)?
Use Range("TaxRate") for the literal defined name TaxRate. Use Range(TaxRate) only when TaxRate is a declared VBA variable or another valid expression that supplies the address or name.
Do I need quotes around a named range in an Excel formula?
No. A formula can refer to a defined name directly, such as =SUM(MonthlySales). If you assign that formula in VBA, the entire formula is text and therefore goes inside VBA double quotes.
Can a named range have spaces?
No. Excel defined names cannot contain spaces. If the text you are trying to reference contains spaces, check whether it is a worksheet name, a table header, or another kind of label instead.
Why does Range(“TaxRate”) refer to the wrong cells?
Check the active worksheet and whether a worksheet-scoped name conflicts with a workbook-scoped name. Qualify the intended worksheet or use the workbook’s Names collection to make the scope explicit.
Can Excel for the web run VBA?
No. Excel for the web can open workbooks containing macros, but VBA macros must be run in the Excel desktop application. Excel for Mac also supports VBA, though some interface details and macro capabilities can differ from Windows.
Related
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance 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 Copy and Paste a Range to Another WorksheetTo copy a range to another worksheet with Excel VBA, identify the source and destination worksheets, then use…
- VBA Function Arguments: Types, Defaults, and ExamplesVBA function arguments are the values or variables supplied when a function is called; the function’s parameter list…
- VBA Find Match: Search Excel Cells and Return ResultsIn Excel VBA, use the Range.Find method to search a range and return the first matching cell. For…
- Microsoft Excel VBA: What It Is and How to Use ItMicrosoft Excel VBA is the programming language built into the desktop versions of Excel that lets you automate…
- VBA Dictionary Class Modules: A Practical How-ToTo use a class module with a VBA Dictionary, define a class to represent one record, create an…
- VBA Delete an Entire Row Based on Cell Value in ExcelTo delete entire Excel rows when a cell matches a value, loop through the worksheet from the bottom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.