Is there a way to obtain the name of a range?

[JenniferMurphy]

Suppose I want to show the names of various named ranges in a table. In this table, for example, I have assigned the name "Price" to Column C and "Quan" to Column D. I would like to put some expression, like "=RangeName(C:C)", into C6 and D6 that would show those names and would change if I renamed those ranges.

R/C	C	D	E	F
5	Unit Price	Quantity	Cost	Formula
6	(Price)	(Quan)	()	()
7	$9.98	250	$2,495.00	E7: =Price*Quan
8	$1.29	500	$645.00	E8: =Price*Quan
9	$5.49	130	$713.70	E9: =Price*Quan

<tbody>
</tbody>

If this does not exist, can I write a UDF that will do the job?

Thanks

 

[JenniferMurphy]

Without argument:

Function RangeName2()
    Dim pieces() As String
    On Error Resume Next
    RangeName2 = Columns(ActiveCell.Column).Name.Name
    pieces = Split(RangeName2, "!")
    If UBound(pieces) = 1 Then RangeName2 = pieces(1) Else RangeName2 = pieces(0)
End Function

This function appears to check if the calling cell is in a column that has been assigned a name. Correct?

I am looking for a function that I can pass a string parameter defining a range, like "C:C" (a column) or "5:5" (a row). But it could also be "C3:C25". I think it will almost always be an entire column, but I don't want to limit the function to that. I want the function to return the name that is assigned to that range or the null string ("") if no name is assigned.

Is that clearer?

 

[JenniferMurphy]

If Price & Quant are defined as RC3 and RC4, I don't think you're going to get there.

I don't understand what RC3 and RC4 are.

/QUOTE]In my example, you're asking the workbook or worksheet the name of a statically-defined range. When a UDF receives a range argument, it's already been resolved by whatever process Excel uses to evaluate arguments.[/QUOTE]
I don't want to pass it an actual range (a parameter of type range). I want to pass it a text string that is a valid range and have the UDF look it up.

This code seems to work:

Function RangeName(pRange As String) As String

Dim pieces() As String                'Array to hold the pieces of the range after splitting
On Error GoTo NoName                  'If no name is assigned to the range, return null
RangeName = Range(pRange).Name.Name   'Get the name of the range, if any
pieces = Split(RangeName, "!")        'Split off the sheet name
RangeName = pieces(1)                 'Return just the range name
Exit Function

NoName:
RangeName = ""                        'Return null if no name is assigned to the range
Exit Function
   
End Function

 

[JenniferMurphy]

OK. I may be over my head, but here's version 2.0. I realized that 99% of the time I will be working with a range that is an entire column and 99% or the remaining 1%, it will be an entire row. Since I will be passing something like "C:C" 99% of the time, I decided to let the UDF simply determine the column of the calling cell and use that. I added the pRowCol parameter for those 1% of cases where it's a row. This makes the calling syntax very simple.

Function RCName(Optional pRowCol As String = "COLUMN", _
                Optional pParenSw As Boolean = True) As String

'Application.Volatile
Dim pieces() As String                'Array to hold the pieces of the range after splitting
On Error GoTo NoName                  'If no name is assigned to the range, return null

Select Case UCase(pRowCol)            'Validity check the parameters
  Case "C", "COL", "COLUMN"             'If they want the row
    RCName = Columns(ActiveCell.Column).Name.Name 'Get the name assigned to the row
  Case "R", "ROW"                       'If they want the row
    RCName = Rows(ActiveCell.Row).Name.Name       'Get the name assigned to the row
  Case Else                             'Else, parameter error
    RCName = CVErr(xlErrValue)            'Return a Value error
    Exit Function
End Select

pieces = Split(RCName, "!")             'Split off the sheet name
If UBound(pieces) = 1 Then RCName = pieces(1) Else RCName = pieces(0)
GoTo Done:

NoName:                                 'No names assigned to the range
RCName = ""                            'Return null
   
Done:
If pParenSw Then                        'If they want parentheses,
  RCName = "(" & RCName & ")"       'Add them
End If

End Function

The only problem is that it doesn't work. It does work in that it returns the correct value, but Alt+Ctrl+Shift+F9 causes all of the results to change.

On this sheet, the name Price is assigned to D:D and Quan to E:E. Col F has no name assigned.

R/C	D	E	F	G
5	Unit Price	Quantity	Cost	Formula
6	(Price)	(Quan)	()	D6: =rcname()
7	$9.98	250	$2,495.00	F7: =Price*Quan
8	$1.29	500	$645.00	F8: =Price*Quan
9	$5.49	130	$713.70	F9: =Price*Quan

<tbody>
</tbody>

But if I press Atl+Ctrl+Shift+F9 to recalculate the entire sheet, I get this.

R/C	D	E	F	G
5	Unit Price	Quantity	Cost	Formula
6	()	()	()	D6: =rcname()
7	$9.98	250	$2,495.00	F7: =Price*Quan
8	$1.29	500	$645.00	F8: =Price*Quan
9	$5.49	130	$713.70	F9: =Price*Quan

<tbody>
</tbody>

Can someone tell me what's going on?

Is was even weirder when I added the Application.Volatile statement. Then executing and of the three calls, cause all of them to show the same result.

