Differences Between Defined Name Scope In Formulas And In VBA

[EngineerRich]

While playing around with defined names I found some behavior that seems to be inconsistent between using defined names within a formula in the workbook environment and defined names within the VBA environment. I have two questions:

1. Can others duplicate this behavior?
2. If others can duplicate it, why is the behavior different between a formula in a workbook and within the VBA environment?

Before you read further, I have written a VBA function to get around this seemingly inconsistent behavior. If someone has a easy solution I'd like to see it so I can compare my solution (VBA function) to it. Some of the Excel VBA gurus out there may be have easy solution, or one that is more elegant than mine. Also, I'd be glad to post my function if someone wants it.

Here is what I've done to observe this seemingly inconsistent behavior. I've created a workbook with two worksheets, Alpha and Beta, where Alpha is the first sheet and Beta is the second sheet. Then I created four defined names with the following scope & value:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Scope[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD]MyName1[/TD]
[TD]Workbook[/TD]
[TD]WrkBk-1[/TD]
[/TR]
[TR]
[TD]MyName1[/TD]
[TD]Worksheet Beta[/TD]
[TD]WrkSht-1B[/TD]
[/TR]
[TR]
[TD]MyName2[/TD]
[TD]Worksheet Alpha[/TD]
[TD]WrkSht-2A[/TD]
[/TR]
[TR]
[TD]MyName2[/TD]
[TD]Workbook[/TD]
[TD]WrkBk-2[/TD]
[/TR]
</tbody>[/TABLE]
​

On each sheet I use the defined names in cells A1 & A2 and get the following results:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Cell[/TD]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Result-Alpha[/TD]
[TD="align: center"]Result-Beta[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]=MyName1[/TD]
[TD]WrkBk1[/TD]
[TD]WrkSht-1B[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]=MyName2[/TD]
[TD]WrkSht-2A[/TD]
[TD]WrkBk-2[/TD]
[/TR]
</tbody>[/TABLE]

​

The results displayed on the worksheets are exactly what is expected based on the established precedence. When duplicate names exist in a workbook, worksheet-scoped names take precedence over workbook-scoped names. On a worksheet, there is a way to override this precedence. To use the workbook-scoped name rather than the worksheet-scoped name, prefix the Defined Name with the name of the workbook, e.g. ‘My Workbook.xlsx’!MyName. The precedence can be overridden for all worksheets other than the first sheet. Worksheet-scoped names always take precedence over workbook-scoped name on the first sheet.

In VBA the precedence seems to work a little differently. Worksheet-scoped names still take precedence over workbook-scoped names. But as determined by some experimentation, a worksheet-scoped name on the first worksheet appears to take precedence over a workbook-scoped name on worksheets other than the first. The scope of the worksheet-scoped name on the first sheet appears to extend beyond the worksheet and applies to the entire workbook. This makes referring to the workbook-scoped name a challenge.

In the Immediate window of the VBE if I type the following ten commands these are the results I get:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Case
[/TD]
[TD="align: center"]Command
(in a single line not as shown here due to text wrap)
[/TD]
[TD="align: center"]Result
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Worksheets(“Alpha”).Names(“MyName1”).Name & Worksheets(“Alpha”).Names(“MyName1”).RefersTo
[/TD]
[TD]Error (MyName1 does not exist at the worksheet level on Alpha)
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Worksheets(“Alpha”).Names(“MyName2”).Name & Worksheets(“Alpha”).Names(“MyName2”).RefersTo
[/TD]
[TD]Alpha!MyName2=“WrkSht-2A”[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Worksheets(“Beta”).Names(“MyName1”).Name & Worksheets(“Beta”).Names(“MyName1”).RefersTo
[/TD]
[TD]Beta!MyName1=“WrkSht-1B”[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Worksheets(“Beta”).Names(“MyName2”).Name & Worksheets(“Beta”).Names(“MyName2”).RefersTo
[/TD]
[TD]Error (MyName2 does not exist at the worksheet level on Beta)[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]ActiveWorkbook.Names(“MyName1”).Name & ActiveWorkbook.Names(“MyName1”).RefersTo
[/TD]
[TD]MyName1=“WrkBk-1”
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]ActiveWorkbook.Names(“MyName2”).Name & ActiveWorkbook.Names(“MyName2”).RefersTo
[/TD]
[TD]Alpha!MyName2=“WkrSht-2A”
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]ActiveWorkbook.Names(“Alpha!MyName1”).Name & ActiveWorkbook.Names(“Alpha!MyName1”).RefersTo
[/TD]
[TD]Error (MyName1 does not exist at the worksheet level on Alpha)
(Same as Case 1)
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]ActiveWorkbook.Names(“Alpha!MyName2”).Name & ActiveWorkbook.Names(“Alpha!MyName2”).RefersTo
[/TD]
[TD]Alpha!MyName2=“WkrSht-2A”
(Same as Case 2)
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]ActiveWorkbook.Names(“Beta!MyName1”).Name & ActiveWorkbook.Names(“Beta!MyName1”).RefersTo
[/TD]
[TD]Beta!MyName1=“WrkSht-1B”
(Same as Case 3)[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]ActiveWorkbook.Names(“Beta!MyName2”).Name & ActiveWorkbook.Names(“Beta!MyName2”).RefersTo
[/TD]
[TD]Error (MyName2 does not exist at the worksheet level on Beta)
(Same as Case 4)
[/TD]
[/TR]
</tbody>[/TABLE]
​

