In Excel VBA, get a range’s address with its Address property: for example, Range("B2:D5").Address returns $B$2:$D$5 by default. Set the property’s optional arguments to choose absolute or relative references, A1 or R1C1 notation, and whether to include the worksheet and workbook.
What the Address property returns
Address returns a string describing the cells represented by a Range object. It reports the location of the range; it does not return the values stored in those cells. A single-cell range produces one cell reference, while a multi-cell range produces a reference spanning its first and last cells. For example, the default address for B2:D5 includes dollar signs before both the column letters and row numbers.
The method’s full argument order is Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo). All five arguments are optional. The defaults produce an absolute A1-style address without a workbook or worksheet prefix. You can pass arguments by position, but named arguments make the intent easier to read and reduce mistakes when you omit an argument in the middle.
Remember that a returned address is text. You can show it in a message box, write it to a cell, concatenate it into a larger string, or use it when constructing another reference. Getting an address is different from reading a value: use a range’s Value or Value2 property when you need its contents.
Choose absolute or relative references
The first two arguments control whether the row and column parts are absolute. By default, both are absolute, which is why the usual result contains dollar signs. Set both arguments to False to remove them in A1 notation. Set only one to False to create a mixed reference.
AddressorAddress(RowAbsolute:=True, ColumnAbsolute:=True)returns$B$2for a one-cell range at B2.Address(RowAbsolute:=False, ColumnAbsolute:=False)returnsB2.Address(RowAbsolute:=True, ColumnAbsolute:=False)returnsB$2.Address(RowAbsolute:=False, ColumnAbsolute:=True)returns$B2.
These are the same absolute and mixed-reference conventions used in Excel formulas. They are useful when VBA builds a formula or when you need to display a location in a particular form. If the address is only for identification or logging, the default absolute form is often the clearest because it does not depend on a separate starting cell.
Get an address in a VBA procedure
To use the property in a macro, first identify the Range object you want. Qualifying it with a workbook and worksheet avoids relying on whichever sheet happens to be active. The following procedure shows a specific range’s default address. Replace the worksheet name or cell reference to match your workbook.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when the macro only appears to display information; a later edit to the example could change cells or other workbook content.
Sub ShowRangeAddress()
Dim target As Range
' Identify the range on the intended worksheet.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2:D5")
' Display its default absolute A1 address.
MsgBox target.Address
End Sub
Here, ThisWorkbook means the workbook containing the VBA code, not necessarily the workbook currently selected in Excel. If the macro is stored in a different workbook, such as the Personal Macro Workbook, use an explicit workbook reference appropriate to the task. The worksheet name must also match an actual sheet tab name.
To place the result in a worksheet cell, assign the string returned by Address to that cell’s value. For example, with a target range already assigned to a Range variable, VBA can put its address in a separate output cell. Keep the source range and output cell distinct if you do not want to overwrite the data whose location you are reporting.
Use A1 and R1C1 reference styles
The ReferenceStyle argument determines how row and column positions are written. The default is xlA1, which uses column letters and row numbers, as in $C$7. Use xlR1C1 for row-and-column notation. With absolute settings, the same cell can be represented in R1C1 form as R7C3.
R1C1 notation can also express a location relative to another cell. When building a relative R1C1 address, provide RelativeTo explicitly so the reference point is clear. For example, C3 is two rows down and two columns to the right of A1, so its relative address from A1 is R[2]C[2].
Sub ShowRelativeR1C1Address()
Dim target As Range
Dim origin As Range
' Set the cell and the reference point on the same worksheet.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("C3")
Set origin = ThisWorkbook.Worksheets("Sheet1").Range("A1")
' Return an R1C1 address relative to A1.
MsgBox target.Address(RowAbsolute:=False, _
ColumnAbsolute:=False, _
ReferenceStyle:=xlR1C1, _
RelativeTo:=origin)
End Sub
In this example the relative offsets are positive because C3 is below and to the right of A1. If the target were above or to the left of the origin, the relative notation would use negative offsets. The RelativeTo argument is especially helpful in code that generates formulas or compares positions, because it makes the base cell unambiguous.
Include the worksheet or workbook name
Use the External argument when the result needs to identify its source sheet or workbook as well as the cell location. Set External:=True to request an external-style address. This is useful when a string will be passed between procedures or written to a log that includes references from more than one sheet or workbook.
The exact displayed prefix depends on the workbook and worksheet names. Names containing spaces or punctuation may be enclosed in apostrophes, and a workbook name may be included. For that reason, avoid assuming that every returned string will have the same number of characters or the same prefix. If you only need the address on a known worksheet, qualify the Range object itself in VBA and leave External at its default.
For instance, code can request an external-style address from a worksheet-qualified range as follows. This example demonstrates the property call; it does not change any cells.
Sub ShowExternalAddress()
Dim target As Range
' Identify the range before requesting its full address.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2:D5")
MsgBox target.Address(External:=True)
End Sub
Address a selection, cell, or multi-area range
The property works on any Range object, so the address can describe an active cell, a selected range, a named range resolved to cells, or a range stored in a variable. A common source of bugs is using an unqualified expression such as Range("A1") in code that may run while another worksheet is active. In general, attach the range to a specific worksheet when the intended location is known.
For a selection-dependent task, first verify that the user has selected cells before treating Selection as a Range. A user can select a chart, shape, or other object, and code that assumes every selection is a range may fail. If the macro is meant to work on one known cell or block, referencing that range directly is more reliable than relying on the current selection.
A range can also contain multiple separate areas, such as a selection created by Ctrl-clicking more than one block. Its address represents those areas as a multi-area reference. If your task requires processing each block independently, inspect the range’s Areas collection and handle each area separately rather than assuming the result is one continuous rectangle. Address punctuation can be affected by Excel’s list separator settings, so avoid using a returned multi-area string as a dependable way to split the areas.
Address versus AddressLocal and other alternatives
Use Address for the standard VBA address representation. Excel also provides AddressLocal, which returns the address using the language and regional conventions of the user’s Excel installation. This can matter when presenting an address to a local user or when working with localized formula syntax. If the string will be consumed by code that expects standard VBA reference notation, the ordinary Address property is generally the more appropriate choice.
Do not confuse an address with a range’s name or its value. A named range can refer to cells, but its name is not necessarily the same as the cells’ address. Similarly, Range("B2").Value reads the cell’s content, while Range("B2").Address returns its location. Choose based on what the next step needs: a cell reference, a displayed label, a defined name, or the data itself.
Common problems and practical checks
If the returned address does not look as expected, check the Range object and the arguments before changing the string afterward. Most surprises come from a default absolute reference, a different active worksheet than expected, or a mismatch between the requested reference style and the format being anticipated.
- Unexpected dollar signs: They are normal defaults. Set the row and column absolute arguments to False if you need a relative A1 address.
- Wrong sheet: Qualify the range with the intended workbook and worksheet instead of relying on an unqualified
Rangereference. - Unexpected R1C1 result: Confirm that
ReferenceStyle:=xlR1C1is passed and provideRelativeTowhen requesting a relative address. - Macro error on a selection: Confirm that the selected object is a range, or use a known worksheet range instead of
Selection. - Address text changes across workbooks: If
Externalis True, workbook and sheet names contribute to the output. Do not hard-code assumptions about the prefix.
It is also worth distinguishing a valid address string from a valid range in the current context. A string may refer to a different sheet or workbook, or to a location that is no longer the one your code intended. When possible, keep and pass Range objects within your procedure, and use the address chiefly where text is actually required, such as for display, logging, or constructing a reference.
Frequently Asked Questions
How do I get the address of the active cell in VBA?
Use the active cell’s Address property, such as ActiveCell.Address. For code that must work on a particular worksheet regardless of what the user has selected, reference that worksheet’s range instead.
How do I remove the dollar signs from a VBA address?
Set both absolute arguments to False: target.Address(RowAbsolute:=False, ColumnAbsolute:=False). For a B2 target, this returns B2 rather than $B$2.
How do I get the full address including the sheet name?
Request an external-style address with target.Address(External:=True). The result can include the worksheet and workbook, and its exact text depends on their names.
Does Address return a cell’s value?
No. It returns a string describing the range’s location. Read the cell’s Value or Value2 property when you need the contents.
Can Address return an R1C1 reference?
Yes. Set ReferenceStyle:=xlR1C1. For a relative R1C1 address, also provide the reference point with RelativeTo so the offsets are based on the cell you intend.
Related
- Balance of Payments: How It Works and What It Shows
- How to Split an Array in Java: Safe Ways to Do It Cleanly
- Balance of Payments Notes: Accounts, Examples and Meaning
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find Exact Match: Find Whole-Cell ValuesIn Excel VBA, set the LookAt argument of Range.Find to xlWhole to match an entire cell rather than…
- VBA Sort Range by Column: Practical Examples and TipsTo sort a range by one column in Excel VBA, call the range’s Sort method and specify the…
- Excel VBA Function vs Sub: Differences and ExamplesIn Excel VBA, a Function calculates and returns a value, while a Sub performs an action and does…
- VBA Sort Custom Order: Code Examples and Common Fixes in ExcelTo sort Excel rows in a chosen sequence with VBA, add a custom sort field and give it…
- Excel VBA Insert Image: Code and OptionsTo insert an image with Excel VBA, call the worksheet’s Shapes.AddPicture method, supplying the image file path and…
- Excel VBA: Loop Through Visible Rows in Filtered DataTo loop through only visible worksheet rows in Excel VBA, get a range of the data cells and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.