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

 

[JenniferMurphy]

I have been working on this code on and off for several months. I now have a UDF that I think will return the name of passed ranges including those that it is part of. It will also differentiate between local and global names. It is about 100 lines of code.

Is anyone interested in it?

If so, should I post it here or in a new thread? I already mistakenly started two threads about this topic, so I am reluctant to start another one unless that is the best option. If I post it here, should I unmark the previous answer and wait for any comments on the new code? That would be one reason to start a new thread.

 

[MARK858]

Personally I would post it as an article as it isn't now a question

Excel Articles

Discussions about existing Excel Articles published in the forum. You can not start a new thread here. To contribute an article, click Excel Articles in the Top Nav Bar and then use the orange button on the right: Add Excel Article....

www.mrexcel.com

Instructions below

You Are Invited to Post Your Articles at MrExcel

The migration of the MrExcel Message Board to Xenforo has consumed the better part of 2019. But now that we have migrated, I would like to take advantage of the excellent editor that we have in the new software. I frequently meet a person or...

www.mrexcel.com

I would also report the duplicate thread using the report icon (bottom left of any post) explaining what happened and a Moderator will probably lock it.

Hopefully a Moderator will read this thread and offer some more official advice

 

[JenniferMurphy]

Personally I would post it as an article as it isn't now a question

I was hoping to get some feedback on the code. I have gotten such good feedback in the past. And there are 2-3 enhancements I plan to make when I get the time.

I would also report the duplicate thread using the report icon (bottom left of any post) explaining what happened and a Moderator will probably lock it.

Done

Hopefully a Moderator will read this thread and offer some more official advice

Ok

 

[MARK858]

I was hoping to get some feedback on the code.

Normally I would post it is the General discussion forum if wanting feedback on code, but in case I would wait for a Moderator to respond because of the multiple threads. Sorry I know that isn't much help.

 

[JenniferMurphy]

Normally I would post it is the General discussion forum if wanting feedback on code, but in case I would wait for a Moderator to respond because of the multiple threads. Sorry I know that isn't much help.

It's fine. I already messed up once, so I have no problem waiting for guidance from on high! I thought it would fit nicely here since it addresses the original question. The new code is kinda cool, if I do say so myself.

I thought the General Discussion section was for things other than Excel or Access.

 

[MARK858]

I thought the General Discussion section was for things other than Excel or Access.

It's for General discussion, including any questions about programs other than Excel / Access / Power BI / Power Query / Other Languages.
If it Excel related but not a question then that is the normal place to post it (which is why I stated to wait for a Moderator as I am not sure if it would be treated as a question or not, as you want the code reviewed then it probably is)