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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,680
Office Version
  1. 365
Platform
  1. Windows
Say B5 has been assigned the name "SalesDate". Is there a function (=getname(B5)) that will return the name ("SalesDate")?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)...
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
 
Upvote 0
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"):
VBA Code:
Debug.Print NameName(Sheet1.Range("a1"))
This issue happens with Names with workbook as well as with worksheet scope.

This worked for me :
VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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 !
 
Upvote 0
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.)
 
Upvote 0
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.
 
Upvote 0
This worked for me :
VBA Code:
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:
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.
 
Upvote 0

Forum statistics

Threads
1,223,576
Messages
6,173,153
Members
452,503
Latest member
AM74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top