Получение именованного диапазона по строке в книге Excel при дублировании имени

Когда мы (JKP и я) писали Name Manager, мы специально добавляли фильтр и предупреждающее сообщение для Duplicate. Глобальные / локальные имена, потому что такое поведение объектной модели Excel, о котором вы упоминаете, затрудняет обнаружение ошибок.

Поэтому я рекомендую никогда не использовать повторяющиеся глобальные / локальные имена.

Мы используем код, чтобы определить, если имя дублируется глобальное / локальное с активным родительским элементом локального имени, а затем при необходимости переключайте листы. Оптимизированный код VBA, который мы используем для поиска локальной версии глобального имени, таков: он достаточно быстрый, если у вас нет нескольких десятков тысяч имен -

    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