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

User · Accepted Answer

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.

User · Answer

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    amp  rng Row  amp       amp  rng Column  amp    1 1      amp        rng Worksheet Name  amp           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    amp  rng Row  amp       amp            rng Column  amp    1 1      amp  rng Worksheet Name  amp              If  rng Count  gt  1  Then          strTmp   strTmp  amp       amp  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 · Answer

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

User · Answer

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        amp  Range Worksheet Name  amp           For Each Area In Range Areas               Sheet 1   H 8  H 15  Sheet 1   C 12  J 12         TheAddress   TheAddress  amp  WorksheetName  amp       amp  Area Address External  False   amp  Seperator      Next Area      GetAddressWithSheetname   Left TheAddress  Len TheAddress  - Len Seperator        If blnBuildAddressForNamedRangeValue Then         GetAddressWithSheetname        amp  GetAddressWithSheetname     End If  End Function

User · Answer

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

User · Answer

Split cell address External  True        1

User · Answer

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

User · Answer

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  amp       strRngName   strSheetName  amp  strCellRef           Then refer to strRngName in the rest of your code

User · Answer

edit on 2009-04-21    nbsp  nbsp  nbsp  nbsp As Micah pointed out  this only works when you have named that   nbsp  nbsp  nbsp  nbsp 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

User · Answer

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  amp       amp  cell Address

User · Answer

rngYourRange Address    TRUE    Shows External Address  Full Address

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

Examples related to vba

Examples related to excel