In a UDF, obtain the name of a passed named range

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
I am trying to obtain the name of a named range passed to a UDF. I want to use it in error messages.

Based on previous discussions and an Internet search, I came up with this, which doesn't work.
Code:
Public Function ShowRangeName(rng As Range)
Dim rngname As String
rngname = rng.Name.Name
MsgBox "The name of this range is '" & rngname & "'"
End Function

I also tried
Code:
rngname = rng.Names.Name
rngname = rng.Name

What is the correct code?

Thanks
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The original code works for me (other than not returning a value to the function).
 
Upvote 0
The original code works for me (other than not returning a value to the function).
Really? It sure doesn't for me.

Here's the sheet that calls the UDF. I defined "Range1" to be $C$6:$E$7 as shown in F6. The UDF call is in E8 as shows in the formula in F8.

R/CCDE
F
6abcRange1 = "=Sheet1!$C$6:$E$7"
7xyz
8Range1 = []
E8: ="Range1 = [" & showrangename(Range1) & "]"

<tbody>
</tbody>

and here's the UDF:
Code:
Public Function ShowRangeName(rng As Range) As String
Dim rngname

On Error Resume Next
rngname = Range(rng).Name.Name
On Error GoTo 0
If IsEmpty(rngname) Then
   MsgBox "rngname is empty"
Else
   MsgBox "The name of this range is '" & rngname & "'"
End If

ShowRangeName = rngname
End Function

The result of "Range(rng).Name.Name" is Empty.
 
Upvote 0
Change this
Code:
rngname = [COLOR=#ff0000]Range(rng)[/COLOR].Name.Name

to
Code:
rngname = rng.Name.Name

In E8 try
="RangeName = [" & showrangename(Sheet1!C6:E7) & "]"

M.
 
Upvote 0
Change this
Code:
rngname = [COLOR=#ff0000]Range(rng)[/COLOR].Name.Name

to
Code:
rngname = rng.Name.Name
That works!

Is there a way to get just the range name without the sheet name or do I need to do a Split on the "!"?

Did my other code work for you? Shg said it worked for him.
 
Upvote 0
Since the code you're using wasn't the code you posted, it's not surprising the results might be different. ;)
This is the original code that I posted that you said worked for you. This exact code did not work for me. Does it still work for you?

Code:
Public Function ShowRangeName(rng As Range)
Dim rngname As String
rngname = rng.Name.Name
MsgBox "The name of this range is '" & rngname & "'"
End Function

In the process of trying to explain the situation better, I created a clean sheet with a named range. At the same time, I had been experimenting with other expressions to get the range name. This is that code, which is different from the previous version. Neither one works for me.

Code:
Public Function ShowRangeName(rng As Range) As String
Dim rngname

On Error Resume Next
rngname = Range(rng).Name.Name
On Error GoTo 0
If IsEmpty(rngname) Then
   MsgBox "rngname is empty"
Else
   MsgBox "The name of this range is '" & rngname & "'"
End If

ShowRangeName = rngname
End Function

Now after reading the suggestion from Marcelo, the code is:

Code:
Public Function ShowRangeName(rng As Range) As String

Dim rngname
On Error Resume Next
rngname = rng.Name.Name
On Error GoTo 0
If IsEmpty(rngname) Then
  MsgBox "rngname is empty"
Else
  MsgBox "The name of this range is '" & rngname & "'"
End If

ShowRangeName = rngname
End Function

This code works. The only minor glitch is that it includes the sheet name, which I can remove.

Anyway, does the original code, the first example above, really work for you? ;);)
 
Upvote 0
Yes, it does. Why else would I say it?
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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