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) 

EDIT:

Modify last line to :

cellAddress = "'" & cell.Parent.Name & "'!" & cell.Address(External:=False)  

if you want it to work even if there are spaces or other funny characters in the sheet name.