2

I have a cell that has more than one named ranges associated with it. How do I get it to return a specific name without having to loop through all the names in the workbook?

Sub Test()
    ActiveWorkbook.Names.Add Name:="AUserDefinedName1", RefersTo:="='Sheet1'!$A$1", Visible:=True
    ActiveWorkbook.Names.Add Name:="AUserDefinedName2", RefersTo:="='Sheet1'!$A$1", Visible:=True
    ActiveWorkbook.Names.Add Name:="MyName", RefersTo:="='Sheet1'!$A$1", Visible:=True
    Dim n As Name
    Set n = Range("A1").Name
    Debug.Print n.Name
End Sub

If you run the above, for cell A1 it would only return the first name. However, how would I get it to return the other names associated with cell A1 as well or a specifically return 'MyName'?

I could loop through all names in the workbook and see if the 'RefersTo' property of the Name object matches the address for cell A1, but I would like a more direct solution if possible that's faster.

This thread gave a partial answer, but need what if there are more than one name? How do you get a Range to return its Name?

Community
  • 1
  • 1
ptownbro
  • 1,240
  • 3
  • 26
  • 44
  • 1
    The real question is - why do you have 3 names for the same range!? – SierraOscar Feb 05 '16 at 23:11
  • 1
    @MacroMan obviously so OP can ask a question on SO duh. – findwindow Feb 05 '16 at 23:14
  • Since you asked.... One of the names would be hidden and storing information used by code. However, what if the user also adds a name (or two) to that same range where the code also placed a name. – ptownbro Feb 06 '16 at 01:20
  • For your information, here's a shorter way of creating names: `Range("Sheet1!A1").Name = "MyName"` – JohnyL Dec 18 '18 at 19:40

2 Answers2

1

Use the Workbook.Names collection and test the address:

For Each n In ActiveWorkbook.Names
    If n.RefersToRange.Address = "$A$1" Then
        Debug.Print n.Name
    End If
Next

There's a whole MSDN article about named ranges too for further reading.

There isn't a Names collection for the range object so the only way to accomplish this is at workbook level. Unless you have thousands of named ranges in your workbook there will be no noticeable speed difference in using this method, just create a UDF to get the names that you need.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • Thanks for you response. Yes. If you read the last part of my original post I mentioned I understood I could loop through the names (just as you suggested). However, I'm looking to see if there is a more direct solution. – ptownbro Feb 06 '16 at 01:27
1

WB as Workbook RG as Range

WB.Names(RefersTo:= "=" & RG.Worksheet.Name & "!" & RG.Address(True, True)).Name

Realize this is an antiquated post, but for anyone else looking like I was today, the above worked for me.

David Williams
  • 11
  • 1