How to use VBA Range.Address method?
Range.Address is used to get the cell address for simple local reference (ex. $A$1) or reference style notation for cell references (ex. A1 or R1C1 format). It can also be used to get the range address which includes the workbook name and worksheet name.
Syntax
expression .Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
expression A variable that represents a Range object.
Parameters
| Name | Required/Optional | Data Type | Description | ||
| RowAbsolute | Optional | Variant |
|
||
| ColumnAbsolute | Optional | Variant | True to return the column part of the reference as an absolute reference. The default value is True. | ||
| ReferenceStyle | Optional | XlReferenceStyle | The reference style. The default value is xlA1. | ||
| External | Optional | Variant |
|
||
| RelativeTo | Optional | Variant | If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point. |
Below are the different sample codes:
- To simply display the local reference of the cell address
Sub Range_Address1() Set MyRange = Worksheets("021817").Cells(1, 1) MsgBox MyRange.Address() ' $A$1 End Sub
- To simply display the absolute address of the cell
Sub Range_Address2() Set MyRange = Worksheets("021817").Cells(1, 1) MsgBox MyRange.Address(RowAbsolute:=False) ' $A1 End Sub
- To display the reference style of the cell address. This simply means using the combination of both column letter and row number.
Sub Range_Address3() Set MyRange = Worksheets("021817").Cells(1, 1) MsgBox MyRange.Address(ReferenceStyle:=xlR1C1) ' R1C1 End Sub
- To display the cell address including workbook and worksheet name
Sub Range_Address4() Set MyRange = Worksheets("021817").Cells(1, 1) MsgBox MyRange.Address(External:=True) 'Address includes workbook and worksheet name End Sub
- Using the combined parameters
Sub Range_Address5() Set MyRange = Worksheets("021817").Cells(1, 1) MsgBox MyRange.Address(ReferenceStyle:=xlR1C1, _ RowAbsolute:=False, _ ColumnAbsolute:=False, _ RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2] End Sub