Excel named range scoped to workbook printing a from a sheet scoped named range

Question

MVCE in a newly created 3 worksheet workbook. Within which on sheet1 and sheet2 the name foo has been created, scoped to each sheet respectively. After, scoped to the workbook, the same name is used in reference to sheet3. When accessing the workbook scoped name I'm getting the name on sheet2 and can't understand why.

Is there something obvious I'm missing?

Public Sub NameConfusion()
    Do While ThisWorkbook.Names.Count > 0
        ThisWorkbook.Names(1).Delete
    Loop

    AddNames

    Debug.Print ThisWorkbook.Names("foo").RefersToRange.Address(False, False) ' --> B2 NOT expected
    'I would expect it to be C5

    ThisWorkbook.Names("foo").Delete
    Debug.Print ThisWorkbook.Names("foo").RefersToRange.Address(False, False) ' --> C5 NOT expected, based on above
    'I would expect it to be B2
End Sub

Private Sub AddNames()
    Sheet1.Names.Add "foo", Sheet1.Range("a2")
    Debug.Print ThisWorkbook.Names.Count ' --> 1 as expected

    Sheet2.Names.Add "foo", Sheet2.Range("B2")
    Debug.Print ThisWorkbook.Names.Count ' --> 2 as expected

    ThisWorkbook.Names.Add "foo", Sheet3.Range("C5")
    Debug.Print ThisWorkbook.Names.Count ' --> 3 as expected
End Sub

NOTE: I have had issues where programatically doing this overwrites one of the worksheet scoped names with the workbook scoped name. When this happens, manually adding the names via Formulas tab>Defined Names group>Name manager button and skipping evaluation over to Debug.Print after the call to AddNames will still result in the non workbook scoped address being printed.

Answer 1

When it looks for foo it looks first in local scope starting with the first physically placed sheet i.e. Leftmost. I guess your B2 foo sheet is leftmost. When this gets deleted it can't find foo in local for that sheet so goes to the workbook scope. This goes for deletion as well. You can test by switching the physical ordering of sheets and you will see you get different results according to ordering. An easy fix would seem to be to use distinct names where possible.