How to return name of range?

Status
Not open for further replies.

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
Suppose I have assigned the name "Average" to the range 12:12 (the entire row 12). If there an expression that I can put in a cell that will return that name?

I couldn't find one, so I wrote a UDF:
VBA Code:
Public Function RangeName(pRange As Range) As String

Dim Pieces() As String
Pieces = Split(pRange.Name.Name, "!")
RangeName = Pieces(1)

End Function
This code works if I call it with a literal argument
Code:
=RangeName(12:12)
but I couldn't figure out a way to call it with an argument that means "this row" -- "the row the calling cell is on". This is what I want, but it doesn't work
Code:
=RangeName(row():row())
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
First, I could not get your function to work. When the range name comes back without a sheet qualification, Pieces only has one element. I did get this to work:

Rich (BB code):
Public Function RangeName(pRange As Range) As String

   Dim Pieces() As String
   Pieces = Split(pRange.Name.Name, "!")
   RangeName = Pieces(UBound(Pieces))

End Function

Once I got the function to work, then I got this formula to work:

=RangeName(INDIRECT(ROW()&":"&ROW()))
 
Upvote 0
First, I could not get your function to work. When the range name comes back without a sheet qualification, Pieces only has one element. I did get this to work:

Rich (BB code):
Public Function RangeName(pRange As Range) As String

   Dim Pieces() As String
   Pieces = Split(pRange.Name.Name, "!")
   RangeName = Pieces(UBound(Pieces))

End Function
I didn't know it could come back without a sheet name. What determines whether there is one or not?

Once I got the function to work, then I got this formula to work:

=RangeName(INDIRECT(ROW()&":"&ROW()))
Pertfect. Thank you
 
Upvote 0
What test can I use on pRange to determine if the range has no name? That is, if the caller passes an unnamed range. The code as it is now get an error.
 
Upvote 0
I am not entirely sure how you plan to use this, but using this code...
VBA Code:
Function RangeName()
  RangeName = Application.Caller.EntireRow.Name.Name
End Function
if you put this formula in a cell...

=RangeName()

it will return the Defined Name containing the cell the formula is in, otherwise it will return a #VALUE! error if there is no Defined Name containing that cell. If you want to suppress the error, you can wrap the formula with an IFERROR function call.
 
Upvote 0
I am not entirely sure how you plan to use this,
I have a sheet with a number of named rows. The data in those rows is used in formulas in other rows. In one column, I display the formulas, which contain named ranges. So I added another column with the name of the row, if any, so I could easily what rows the formulas referred to.

but using this code...
VBA Code:
Function RangeName()
  RangeName = Application.Caller.EntireRow.Name.Name
End Function
if you put this formula in a cell...

=RangeName()

it will return the Defined Name containing the cell the formula is in, otherwise it will return a #VALUE! error if there is no Defined Name containing that cell. If you want to suppress the error, you can wrap the formula with an IFERROR function call.
That works. Thank you. But it means that I have to add that code to every call. One of the reasons for writing UDFs in the first place is to make the calls simple.

So, is there any test that I can do in the UDF before I execute the expression that will tell me if the passed range has a name or not?

I know I can trap the error, but I was hoping for a simple If statement.
 
Upvote 0
I wasn't exactly sure what you were looking for. Your original message sounded like you did not want to pass in a range to check (which would have meant you wanted to test the cell the formula was in, hence the UDF that I posted). Since your last message seems to indicate you do want to pass a range in to test, then I think this UDF will do what you want... it returns the Defined Name containing the range you pass into it if there is one, otherwise it returns the empty text string ("").
VBA Code:
Function RangeName(Rng As Range) As String
  On Error GoTo NoRangeName
  RangeName = Rng.EntireRow.Name.Name
NoRangeName:
End Function
 
Upvote 0
I wasn't exactly sure what you were looking for. Your original message sounded like you did not want to pass in a range to check (which would have meant you wanted to test the cell the formula was in, hence the UDF that I posted). Since your last message seems to indicate you do want to pass a range in to test, then I think this UDF will do what you want... it returns the Defined Name containing the range you pass into it if there is one, otherwise it returns the empty text string ("").
VBA Code:
Function RangeName(Rng As Range) As String
  On Error GoTo NoRangeName
  RangeName = Rng.EntireRow.Name.Name
NoRangeName:
End Function
It's way past my bedtime, so I'll post a couple of comments, then take this up tomorrow morning, in between serving as Santa's Helper. ?

1. I don't necessarily want the range to be an entire row. I do for this particular worksheet, but I'd like a more general function that can handle any range: row (12:12), column (B:B), rectangle (B4:D90), or part of a row (B10:E10) or column (G10:G25).

2. I will probably want to pass a third parameter, which will be the string to return if the range has no name (like the horse in the desert), like this:

VBA Code:
Function RangeName(Rng As Range, Optional NoNameStr as String = "n/a") As String
  RangeName = NoNameStr
  On Error GoTo Done
  RangeName = Rng.Name.Name
Done:
End Function

3. How does your code handle the situation where the name comes with a sheetname qualifier (Sheet1!RangeName)?

Nite-Nite :sleep:??
 
Upvote 0
You can just add an error handler to 6SJ's code:

VBA Code:
Public Function RangeName(pRange As Range, Optional ErrorMsg As String = "No name") As String
   Application.Volatile
   On Error GoTo err_handle
   Dim Pieces() As String
   Pieces = Split(pRange.Name.Name, "!")
   RangeName = Pieces(UBound(Pieces))
   Exit Function
err_handle:
   RangeName = ErrorMsg
End Function

I made it volatile so it would update if a referenced range has a name added.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,966
Messages
6,175,661
Members
452,666
Latest member
AllexDee

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