Get a Named Range by String in Excel Workbook when Name is duplicated

Question

To elaborate, let's say I have two named ranges in my workbook. Both named ranges have the same Name (let's say "myName"), but one is scoped to Sheet1 and the other is scoped to the Workbook.

Given a name (string) of the Named Range, I want to grab the Workbook level Named Range.

If I use the native call: wb.Names.Item("myName"), it returns the sheet scoped named range.

If instead I do: wb.Names.Item("Sheet1!myName"), that obviously returns the sheet scoped name range. I've found I can use this to specify the sheet specific ones, but not a workbook one.

Is there anyway I can specify I want the workbook scoped one?

My workaround is currently iterating over the list of all the Names, and comparing the .Name property to grab the workbook scope Named Range. This works because the .Name property appends a "Sheet1!" to the sheet scoped Named Range. This is however very costly to do, and I want to avoid it.

Answer 1

When we (JKP and myself) were writing Name Manager we specifically added a filter and warning message for Duplicate Global/Local Names because this Excel object model behaviour you mention leads to hard to detect bugs.

So my recommendation is never to use duplicate Global/Local Names.

We use code to detect if a name is duplicate global/local with the parent of the local name active and then switch sheets if necessary. The optimised VBA code we use to find the local version of a global name is this: its reasonably fast unless you have several 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