VBA Range Address: Get, Set, and Understand References

In Excel VBA, use a range’s Address property to return its cell reference as text: for example, Range("B2:D5").Address returns $B$2:$D$5. You can control whether row and column markers are absolute, choose A1 or R1C1 notation, and optionally include the workbook and worksheet.

What the Range.Address property does

Address describes where a range is located; it does not return the range’s value or change the cells. Its result is a string, which is useful when building formulas, displaying a location, recording a selection, or passing a reference to another part of a macro. For example, a range object can refer to the cells themselves, while its address is text such as $B$2:$D$5.

In Excel for Microsoft 365 on Windows, the property is available on a Range object. You can call it on a single cell, a multi-cell range, or a range stored in a variable. Qualifying the range with a worksheet is a good habit: an unqualified expression such as Range("B2") refers to the active sheet, which may not be the sheet your code expects.

Address syntax and its arguments

The property’s arguments are optional. In VBA, its general form is someRange.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo). Named arguments make code easier to read and reduce mistakes caused by remembering the argument order.

RowAbsolute controls whether the row number has an absolute marker, such as the dollar sign in $2. It defaults to True.
ColumnAbsolute controls whether the column has an absolute marker, such as $B. It defaults to True.
ReferenceStyle selects A1 or R1C1 notation. The usual constants are xlA1 and xlR1C1; A1 is the default.
External determines whether the returned address includes workbook and worksheet information. It defaults to False.
RelativeTo supplies a reference point for relative R1C1 addresses. Specify it when you need predictable relative offsets.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. The snippets below are small procedures you can adapt; run them only after confirming the workbook and sheet names match your file.

Absolute and relative A1 addresses

By default, both row and column are absolute, so a single cell is returned with two dollar signs. For a range on a worksheet named Data, the following procedure prints the default address and then each of the common mixed-reference forms in the Immediate window.

Sub ShowAddressStyles()
    Dim ws As Worksheet
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set cell = ws.Range("B2")

    ' Print absolute and mixed A1 references in the Immediate window.
    Debug.Print cell.Address
    Debug.Print cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    Debug.Print cell.Address(RowAbsolute:=False, ColumnAbsolute:=True)
    Debug.Print cell.Address(RowAbsolute:=True, ColumnAbsolute:=False)
End Sub

For B2, the expected output is $B$2, B2, $B2, and B$2, in that order. These markers have the same meaning as in Excel formulas: when a formula is copied, a dollar-marked row or column stays fixed, while an unmarked part can adjust. A multi-cell range retains its colon separator; for example, the default address of B2:D5 is $B$2:$D$5.

A1 notation compared with R1C1 notation

A1 notation identifies a cell by column letter and row number, as in $B$2. R1C1 notation uses row and column numbers. With absolute settings, B2 is represented as R2C2. With relative settings, the R and C portions can show offsets from a reference cell in square brackets.

For example, if the reference point is A1 and the target is B2, the target is one row down and one column right, so the relative R1C1 address is R[1]C[1]. This can be useful when constructing formulas that should describe a position relative to another cell rather than rely on column letters. The following example explicitly sets the reference point instead of relying on an implicit default.

Sub ShowRelativeR1C1Address()
    Dim ws As Worksheet
    Dim target As Range
    Dim baseCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("B2")
    Set baseCell = ws.Range("A1")

    ' Report B2's position relative to A1.
    Debug.Print target.Address(RowAbsolute:=False, _
        ColumnAbsolute:=False, ReferenceStyle:=xlR1C1, _
        RelativeTo:=baseCell)
End Sub

With those cells, the expected result is R[1]C[1]. If you change the base cell, the offsets change. Supplying RelativeTo is especially helpful in shared code because it makes the intended origin clear to anyone reading the procedure.

Qualifying addresses with a worksheet or workbook

A plain address such as $B$2 does not identify which worksheet contains the cell. Use External:=True when the text must include workbook and worksheet context. The precise string can depend on the workbook’s name, location, and sheet name, so do not hard-code an assumed full path when your code needs to work with different files.

