VBA bug in deleting a Name if there are two Names with the same name property but differing scope

[ttt123]

Names can either have workbook scope of worksheet scope. Excel allows you to create multiple names with the same Name property if they have different scope.

So for example, I could have two names called "test" where one has workbook scope and one has worksheet scope. The problem is that if I have multiple names with the same name property and try to delete one of them, sometimes the wrong name is deleted.

Please see the example below to replicate the issue.

This appears to be a bug in the Excel object model. Does anyone know how to delete the correct name in this situation?

Sub NameDeletionWithScope()
    Dim workbookName As Name, worksheetName As Name
    Dim sWorkbookName As String, sWorksheetName As String, sRemainingName As String
    
    'Delete all existing names for fresh demo
    For i = ThisWorkbook.Names.Count To 1 Step -1
        ThisWorkbook.Names(i).Delete
    Next
    
    Set workbookName = ThisWorkbook.Names.Add(Name:="test", RefersToR1C1:="=Sheet1!R1C1")
    Set worksheetName = ThisWorkbook.Worksheets(1).Names.Add(Name:="test", RefersToR1C1:="=Sheet1!R1C1")
    sWorkbookName = workbookName.Name ' workbook scope name "test"
    sWorksheetName = worksheetName.Name ' worksheet scope name "Sheet1!test"
    
    workbookName.Delete 'This should delete workbookName but instead deletes worksheetName
    
    sRemainingName = ThisWorkbook.Names.Item(1).Name 'This should be "Sheet1!test" but is instead "test"
    
    If sRemainingName = sWorkbookName Then
        MsgBox "The name that was deleted had worksheet scope when it should have workbook scope"
    End If
End Sub

 

[Alex Blakenburg]

The only way I have been able to delete it is using the following:

    Dim aName As Variant
    For Each aName In Names
        If aName.Name = sWorkbookName Then workbookName.Delete
    Next aName

OR

    Dim aName As Variant
    For Each aName In Names
        If aName.Name = sWorkbookName Then aName.Delete
    Next aName

 

[ttt123]

Hi Alex,

Thanks for your reply. I tried your suggestion but still get the same error. Please see updated sample below:

Sub NameDeletionWithScope()
    Dim workbookName As name, worksheetName As name
    Dim sWorkbookName As String, sWorksheetName As String, sRemainingName As String
    
    'Delete all existing names for fresh sample
    For i = ThisWorkbook.Names.Count To 1 Step -1
        ThisWorkbook.Names(i).Delete
    Next
    
    Set workbookName = ThisWorkbook.Names.Add(name:="test", RefersToR1C1:="=Sheet1!R1C1")
    Set worksheetName = ThisWorkbook.Worksheets(1).Names.Add(name:="test", RefersToR1C1:="=Sheet1!R1C1")
    sWorkbookName = workbookName.name ' "test"
    sWorksheetName = worksheetName.name ' "Sheet1!test"
    
    'workbookName.Delete 'This should delete workbookName but instead deletes worksheetName
           
    Dim aName As Variant
    For Each aName In Names
        If aName.name = sWorkbookName Then aName.Delete
    Next aName
    
    sRemainingName = ThisWorkbook.Names.Item(1).name 'This should be "Sheet1!test" but is instead "test"
    
    If sRemainingName = sWorkbookName Then
        MsgBox "The name that was deleted had worksheet scope when it should have workbook scope"
    End If
End Sub

 

[Alex Blakenburg]

Well that wasn't as easy as it might appear.
If you have a Named Range that exists with both a workBook scope and a workSheet scope, the behaviour when you use it is that if the activesheet matches the worksheet it is scoped for, then the worksheet range name takes precedence.
It looks like using the name to delete the Named Range follows the same logic.
I tried to delete using the index number but couldn't get that to behave any differently.

The below should do what you are trying to do.
It revolves around checking if the Named Range exists on the active sheet and if it does change the sheet,

It probably needs an enhancement to loop through the "change sheet check" mulitple time in case the Name exists in more that one sheet.
Creating a dummy sheet would always work but I am conscious of the fact that increments the internal sheet index each time.

Sub NameDelete_ChgActiveSheet()

    Dim workbookName As Name, worksheetName As Name
    Dim sWorkbookName As String, sWorksheetName As String, sRemainingName As String
    
    ' XXX Additional Declarations
    Dim i As Long
    Dim origSht As Worksheet
    Dim aName As Variant
    Dim dummySht As Worksheet
    Set origSht = ActiveSheet
    
    'Delete all existing names for fresh sample
    For i = ThisWorkbook.Names.Count To 1 Step -1
        ThisWorkbook.Names(i).Delete
    Next
    
    Set workbookName = ThisWorkbook.Names.Add(Name:="test", RefersToR1C1:="=Sheet1!R2C1")
    Set worksheetName = ThisWorkbook.Worksheets("Sheet1").Names.Add(Name:="test", RefersToR1C1:="=Sheet1!R1C1")
    sWorkbookName = workbookName.Name ' "test"
    sWorksheetName = worksheetName.Name ' "Sheet1!test"
    
    ' Check if Name to delete exists with Scope of Worksheet
    For Each aName In Names
        ' if worksheet scope exists and it is the active worksheet - change the active worksheet
        If InStr(aName.Name, sWorkbookName) >= 2 Then
            If Worksheets.Count = 1 Then
                Set dummySht = Worksheets.Add
            ElseIf origSht.Index <> Worksheets.Count Then
                    Worksheets(origSht.Index + 1).Activate
            Else
                Worksheets(origSht.Index - 1).Activate
            End If
            
            Exit For
        End If
    Next aName
    
    ' Worksheet name takes precedence if it exists on the active sheet - hence the code above
    For Each aName In Names
        If aName.Name = sWorkbookName Then workbookName.Delete
    Next aName
    
    If Not dummySht Is Nothing Then
        Application.DisplayAlerts = False
        dummySht.Delete
        Application.DisplayAlerts = True
    End If
    
    origSht.Activate
           
End Sub