CODEWITHSUNDEEP
excelvbanamed-ranges
IvenBach
IvenBach

Reputation: 583

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

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.

Upvotes: 1

Views: 152

Answers (2)

Joost
Joost

Reputation: 102

I did the testing, but I didn't get the result @QHarr mentioned. I used this code:

Sub test()
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        Debug.Print nm.RefersTo
    Next
End Sub

with 3 sheets with the same name, 2 worksheet names (sheet1 and sheet3), 1 workbook name (sheet2). The result would always be:

=Sheet1!$B$3:$C$4
=Sheet3!$B$3:$C$4
=Sheet2!$B$3:$C$4

no matter what the physical sequence was. When I renamed the tab of Sheet1, it became:

=Sheet3!$B$3:$C$4
=zzSheet1!$B$3:$C$4
=Sheet2!$B$3:$C$4

So I guess the sequence is: first the worksheet names, in alfabetical order of the tabname, then the workbook names. Please note it is the order of the tabname, not the sheetname.

Upvotes: 1

QHarr
QHarr

Reputation: 84465

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.

Upvotes: 1

Related Questions