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.

It is not 100% clear to me how you are using the range. If you were trying to set a range object to a name string then Set rng1 = Range("myName") will return the range from the local sheet name if sheet1 is active else it will return the range from the workbook name. I think this is the cleanest workaround - ie test that the active sheet is different from the sheet hosting the local name before looking to use the name as the workbook scope. Make sense? :) — brettdj
you can also compare the parent.name with the workbook name, but you still need to loop through the names to extract that collection. — nutsch
re: @brettdj's comment. depending on what you're trying to achieve, the easiest might be to just add a temp sheet at the beginning of your macro, to be deleted at the end of your code. — nutsch
Nope, it doesn't matter what sheet you are on. It returns sheet level first (if multiple sheet scoped ones exist, it orders based on sheet ordering). If no sheet scoped ones exist, then it return the wb scoped one. And I'm not trying to set the range. I just want to get a hold of the COM object so I can do whatever I need to do to it. — Shark
@Shark, can you do a one-time analysis of all the names in the workbook, then save the workbook-scoped named ranges in a dictionary? It seems this type of caching would mitigate any performance issues. — devuxer

Charles Williams Charles Williams · Accepted Answer · 2012-10-10T08:14:54

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

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

1 Answers