I need a nap.

 

[Tetra201]

Maybe just

Function RangeName(Rng As Range) As String
    Application.Volatile
    On Error Resume Next
    RangeName = Rng.Name.Name
End Function

Usage: =RangeName(C:C)

 

[DanteAmor]

How about. Range as argument

Function RangeName([COLOR=#0000ff]pRange As Range[/COLOR]) As String


    Dim pieces() As String                      'Array to hold the pieces of the range after splitting
    On Error Resume Next
    RangeName = ""                              'Return null if no name is assigned to the range
    RangeName = pRange.Name.Name                'Get the name of the range, if any
    pieces = Split(pRange.Name.Name, "!")       'Split off the sheet name
    If UBound(pieces) = 1 Then RangeName = pieces(1) Else RangeName = pieces(0)
       
End Function

Examples:

=RangeName(D:D)
=RangeName(C1:C14)
=RangeName(F11:F13)
=RangeName(12:12)

 

[JenniferMurphy]

Maybe just

Function RangeName(Rng As Range) As String
    Application.Volatile
    On Error Resume Next
    RangeName = Rng.Name.Name
End Function

Usage: =RangeName(C:C)

This appears to work, much the same as Dante's, but they both require that I pass a literal range. I'll reply in more detail to Dante.

 

[JenniferMurphy]

How about. Range as argument

Function RangeName([COLOR=#0000ff]pRange As Range[/COLOR]) As String


    Dim pieces() As String                      'Array to hold the pieces of the range after splitting
    On Error Resume Next
    RangeName = ""                              'Return null if no name is assigned to the range
    RangeName = pRange.Name.Name                'Get the name of the range, if any
    pieces = Split(pRange.Name.Name, "!")       'Split off the sheet name
    If UBound(pieces) = 1 Then RangeName = pieces(1) Else RangeName = pieces(0)
       
End Function

Examples:

=RangeName(D:D)
=RangeName(C1:C14)
=RangeName(F11:F13)
=RangeName(12:12)

This appears to work, much like the one from Tetra, but it requires me to pass a literal range. It will certainly do the job that I was looking to do, but after fiddling around with this for awhile, I have come to the realization that what I would really like is something more like the RCName function I posted above. The key feature is that it checks if the entire column (or row) of the calling cell has a name assigned to it. If so, it returns that name. Otherwise, it returns the null string.

In particular, I would like to know why it doesn't work.

Can either you or Tetra provide any insight there? What did I do wrong in that code?


Also, Tetra has an Application.Volatile statement in his. You do not. I think I understand what that statement does, but apparently I don't understand all of the implications. Any enlightenment there would also be appreciated.

Thanks

 

[Tetra201]

See if the following works for you:

Function RCName(Optional RorC As String = "C", Optional Wrap As Boolean = True) As String
    Application.Volatile
    On Error Resume Next
    Select Case Left(RorC, 1)
        Case "C", "c": RCName = Application.Caller.EntireColumn.Name.Name
        Case "R", "r": RCName = Application.Caller.EntireRow.Name.Name
    End Select
    If Wrap Then RCName = "(" & RCName & ")"
End Function

The "Application.Volatile" statement makes any name change visible immediately.

 

[DanteAmor]

This appears to work, much like the one from Tetra, but it requires me to pass a literal range. It will certainly do the job that I was looking to do, but after fiddling around with this for awhile, I have come to the realization that what I would really like is something more like the RCName function I posted above. The key feature is that it checks if the entire column (or row) of the calling cell has a name assigned to it. If so, it returns that name. Otherwise, it returns the null string.

In particular, I would like to know why it doesn't work.

Can either you or Tetra provide any insight there? What did I do wrong in that code?


Also, Tetra has an Application.Volatile statement in his. You do not. I think I understand what that statement does, but apparently I don't understand all of the implications. Any enlightenment there would also be appreciated.

Thanks

I thought you wanted something more practical and simple to use, that's the only thing with the range selection. But if it is a requirement to use string, then you must put the interpretation in the code.

 

[Rick Rothstein]

General observation about some of the code that has been posted...

1) When using Application.Caller, the cell it references is the cell the UDF formula is in (which may or may not be within the sought after named range).

2) You should not use ActiveCell within a UDF formula as I don't think it will report correctly.

3) Any cell or range of cells can be part of more than one Named range (although that is probably not the case for the OP's setup).

The following UDF is probably not going to be useful to the OP... what it does is take a single cell reference (a #REF ! error is returned if a multiple cell range is passed into it) and return the name of every Named range the cell is part of (as a comma delimited text string)...

Function CellName(Cell As Range) As Variant
  Dim Nm As Name
  If Cell.CountLarge > 1 Then
    CellName = CVErr(xlErrRef)
    Exit Function
  Else
    On Error Resume Next
    For Each Nm In Names
      If Not Intersect(Nm.RefersToRange, Cell) Is Nothing Then
        If Err.Number = 0 Then
          CellName = CellName & ", " & Nm.Name
        Else
          Err.Clear
        End If
      End If
    Next
    On Error GoTo 0
    CellName = Mid(CellName, 3)
  End If
End Function