Sub ShowQualifiedAddress()
    Dim ws As Worksheet
    Dim cell As Range
    Dim addressText As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set cell = ws.Range("B2")

    ' Include workbook and worksheet context in the address text.
    addressText = cell.Address(External:=True)
    Debug.Print addressText
End Sub

The output identifies the workbook and Data sheet as well as the cell; a typical form includes a workbook name in square brackets and the sheet name before an exclamation mark. Worksheet names containing spaces or punctuation may be enclosed in apostrophes in the returned reference. Treat the result as address text, not as a guarantee that the workbook will be at the same file path later.

Using an address in a formula or message

Since Address returns text, a common use is to combine it with other text. For instance, a macro might report the address of the current selection, or assemble a formula that refers to a range. When doing this, distinguish between a reference string and a Range object: assigning an address string to a text variable does not itself select cells, read a value, or perform a calculation.

Here is a simple message example. It reports the range B2:D5 on the Data sheet without changing the selection.

Sub ReportRangeAddress()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim addressText As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("B2:D5")

    ' Use the range's address as display text.
    addressText = dataRange.Address
    MsgBox "The range is " & addressText
End Sub

The message text ends with $B$2:$D$5. If a formula needs a worksheet-qualified reference, use the external form where appropriate, or construct the formula with a clearly qualified range. Be mindful that formulas entered through VBA may require the correct formula property and syntax for the workbook’s language settings; an address string alone is not a complete formula.

Common mistakes and troubleshooting

Using the active sheet by accident: Range("B2") without a worksheet qualifier depends on which sheet is active. Prefer ws.Range("B2") after setting ws.
Expecting a value: Range("B2").Address returns a location. Use a cell’s Value property when you want its contents.
Confusing dollar signs with a changed cell: The dollar signs describe absolute-reference behavior in text. They do not lock, protect, or otherwise modify the worksheet cell.
Expecting a sheet name by default: The default result is generally just the cell or range address. Request external qualification when the workbook and sheet must be represented.
Misreading relative R1C1 output: Bracketed values are offsets from the chosen reference point, not worksheet row and column numbers. Check the RelativeTo range if the offsets seem unexpected.

For debugging, print the result with Debug.Print and inspect the Immediate window in the Visual Basic Editor. That makes it easy to verify the actual returned string before using it to build a larger expression.

Address versus other ways to identify a range

Address is the right choice when code needs a cell-reference string. If code needs to work with cells, however, it is often safer to keep a Range object rather than convert it to text and then parse or reconstruct it. A range object can be passed directly to procedures and used with properties such as Value or methods such as ClearContents.

Other properties answer different questions. Address gives a reference; Row and Column give numeric position information; and Value returns the cell contents. Choose based on what the next operation needs. An address string is convenient for display and formula construction, but it is not a substitute for the object when the code must manipulate a range.

Excel for the web can open and edit workbooks containing macros, but it cannot run VBA macros. Use desktop Excel to run this code. VBA is also available in desktop Excel for Mac, although interface steps and some capabilities can differ; the examples use standard VBA object-model syntax and are written for Excel on Windows.

Frequently Asked Questions

How do I get a cell address in VBA without dollar signs?
Set both absolute arguments to False, for example cell.Address(RowAbsolute:=False, ColumnAbsolute:=False). For B2, the result is B2.

How do I return the address of the selected cell?
Use Selection.Address when the selection is a range. For dependable code, first check that the selection is a range and remember that its address will be on the active worksheet.

How do I include the worksheet name in a VBA address?
Use the range’s Address property with External:=True. The returned text includes workbook and worksheet context; its exact formatting can depend on the workbook and sheet names.

What does Address(False, False) mean?
It requests an address with neither the row nor column marked absolute. In A1 notation, the dollar signs are omitted; in R1C1 notation, relative offsets can be shown in brackets when a reference point is supplied.

Does Range.Address select the cell?
No. It returns a string describing the range. To select cells, use a suitable range object’s Select method, although many macros can work directly with the range without selecting it.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.