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

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
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.

[TABLE="class: grid, width: 420"]
<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"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"](Price)[/TD]
[TD="align: center"](Quan)[/TD]
[TD="align: center"]()[/TD]
[TD="align: center"]()[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]$9.98[/TD]
[TD="align: center"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]E7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]$1.29[/TD]
[TD="align: center"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]E8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]$5.49[/TD]
[TD="align: center"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]E9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks
 
Sort by date Sort by votes
DanteAmor said:
Without argument:

Code: 
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
Click to expand...

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?
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
shg said:
If Price & Quant are defined as RC3 and RC4, I don't think you're going to get there.
Click to expand...
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:
Code: 
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
 
Upvote 0
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.

Code: 
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.

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"](Price)[/TD]
[TD="align: center"](Quan)[/TD]
[TD="align: center"]()[/TD]
[TD]D6: =rcname()[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]$9.98[/TD]
[TD="align: center"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]F7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]$1.29[/TD]
[TD="align: center"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]F8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]$5.49[/TD]
[TD="align: center"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]F9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]()[/TD]
[TD="align: center"]()[/TD]
[TD="align: center"]()[/TD]
[TD]D6: =rcname()[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]$9.98[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]F7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$1.29[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]F8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]$5.49[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]F9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
Maybe just
Code: 
Function RangeName(Rng As Range) As String
    Application.Volatile
    On Error Resume Next
    RangeName = Rng.Name.Name
End Function

Usage: =RangeName(C:C)
 
Upvote 0
How about. Range as argument

Code: 
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)
 
Upvote 0
Tetra201 said:
Maybe just
Code: 
Function RangeName(Rng As Range) As String
    Application.Volatile
    On Error Resume Next
    RangeName = Rng.Name.Name
End Function

Usage: =RangeName(C:C)
Click to expand...

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.
 
Upvote 0
DanteAmor said:
How about. Range as argument

Code: 
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)
Click to expand...

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. :confused:

Thanks
 
Upvote 0
See if the following works for you:
Code: 
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.
 
Upvote 0
JenniferMurphy said:
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. :confused:

Thanks
Click to expand...

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.
 
Upvote 0
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)...
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

JenniferMurphy
  • Solved
How to make sure that added table rows assume all characteristics of the rest of the table?
Replies
8
Views
459
JenniferMurphy
JenniferMurphy
JenniferMurphy
  • Question Question
How to update a specific cell in a table from VBA?
Replies
5
Views
4K
JenniferMurphy
JenniferMurphy
G
  • Question Question
Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?
Replies
2
Views
88
Peter_SSs
Peter_SSs
B
  • Question Question
Suggestions for Building a Historical Cost Database by Project
Replies
0
Views
53
babyjwhale
B
A
  • Solved
Extract the names of the Items purchased at different prices
Replies
2
Views
138
ala995
A

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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