Is there a function that will return the name of a range?

[JenniferMurphy]

Say B5 has been assigned the name "SalesDate". Is there a function (=getname(B5)) that will return the name ("SalesDate")?

 

[Rick Rothstein]

I guess this VBA function (can be used as a UDF on a worksheet as well as be called from other VBA code) will work, simply pass it the cell reference (B5 if called from a formula, Range("B5") if called from other VBA code)...

Function NameName(Rng As Range) As String
  Dim N As Name
  For Each N In Names
    If Rng.Address = N.RefersToRange.Address Then
      NameName = N.Name
      Exit Function
    End If
  Next
End Function

 

[Jaafar Tribak]

Hi Rick,

Any particular reason for iterating the names ?

BTW, your function didn't work for me and gave me some weird string after adding a On Error Resume Next.

This is the string what the function returned "_xlfn.UNICHAR" when I ran the function on Range("a1"):

Debug.Print NameName(Sheet1.Range("a1"))

This issue happens with Names with workbook as well as with worksheet scope.

This worked for me :

Function GetRangeName(ByVal Rng As Range, Optional ByVal FullName As Boolean = False) As String

    Dim sName As String
    On Error Resume Next
    
    sName = Rng.Name.Name
    GetRangeName = IIf(FullName, sName, Right(sName, Len(sName) - InStrRev(sName, "!")))

End Function

 

[RoryA]

I guess it depends on whether you want the ability to return the name of a range that the specified cell might be part of. If you do, you need a loop, and you need to handle the hidden xlfn names that are created when using functions introduced from 2007 onwards.

 

[RoryA]

Also seems quite similar to this: How to return name of range?

 

[Jaafar Tribak]

I guess it depends on whether you want the ability to return the name of a range that the specified cell might be part of. If you do, you need a loop, and you need to handle the hidden xlfn names that are created when using functions introduced from 2007 onwards.

Thanks.

I wasn't aware of these hidden xlfn names ... I have just looked it up and it says :
"The Excel _xlfn. prefix indicates that your workbook contains a function that is not supported by your version of Excel "
But this happens in a blank workbook with no formulas !

 

[RoryA]

That is weird. They should only be created if you used a 'new' formula. (Note: if you used one then deleted it, that would not clear the name as far as I know.)

 

[Jaafar Tribak]

That is weird. They should only be created if you used a 'new' formula. (Note: if you used one then deleted it, that would not clear the name as far as I know.)

I did a re-test in a new blank workbook and now I no longer get that _xlfn prefix ! ... I must have done something in my previous test without knowing it.

Thanks.

 

[JenniferMurphy]

Also seems quite similar to this: How to return name of range?

Yeah, sorry about that. I started this little project last year, but got interrupted. I forgot already had a thread about it.

 

[JenniferMurphy]

This worked for me :

Function GetRangeName(ByVal Rng As Range, Optional ByVal FullName As Boolean = False) As String

    Dim sName As String
    On Error Resume Next
   
    sName = Rng.Name.Name
    GetRangeName = IIf(FullName, sName, Right(sName, Len(sName) - InStrRev(sName, "!")))

End Function

Based on the suggestions here and in the other thread, I came up with something similar, which seems to work. I just want the name if the cell is the only thing in the range.

Here's my code:

Function GetRangeName(pRange As Range) As String
Application.Volatile                        'Needed so it will update with any changes
Dim pieces() As String                      'Array to hold the pieces of the range after splitting
On Error Resume Next
GetRangeName = ""                              'Return null if no name is assigned to the range
GetRangeName = pRange.Name.Name                'Get the name of the range, if any
If GetRangeName = "" Then
    GetRangeName = "No names found"
    Exit Function
End If
pieces = Split(pRange.Name.Name, "!")       'Split off the sheet name
If UBound(pieces) = 1 Then GetRangeName = pieces(1) Else GetRangeName = pieces(0)
       
End Function

Thanks for the help and sorry for the duplicate thread.