0

By Worksheet.Range("Name"), I can get a named range.

I'm looking for the reverse operation: given a Range, check if it has a defined name.

Tried .Name but it gives an expression rather than the user-defined name:

? SheetDB.Range("VerPeriod").Address
$C$1
? SheetDB.Range("$C$1").Name
=БазаСИ!$C$1

The intent is: I'd like to get a value from a given row by a column's user-defined name rather than its location to improve code readability and maintainability. For that, I'd make every header cell a named range. This can be implemented with a class module, so it's possible in the set-up code to scan the header row and make a hash table {name: column number}. (Leaving aside how much warranted this optimization is, the question remains.)

Community
  • 1
  • 1
ivan_pozdeev
  • 33,874
  • 19
  • 107
  • 152
  • 2
    Why not just look through the `Names` collection to see if one of the named ranges' `RefersTo` refers to what you are looking at? But I'm not sure I understand why you are doing this - you want to take a known cell location, convert that to a named range, so that you can implicitly use the named range's RefersTo to get the address you started with? – YowE3K Aug 31 '17 at 22:53
  • @YowE3K see update – ivan_pozdeev Aug 31 '17 at 23:19
  • I'm still not sure I understand the need to work backward, especially if each column has a named range. I.e. if a column had a header of "XYZ" and you therefore had a named range called (perhaps) "Col_XYZ" referring just to the header, the column itself can be accessed as `Columns(Range("Col_XYZ").Column)` or the cell within that column on row `r` could be referred to by `Cells(r, Range("Col_XYZ").Column)`. So I just don't understand why you need to see that column (perhaps 5) has a header of "XYZ" in cell E1 so that you can find that the range name referring to cell E1 is "Col_XYZ". – YowE3K Aug 31 '17 at 23:33
  • @YowE3K "Excel doesn't have this facility because it's not needed" is an acceptable answer. I'm currently doing without it (using `Range(name).Column` each time) but wondering if it's possible anyway. – ivan_pozdeev Sep 01 '17 at 00:51
  • A `Range` object has no property which will return the named ranges it is associated with. If it did, it would need to be a Collection, because a single range can have multiple named ranges referring to it, and would also need to cater for whether the programmer was trying to get details of the named ranges which **exactly** matched the selected range, or which were an area that completely encompassed the selected range, or which were completely encompassed by the selected range, or which included any portion of the selected range. It probably got too difficult to implement. – YowE3K Sep 01 '17 at 01:48

1 Answers1

3

You were just a property away.

When you write SheetDB.Range("$C$1").Name, you are returned a Name object.

The property of the Name object that you want is actually the Name property of that Name object.

So:

SheetDB.Range("$C$1").Name.Name will give you "VerPeriod" as you want

MacroMarc
  • 3,214
  • 2
  • 11
  • 20