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
True to return the row part of the reference as an absolute reference. The default value is True.
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
True to return an external reference. False to return a local reference. The default value is False.
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:

  1. 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 
  1. 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 
  1. 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 
  1. 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 
  1. 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 

Leave a Reply

Your email address will not be published. Required fields are marked *