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.

Zanthoxylum

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? :) – Wil

you can also compare the parent.name with the workbook name, but you still need to loop through the names to extract that collection. – Stodge

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. – Stodge

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. – Zanthoxylum

@Stodge I'm trying to avoid iterating through all the names, as that is the costly piece. I realize I can use the parent property too. I believe that answer was here: #8657293 – Zanthoxylum

@Zanthoxylum It does matter what sheet you are on if you are setting a range object as per my sample code. – Wil

@Wil Oh, I see. You're getting the range associated by the name. However, that'll return the Range object, not the NamedRange object. You then run into the issue of extracting the NamedRange from a Range, which has its own problems. (Your vba syntax also threw me off, I should specify I'm working with C#) – Zanthoxylum

@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. – Dizon

@Shark, are you really sure you have a problem? Please verify that "wb" is set to the workbook and not the sheet. – Novellanovello

As far as I know, your workaround is the only way to address this. – Volute

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

Albinaalbinism

I'm not sure this answers my question. Maybe I'm misreading it, but I need to get the Global version of the name. This returns the sheet level one, which I can already do by passing it in the format of wb.Names.Item("Sheet1!myName"). I did notice one thing in your code that I'm curious about. Does comparing length before comparing strings cause much of a performance gain? – Zanthoxylum

We use this to detect if there is a duplicated global-local name by passing the global name and the active worksheet name. If there is a duplicate then you need to change worksheet and try again until you find a worksheet that does not contain a duplicate (or temporarily add a new sheet) so that then when you access the name you will get the global one. Getting the length of a string is very fast in VBA but comparing strings is slow: don't know to what extent this applies in C#. – Albinaalbinism

Ahh, makes sense now. I'll have to see if your approach (translated to C#) is faster than another idea I've come up with. I'll be posting the solution as another answer. Then performance testing will determine the winner! – Zanthoxylum

BTW if you are working in US english you should be able to skip the For Each loop. – Albinaalbinism