Address() worksheet function does exactly that. As it’s not available through
Application.WorksheetFunction, I came up with a solution using the
This solution let Excel deals with spaces and other funny characters in the sheet name, which is a nice advantage over the previous answers.
Evaluate("ADDRESS(" & rng.Row & "," & rng.Column & ",1,1,""" & _ rng.Worksheet.Name & """)")
returns exactly “Sheet1!$A$1”, with a
Range object named
rng referring the A1 cell in the Sheet1 worksheet.
This solution returns only the address of the first cell of a range, not the address of the whole range (“Sheet1!$A$1” vs “Sheet1!$A$1:$B$2”). So I use it in a custom function:
Public Function AddressEx(rng As Range) As String Dim strTmp As String strTmp = Evaluate("ADDRESS(" & rng.Row & "," & _ rng.Column & ",1,1,""" & rng.Worksheet.Name & """)") If (rng.Count > 1) Then strTmp = strTmp & ":" & rng.Cells(rng.Count) _ .Address(RowAbsolute:=True, ColumnAbsolute:=True) End If AddressEx = strTmp End Function
The full documentation of the Address() worksheet function is available on the Office website: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89
Leave an answer