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

 

[shg]

I expect it only works for statically-defined ranges -- not dynamic (or relative) ranges.

 

[Marcelo Branco]

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.

It includes the sheet name only when the scope of the range is a worksheet, not Workbook.

M.

 

[JenniferMurphy]

I expect it only works for statically-defined ranges -- not dynamic (or relative) ranges.

Yep, that's it. Here's my code:

Public Function RngName(rng As Range) As String
Dim rname As String
On Error Resume Next
rname = rng.Name.Name          'Works for absolute ranges
'rname = Range(rng).Name.Name   'Doesn't work for any ranges
On Error GoTo 0
RngName = "[" & rname & "]"
End Function

Here's a table from a test sheet. Three ranges are named as shown. Range1 & Range2 are absolute. RangeR has absolute columns, but relative rows. The code above returns the results in F9:F12 using the expressions shown in G9:G12.

[TABLE="class: grid, width: 720"]
<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]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/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$6"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]z[/TD]
[TD]Range2 = "=Sheet1!$C$7:$E$7"[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD]RangeN = "=Sheet1!$C7:$E7"[/TD]
[TD="align: center"]Formulas[/TD]
[TD="align: center"]Objective[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][Sheet1!Range1][/TD]
[TD]F9: =rngname(Range1)[/TD]
[TD][Range1][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][Sheet1!Range2][/TD]
[TD]F10: =rngname(Range2)[/TD]
[TD][Range1][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][][/TD]
[TD]F11: =rngname(RangeR)[/TD]
[TD][RangeR][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][][/TD]
[TD]F12: =rngname(RangeR)[/TD]
[TD][RangeR][/TD]
[/TR]
</tbody>[/TABLE]

Is there an expression that I can use in VBA that will return the values in H9:H12?

Sorry for the confusion earlier. I'm struggling to get my head around ranges and Excel's range notation.

 

[Marcelo Branco]

See if this does what you need

Function GetRngName(r As Range) As String
    Dim n As Name, sFor As String, pos As Long, str2 As String
    
    sFor = "=" & r.Parent.Name & "!" & r.Address
    For Each n In ThisWorkbook.Names
        pos = InStr(Application.ConvertFormula(n.RefersTo, xlA1, xlA1, xlAbsolute), "]")
        str2 = "=" & Replace(Mid(Application.ConvertFormula(n.RefersTo, xlA1, xlA1, xlAbsolute), pos + 1), "'", "")
        If sFor = str2 Then
            GetRngName = "[" & n.Name & "]"
            Exit Function
        End If
    Next n
End Function

Sheet1

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

a​

[/td][td]

b​

[/td][td]

c​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

d​

[/td][td]

e​

[/td][td]

f​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td]

[Range1]​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td]

[Range2]​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td]

[RangeR]​

[/td][/tr]
[/table]

Range1 = $C$6:$E$6
Range2 = $C$7:$E$7
RangeR = $C8:$E8 (relative row)

F9
=GetRngName(C6:E6)

F10
=GetRngName(C7:E7)

F11
=GetRngName(C11:E11)
Since the range has been defined with relative row, you must enter the funtion's argument this way, that is: Formula in row x use Cx:Ex

M.

 

[Marcelo Branco]

EDIT
Range1 = Sheet1!$C$6:$E$6
Range2 = Sheet1!$C$7:$E$7
RangeR = Sheet1!$C8:$E8 (relative row)

M.

 

[JenniferMurphy]

See if this does what you need

I think I did exactly what you suggest. For me, the function exits on the first execution of the For statement before any iterations.

Is this function searching all named ranges in the entire workbook for an exact match on the passed range? If so, it's probably not what I need. I only want to check the calling sheet.

But there must be a simpler way.

This code works for all absolute ranges ($C$R:$C$R). It just doesn't work for relative ranges ($CR:$CR).

Public Function RngName(rng As Range) As Variant
RngName = rng.Name.Name
End Function

Isn't there some similarly simple statement that will work for all ranges?

 

[Marcelo Branco]

I think I did exactly what you suggest. For me, the function exits on the first execution of the For statement before any iterations.

I tested many times and the function worked perfectly.
I don't know why is not working for you

M.

 

[RoryA]

A relative range is a named formula, not a named range per se.