CODEWITHSUNDEEP
vbaexcel
Micah
Micah

Reputation: 1547

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

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?

Upvotes: 44

Views: 185192

Answers (13)

JoeM
JoeM

Reputation: 133

Admittedly DOES use string functions, but this handles many cases. Edited to handle multi-Area Ranges, per @HarveyFrench.

Function AddressWSheetName( _
    rg As Range, _
    Optional RowAbsolute As Boolean = True, _
    Optional ColumnAbsolute As Boolean = True _
) As String
    ' Returns the address including Sheet.Name for all Areas within rg.
    ' Encloses the Sheet.Name in single quotes when appropriate.
    
    Dim c           As String
    Dim strShtName  As String
    Dim strPart     As String
    Dim rgPart      As Range
    
    ' Get the proper form of the sheet name, bounded in single-quotes if needed.
    strShtName = rg.Worksheet.Name
    If (InStr(strShtName, " ") > 0) Then
        strShtName = "'" & strShtName & "'"
    End If
    
    For Each rgPart In rg.Areas
        strPart = rgPart.Address( _
            RowAbsolute:=RowAbsolute, _
            ColumnAbsolute:=ColumnAbsolute _
        )
        AddressWSheetName = AddressWSheetName & strShtName & "!" & strPart & ","
    Next rgPart
    
    ' Remove final area separator.
    AddressWSheetName = Left(AddressWSheetName, Len(AddressWSheetName) - 1)
    

End Function

Upvotes: 0

Rainier Rothmann
Rainier Rothmann

Reputation: 1

The best way I found to do this is to use the following code:

Dim SelectedCell As String

'This message Box allows you to select any cell on any sheet and it will return it in the format of =worksheetname!$A$X" where X is any number.

SelectedCell = Application.InputBox("Select a Cell on ANY sheet in your workbook", "Bookmark", Type:=8).Address(External:=True)

SelectedCell = "=" & "'" & Right(SelectedCell, Len(SelectedCell) - Len("[" & ActiveWorkbook.Name & "]") - 1)

'Be sure to modify Sheet1.Cells(1,1) with the Sheet and cell you want to use as the destination. I'd recommend using the Sheets VBA name.

Sheet1.Cells(1, 1).Value = SelectedCell

How it works;

By Clicking on the desired cell when the message box appears. The string from "Address(External:=True)" (i.e ['[Code Sheet.xlsb]Settings'!$A$1) is then modified to remove the full name of the worksheet([Code Sheet.xlsb]).

Using the previous example it does this by taking the "Len" of the full length of;

[Code Sheet.xlsb]Settings'!$A$1 and subtracts it with the Len of ([Code Sheet.xlsb] -1). leaving you with Settings'!$A$1.

 SelectedCell = "=" & "'" & Right(SelectedCell, Len(SelectedCell) - Len("[" & ActiveWorkbook.Name & "]") - 1)

The Code then its and "='" to insure that it will be seen as a Formula (='Settings'!$A$1).

Im not sure if it is only on Excel on IOS but for some reason you will get an Error Code if you add the "='" in any other way than "=" & "'" as seen bellow.

SelectedCell = "=" & "'" & Right....

From here all you need is to make the program in the Sheet and cell you want your new formula in.

Sheet1.Cells(1, 1).Value = SelectedCell

By Opening a new Workbook the full Code above will work as is.

This Code is Especially useful as changing the name of the workbook or the name of the sheet that you are selecting from in the message box will not result in bugs later on.

Thanks Everyone in the Forum before today I was not aware that External=True was a thing, it will make my coding a lot easier. Hope this can also help someone some day.

Upvotes: 0

ArnonK
ArnonK

Reputation: 1

rngYourRange.Address(,,,TRUE)

Shows External Address, Full Address

Upvotes: -1

raph82
raph82

Reputation: 117

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

Upvotes: 3

Harry S
Harry S

Reputation: 511

For confused old me a range

.Address(False, False, , True)

seems to give in format TheSheet!B4:K9

If it does not why the criteria .. avoid Str functons

will probably only take less a millisecond and use 153 already used electrons

about 0.3 Microsec

RaAdd=mid(RaAdd,instr(raadd,"]") +1)

or

'about 1.7 microsec

RaAdd= split(radd,"]")(1)

Upvotes: -1

HarveyFrench
HarveyFrench

Reputation: 4578

You may need to write code that handles a range with multiple areas, which this does:

Public Function GetAddressWithSheetname(Range As Range, Optional blnBuildAddressForNamedRangeValue As Boolean = False) As String

    Const Seperator As String = ","

    Dim WorksheetName As String
    Dim TheAddress As String
    Dim Areas As Areas
    Dim Area As Range

    WorksheetName = "'" & Range.Worksheet.Name & "'"

    For Each Area In Range.Areas
'           ='Sheet 1'!$H$8:$H$15,'Sheet 1'!$C$12:$J$12
        TheAddress = TheAddress & WorksheetName & "!" & Area.Address(External:=False) & Seperator

    Next Area

    GetAddressWithSheetname = Left(TheAddress, Len(TheAddress) - Len(Seperator))

    If blnBuildAddressForNamedRangeValue Then
        GetAddressWithSheetname = "=" & GetAddressWithSheetname
    End If

End Function

Upvotes: 0

Jeff
Jeff

Reputation: 1

I found the following worked for me in a user defined function I created. I concatenated the cell range reference and worksheet name as a string and then used in an Evaluate statement (I was using Evaluate on Sumproduct).

For example: 

Function SumRange(RangeName as range)   

Dim strCellRef, strSheetName, strRngName As String

strCellRef = RangeName.Address                 
strSheetName = RangeName.Worksheet.Name & "!" 
strRngName = strSheetName & strCellRef

Then refer to strRngName in the rest of your code.

Upvotes: -1

Luciano Evaristo Guerche
Luciano Evaristo Guerche

Reputation: 227

Split(cell.address(External:=True), "]")(1)

Upvotes: 21

Ben Hoffstein
Ben Hoffstein

Reputation: 103395

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.

Upvotes: 66

rinku
rinku

Reputation: 1

Dim rg As Range
Set rg = Range("A1:E10")
Dim i As Integer
For i = 1 To rg.Rows.Count

    For j = 1 To rg.Columns.Count
    rg.Cells(i, j).Value = rg.Cells(i, j).Address(False, False)

    Next
Next

Upvotes: -2

Aaron Storer
Aaron Storer

Reputation:

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

Upvotes: -1

TimS
TimS

Reputation: 247

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

Upvotes: -3

theo
theo

Reputation: 8921

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

Upvotes: 3

Related Questions