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

Micah_6209 0
Viewed 170064

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?

vba excel
11 Answers

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.

Ben_Hoffstein_44820

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, "]"))
theo_78700

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 Office website: https://support.office.com/en-us/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89

user_1138850

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

Admin1

[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, "=", "")
TimS_834700
Related