In VBA most of the results return the expected values. The exception is Case 6. The result is the worksheet-scoped name MyName2 for the first worksheet even though there is a workbook-scoped name MyName2. This result is different than the formula on the worksheet in Excel. On Worksheet Beta in cell A2 the formula is =MyName2 with a result of WrkBk‑2. In VBA there appears to be no direct way to get the same result achieved by this formula on Worksheet Beta.

In VBA a worksheet-scoped name on the first worksheet appears to take precedence over a workbook-scoped name on worksheets other than the first. The scope of the worksheet-scoped name on the first sheet appears to extend beyond the worksheet and applies to the entire workbook.

In order to check that is behavior follows the first worksheet, swap the worksheets in Excel. Make Worksheet Beta the first worksheet rather than Worksheet Alpha. Then repeat the ten commands above. The cases that give different results are:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Case
[/TD]
[TD="align: center"]Command
(in a single line not as shown here due to text wrap)
[/TD]
[TD="align: center"]Result
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]ActiveWorkbook.Names(“MyName1”).Name & ActiveWorkbook.Names(“MyName1”).RefersTo[/TD]
[TD]Beta!MyName1=“WrkSht-1B”[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]ActiveWorkbook.Names(“MyName2”).Name & ActiveWorkbook.Names(“MyName2”).RefersTo[/TD]
[TD]MyName2=“WrkBk-2”[/TD]
[/TR]
</tbody>[/TABLE]
​

The behavior is consistent. The scope of the worksheet-scoped name on the first sheet appears to extend beyond the worksheet and applies to the entire workbook.

The result for Case 6 is now the expected result of the workbook-scoped name MyName3. The result for Case 5 is the worksheet-scoped name MyName1 for the first worksheet even though there is a workbook-scoped name MyName1. This result is different than the formula on the worksheet in Excel. On Worksheet Alpha in cell2 A1 the formula is =MyName1 with a result of WrkBk‑1. In VBA there appears to be no direct way to get the same result achieved by this formula on Worksheet Alpha.

This confirms the behavior observed earlier. In VBA a worksheet-scoped name on the first worksheet appears to take precedence over a workbook-scoped name on worksheets other than the first. The scope of the worksheet-scoped name on the first sheet appears to extend beyond the worksheet and applies to the entire workbook.

In VBA there appears to be no direct way to refer to workbook-scoped name if the first worksheet has a duplicate name that is worksheet-scoped, therefore an indirect way is required to refer to the workbook-scoped name.

I have written a function for use in VBA which yields the same results as are returned by the formulas in the workbook. But I am wondering if there is a better, easier way.

Thanks

 

[thisoldman]

I almost never use workseet scoped named ranges, the named ranges are workbook scoped. The naming scheme I use, from one of Jordan Alexander's books, has the format: < tab_name >.< descriptive_name >

The naming scheme prevents conflicts.

In one workbook, I might have Data.JuneSales as a named range referring to the cell $D$6 on the Data worksheet, with the value 21000. Another named range, Calculations.JuneSales, refers to cell $B$3 on the Calculations tab, with a value of 1300.

Sub TryMe()
    Dim rng_1 As Range, rng_2 As Range
    Dim val_1 As Long, val_2 As Long
    
    Set rng_1 = [Data.JuneSales]
    val_1 = [Data.JuneSales]
    
    Set rng_2 = [Calculations.JuneSales]
    val_2 = [Calculations.JuneSales].Value2
    
    Debug.Print rng_1.Parent.Name        ' Data
    Debug.Print rng_2.Parent.Name        ' Calculations
    
    Debug.Print rng_1.Address            ' $D$6
    Debug.Print rng_2.Address            ' $B$3
    
    Debug.Print rng_1.Value2             ' 21000
    Debug.Print rng_2.Value2             ' 1300
End Sub

The shortcut bracket evaluations I used in the example are neat—Excel knows when I want a range object and when, instead, I want a value returned. I wouldn't use the brackets where I'm coding for speed, but most of the time the slowdown from the evaluation is inconsequential.

 

[EngineerRich]

Thanks for your response. I agree the best option is, when possible, to avoid duplicate names and thereby the confusion that can be caused by their use.