Can You Name a Range as a String in VBA? Explained

Yes. In VBA, a String variable can hold a range’s address, such as B2:B10, or the text name of a defined range, such as SalesData. You then pass that text to a worksheet’s Range property to get a Range object; the string itself is not a range and cannot be used as one until it is resolved.

A String Is Not a Range Object

This distinction is the key to understanding the question. A String stores text. A Range object represents cells in a workbook and exposes properties and methods such as Value, Address, ClearContents, and Copy. Storing B2:B10 in a String does not connect that variable to those cells. It only records the characters that describe them.

To work with those cells, use the string as an argument to Range and assign the result to an object variable with Set. For example, Set target = ws.Range(rangeText) turns valid range text into a Range object on worksheet ws. You can then read or change the cells through target.

That means VBA code commonly uses both kinds of variables: a String for a name or address that may change, and a Range for the actual cells. If the address is fixed and will never vary, you can refer to the range directly instead of storing its address as text.

How Range Text Is Resolved

A worksheet’s Range property accepts a cell reference or a defined name. The text must make sense in the context of the worksheet you specify. For example, ws.Range("B2:B10") refers to cells B2 through B10 on ws. If the string contains a defined name that Excel can resolve on that sheet, the same pattern can refer to the named cells.

Here is a small example that accepts either an address or a defined name, then displays the resolved range’s address. Before running any macro, save a copy of the workbook: macros cannot be undone with Ctrl+Z, even though this example only displays a message.

Sub ResolveRangeText()
    Dim ws As Worksheet
    Dim rangeText As String
    Dim target As Range

    ' Choose the worksheet and the address or name to resolve.
    Set ws = ThisWorkbook.Worksheets("Data")
    rangeText = "B2:B10"

    On Error GoTo InvalidRange
    Set target = ws.Range(rangeText)
    MsgBox target.Address(External:=True)
    Exit Sub

InvalidRange:
    MsgBox "Could not resolve the range: " & Err.Description, vbExclamation
End Sub

The example assumes the workbook contains a worksheet named Data. Change rangeText to a valid address or an applicable defined name. The error handler reports a problem if Excel cannot resolve the range text; it does not make an invalid name valid. The workbook and worksheet references are explicit, which helps avoid accidentally using a different workbook or whichever sheet happens to be active.

Using a Defined Name Stored as Text

Excel defined names are labels such as SalesData that refer to a cell, a range, a formula, or a constant. If a defined name refers to cells, you can store its name in a String and resolve it as above. For example, set rangeText to SalesData and use ws.Range(rangeText), provided that name is in scope for the worksheet.

For a workbook-scoped name, you can also retrieve the name through the workbook’s Names collection and ask for the range it refers to. The following version illustrates that route. It assumes that SalesData is a workbook-scoped name that refers to cells, not a formula or constant.

Sub GetWorkbookNamedRange()
    Dim target As Range

    ' Resolve a workbook-scoped name that refers to cells.
    Set target = ThisWorkbook.Names("SalesData").RefersToRange
    MsgBox target.Address(External:=True)
End Sub

RefersToRange is useful when you specifically want the cells behind a defined name. It will not return a Range for every possible defined name: names can also refer to formulas or constants. A missing name or a name that does not refer to a range can cause an error, so production code should validate the name or handle that failure.

Names can also be scoped to an individual worksheet rather than the whole workbook. If a workbook has worksheet-scoped names, make sure the code resolves the intended one; duplicate text can be ambiguous to a reader and can behave differently depending on the worksheet context. Using a worksheet variable and checking the name’s scope in Excel’s Name Manager makes such code easier to maintain.

Building an Address Dynamically

A common reason to store a range as a String is that its row or column changes while the macro runs. You can assemble address text from variables, then resolve it. This is helpful when the first and last rows depend on a calculation, an input, or the size of a data set.

Sub BuildRangeFromRows()
    Dim ws As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long
    Dim rangeText As String
    Dim target As Range

    ' Build a range address from row numbers.
    Set ws = ThisWorkbook.Worksheets("Data")
    firstRow = 2
    lastRow = 10
    rangeText = "B" & firstRow & ":B" & lastRow
    Set target = ws.Range(rangeText)

    MsgBox target.Address(External:=True)
End Sub

With these values, the constructed text is B2:B10. You could also build a multi-column address, such as A2:D10, by joining the relevant column letters and row numbers. If row numbers come from user input or calculations, check that they are positive and in the expected order before building the address.

