How to return name of range?

[JenniferMurphy]

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:

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

=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

=RangeName(row():row())

 

[6StringJazzer]

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:

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()))

 

[JenniferMurphy]

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:

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

 

[JenniferMurphy]

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.

 

[Rick Rothstein]

I am not entirely sure how you plan to use this, but using this 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.

 

[JenniferMurphy]

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

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.

 

[Rick Rothstein]

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 ("").

Function RangeName(Rng As Range) As String
  On Error GoTo NoRangeName
  RangeName = Rng.EntireRow.Name.Name
NoRangeName:
End Function

 

[JenniferMurphy]

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 ("").

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:

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

 

[RoryA]

You can just add an error handler to 6SJ's 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.