Why does Range.Address() return the address of the

Question

I stumbled across some unexpected behavior when writing an answer for this question.

When chaining together Range calls, Address always returns the address of the very first Range object in the statement. For example:

Public Sub Unexpected()

    Debug.Print Range("B3").Address
    Debug.Print Range("B3").Range("A1").Address

End Sub

Returns the following output.

$B$3
$B$3

But I would have expected it to return the Address of the last Range object in the chain.

$B$3
$A$1

Can anyone explain this behavior? Preferably with a quote of and link to the appropriate documentation.

Answer 1

There is documentation on using Range().Cells() which indicates that Cells() will return the location within the given Range() relative to the top left cell of the Range(). Testing this theory with Range().Range() gives:

Public Sub Unexpected()

    Debug.Print Range("B1:C3").Range("A1").Address
        '$B$1
    Debug.Print Range("B1:C3").Range("B1").Address
        '$C$1
End Sub

Documentation here:

When applied to a Range object, the property is relative to the Range object.

For example, if the selection is cell C3, then Selection.Range("B1") returns cell D3 because it’s relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.