How do I get a range's address including the worksheet name, but not the workbook name, in Excel VBA?

Question

If I have a Range object--for example, let's say it refers to cell A1 on a worksheet called Book1. So I know that calling Address() will get me a simple local reference: "$A$1". I know it can also be called as Address(External:=True) to get a reference including the workbook name and worksheet name: "[Book1]Sheet1!$A$1".

What I want is to get an address including the sheet name, but not the book name. I really don't want to call Address(External:=True) and try to strip out the workbook name myself with string functions. Is there any call I can make on the range to get "Sheet1!$A$1"?

Answer 1

Only way I can think of is to concatenate the worksheet name with the cell reference, as follows:

Dim cell As Range
Dim cellAddress As String
Set cell = ThisWorkbook.Worksheets(1).Cells(1, 1)
cellAddress = cell.Parent.Name & "!" & cell.Address(External:=False)

Answer 2

Ben is right. I also can't think of any way to do this. I'd suggest either the method Ben recommends, or the following to strip the Workbook name off.

Dim cell As Range
Dim address As String
Set cell = Worksheets(1).Cells.Range("A1")
address = cell.address(External:=True)
address = Right(address, Len(address) - InStr(1, address, "]"))

Answer 3

[edit on 2009-04-21]

    As Micah pointed out, this only works when you have named that
    particular range (hence .Name anyone?) Yeah, oops!

[/edit]

A little late to the party, I know, but in case anyone else catches this in a google search (as I just did), you could also try the following:

Dim cell as Range
Dim address as String
Set cell = Sheet1.Range("A1")
address = cell.Name

This should return the full address, something like "=Sheet1!$A$1".

Assuming you don't want the equal sign, you can strip it off with a Replace function:

address = Replace(address, "=", "")

Answer 4

Why not just return the worksheet name with address = cell.Worksheet.Name then you can concatenate the address back on like this address = cell.Worksheet.Name & "!" & cell.Address

Answer 5

The Address() worksheet function does exactly that. As it's not available through Application.WorksheetFunction, I came up with a solution using the Evaluate() method.

This solution let Excel deals with spaces and other funny characters in the sheet name, which is a nice advantage over the previous answers.

Example: 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 Microsoft website: http://office.microsoft.com/en-us/excel/HP052089831033.aspx