For a purely numeric row-and-column calculation, Cells can sometimes be clearer than assembling A1-style text. For example, a range can be defined from two cell positions with ws.Range(ws.Cells(firstRow, 2), ws.Cells(lastRow, 2)). That approach avoids converting column numbers to letters. Both approaches can be appropriate: use a String when the address itself is variable input or a defined name, and use Cells when the row and column numbers are already available.

Qualify the Workbook and Worksheet

Unqualified references are a frequent source of VBA mistakes. A statement such as Range(rangeText) depends on the active worksheet. If the user activates another sheet before the macro runs, the same text can point somewhere else or fail. Using ws.Range(rangeText) ties the lookup to a worksheet variable instead.

Similarly, ThisWorkbook means the workbook containing the VBA code, while ActiveWorkbook means the workbook currently active in Excel. If the macro is stored in the workbook it should edit, ThisWorkbook is often the intended choice. If it is stored in a personal macro workbook or add-in and should operate on the user’s open workbook, the correct reference may differ. Choose deliberately rather than relying on whichever workbook is active.

A range address without a worksheet qualifier, such as B2:B10, does not identify a worksheet by itself. The workbook and worksheet context supplied by your code determine where Excel looks. A fully qualified external address can identify a workbook and sheet too, but embedding workbook paths in strings is fragile if files move or are renamed.

Common Problems and How to Avoid Them

• Confusing text with an object: A String cannot be used where a Range object is required. Resolve it first with Set target = ws.Range(rangeText).
• Using a misspelled or missing name: Excel cannot resolve a name that does not exist or is out of scope. Check the spelling and scope in Formulas > Name Manager.
• Assuming every defined name refers to cells: A name can refer to a formula or constant. RefersToRange is appropriate only when the name refers to a range.
• Depending on the active sheet: Prefer a worksheet-qualified expression such as ws.Range(rangeText) when the intended sheet is known.
• Constructing malformed text: A missing colon, an invalid row number, or a misplaced sheet qualifier can make the address invalid. Inspect the assembled String while debugging.
• Using an address from the wrong workbook: A simple address like B2 has no workbook identity. Resolve it against the intended worksheet and workbook.

When debugging, put a breakpoint on the line that calls Range and inspect rangeText in the Locals or Watch window. You can also display the text in the Immediate window. This separates two questions: whether the String contains the intended characters, and whether Excel can resolve those characters in the chosen worksheet context.

When to Use a String, a Range, or a Defined Name

Use a String when the range reference is data that may change: a user-selected name, an address read from a configuration cell, or an address assembled from row numbers. Use a Range object once you have resolved that text and need to inspect or manipulate the cells. Keeping the object in a variable avoids repeatedly resolving the same text and makes subsequent code easier to read.

Use a defined name when a range has a meaningful, stable role in the workbook, such as an input area or a report table. A name such as SalesData can make VBA and worksheet formulas easier to understand than a bare address, especially if the cells may move. However, a name is not automatically a String variable: it is a workbook feature that Excel stores and resolves. VBA can hold its label as text, then look up the named range.

For fixed code, a direct reference such as ws.Range("B2:B10") is perfectly valid and simpler. Add a String layer only when it provides flexibility or makes the source of the range clear. Avoid constructing text when a direct object reference or Cells-based expression communicates the intent better.

Frequently Asked Questions

Can a VBA String variable store a named range?
Yes. Assign the name’s text, such as SalesData, to a String, then resolve it with the appropriate worksheet’s Range property or the workbook’s Names collection. The name must exist and refer to cells if you expect a Range object.

Can I use Range(myString) directly?
Yes, if myString contains a valid address or a name Excel can resolve in that context. For predictable results, qualify it, for example ws.Range(myString). Use Set when assigning the result to an object variable.

What is the difference between a range name and a String?
A range name is an Excel defined name that refers to cells or another expression. A String is a VBA variable containing text. A String can contain the characters of a range name, but it does not create that name or refer to cells until your code resolves it.

Can a String store a range’s address?
Yes. It can hold text such as B2:B10 or an address returned by a Range object’s Address property. Pass that text to the intended worksheet’s Range property to get a Range object again.

Why does my range name work on one sheet but not another?
The name may be worksheet-scoped, or the code may be using the active worksheet rather than the one where the name applies. Check the name’s scope in Name Manager and qualify the worksheet explicitly when resolving it.