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

[JenniferMurphy]

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.

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

rngname = rng.Names.Name
rngname = rng.Name

What is the correct code?

Thanks

 

[RoryA]

The original code works for me (other than not returning a value to the function).

 

[JenniferMurphy]

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.

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD]Range1 = "=Sheet1!$C$6:$E$7"
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]Range1 = []
[/TD]
[TD]E8: ="Range1 = [" & showrangename(Range1) & "]"[/TD]
[/TR]
</tbody>[/TABLE]

and here's the UDF:

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.

 

[Marcelo Branco]

Change this

rngname = [COLOR=#ff0000]Range(rng)[/COLOR].Name.Name

to

rngname = rng.Name.Name

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

M.

 

[JenniferMurphy]

Change this

rngname = [COLOR=#ff0000]Range(rng)[/COLOR].Name.Name

to

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.

 

[RoryA]

Really? It sure doesn't for me.

Since the code you're using wasn't the code you posted, it's not surprising the results might be different.

 

[Marcelo Branco]

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

I think that if omitted the function assumes that the range is on the same worksheet as the cell that called the function.

M.

 

[JenniferMurphy]

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?

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.

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:

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?

 

[RoryA]

Yes, it does. Why else would I say it?

 

[Marcelo Branco]

Anyway, does the original code, the first example above, really work for you?

No, the code with this code line
rngname = Range(rng).Name.Name
didn't work for me.

M.