Get named range by row in Excel workbook when name is duplicated

Question

To clarify, let's say I have two named ranges in my book. Both named ranges have the same name (for example, “myName”), but one is bound to Sheet1 and the other to a workbook.

Given the name (string) of the Named Range, I want to capture the level of the named range of the workbook.

If I use my own call: wb.Names.Item("myName") , it returns a range with the specified range.

If I do this instead: wb.Names.Item("Sheet1!myName") , this obviously returns a range of names in the sheet area. I found that I can use this to indicate specific pages, but not a book.

Is there anyway I can indicate that I want the workspace to be occupied?

My workaround is currently iterating over the list of all the names and comparing the .Name property to capture the area of ​​the Named Range workspace. This works because the .Name property adds "Sheet1!". to a range of name ranges. It is, however, very expensive, and I want to avoid it.

Answer 1

When we (JKP and I) wrote Name Manager , we specifically added a filter and warning message for Duplicate Global / Local Names, because this Excel behavior of the object model you are talking about makes it difficult to detect errors.

So my recommendation is never to use duplicate global / local names.

We use the code to determine if the name is a duplicate global / local with the active parent of the local name and then switch sheets if necessary. The optimized VBA code we use to find the local version of the global name is this: it is reasonably fast if you don't have tens of thousands of names -

  Function FindNameLocal(oSheet As Worksheet, sName As String) As Name Dim oName As Name Dim strLocalName As String Dim strLocalNameNoQuote Dim strName As String Set FindNameLocal = Nothing If Len(sName) > 0 And Not oSheet Is Nothing And oSheet.Names.Count > 0 Then On Error Resume Next strLocalName = "'" & oSheet.Name & "'!" & sName strLocalNameNoQuote = oSheet.Name & "!" & sName Set FindNameLocal = oSheet.Names(strLocalName) If Err <> 0 Or (FindNameLocal.NameLocal <> strLocalName And FindNameLocal.NameLocal <> strLocalNameNoQuote) Then On Error GoTo 0 Set FindNameLocal = Nothing For Each oName In oSheet.Names strName = oName.Name If Len(strLocalName) = Len(strName) Or Len(strLocalNameNoQuote) = Len(strName) Then If strName = strLocalName Or strName = strLocalNameNoQuote Then Set FindNameLocal = oName GoTo GoExit End If End If Next End If End If GoExit: End Function