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")?

 

[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

This works if the Rng has a name, but it gets a Value error if not.

GetRangeName.xlsx
	C	D	E
5	Name	Cell Address	Range Name
6	Name is: A	A	A
7	Name is: NextName	NextName	NextName
8	Name is:	#VALUE!	#VALUE!
Sheet1

Cell Formulas
Range	Formula
D6:D8	D6	=GetRangeName(C6)
E6	E6	=GetRangeName(A)
E7	E7	=GetRangeName(NextName)
E8	E8	=GetRangeName(C8)

Named Ranges
Name	Refers To	Cells
A	=Sheet1!$C$6	D6:E6
NextName	=Sheet1!$C$7	D7:E7