JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,687
- Office Version
- 365
- Platform
- Windows
Say B5 has been assigned the name "SalesDate". Is there a function (=getname(B5)) that will return the name ("SalesDate")?
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
Debug.Print NameName(Sheet1.Range("a1"))
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
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.
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.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.)
Yeah, sorry about that. I started this little project last year, but got interrupted. I forgot already had a thread about it.Also seems quite similar to this: How to return name of range?
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.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
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