Differences Between Defined Name Scope In Formulas And In VBA

EngineerRich

New Member
Joined
Apr 14, 2018
Messages
2
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:
NameScopeValue
MyName1WorkbookWrkBk-1
MyName1Worksheet BetaWrkSht-1B
MyName2Worksheet AlphaWrkSht-2A
MyName2WorkbookWrkBk-2

<tbody>
</tbody>
On each sheet I use the defined names in cells A1 & A2 and get the following results:
CellFormulaResult-AlphaResult-Beta
A1=MyName1WrkBk1WrkSht-1B
A2=MyName2WrkSht-2AWrkBk-2

<tbody>
</tbody>

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:
Case
Command
(in a single line not as shown here due to text wrap)
Result
1
Worksheets(“Alpha”).Names(“MyName1”).Name & Worksheets(“Alpha”).Names(“MyName1”).RefersTo
Error (MyName1 does not exist at the worksheet level on Alpha)
2
Worksheets(“Alpha”).Names(“MyName2”).Name & Worksheets(“Alpha”).Names(“MyName2”).RefersTo
Alpha!MyName2=“WrkSht-2A”
3Worksheets(“Beta”).Names(“MyName1”).Name & Worksheets(“Beta”).Names(“MyName1”).RefersTo
Beta!MyName1=“WrkSht-1B”
4Worksheets(“Beta”).Names(“MyName2”).Name & Worksheets(“Beta”).Names(“MyName2”).RefersTo
Error (MyName2 does not exist at the worksheet level on Beta)
5
ActiveWorkbook.Names(“MyName1”).Name & ActiveWorkbook.Names(“MyName1”).RefersTo
MyName1=“WrkBk-1”
6
ActiveWorkbook.Names(“MyName2”).Name & ActiveWorkbook.Names(“MyName2”).RefersTo
Alpha!MyName2=“WkrSht-2A”
7ActiveWorkbook.Names(“Alpha!MyName1”).Name & ActiveWorkbook.Names(“Alpha!MyName1”).RefersTo
Error (MyName1 does not exist at the worksheet level on Alpha)
(Same as Case 1)
8
ActiveWorkbook.Names(“Alpha!MyName2”).Name & ActiveWorkbook.Names(“Alpha!MyName2”).RefersTo
Alpha!MyName2=“WkrSht-2A”
(Same as Case 2)
9
ActiveWorkbook.Names(“Beta!MyName1”).Name & ActiveWorkbook.Names(“Beta!MyName1”).RefersTo
Beta!MyName1=“WrkSht-1B”
(Same as Case 3)
10
ActiveWorkbook.Names(“Beta!MyName2”).Name & ActiveWorkbook.Names(“Beta!MyName2”).RefersTo
Error (MyName2 does not exist at the worksheet level on Beta)
(Same as Case 4)

<tbody>
</tbody>

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:
Case
Command
(in a single line not as shown here due to text wrap)
Result
5ActiveWorkbook.Names(“MyName1”).Name & ActiveWorkbook.Names(“MyName1”).RefersToBeta!MyName1=“WrkSht-1B”
6ActiveWorkbook.Names(“MyName2”).Name & ActiveWorkbook.Names(“MyName2”).RefersToMyName2=“WrkBk-2”

<tbody>
</tbody>
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

Well-known Member
Joined
Jan 5, 2014
Messages
1,073
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.
Code:
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

New Member
Joined
Apr 14, 2018
Messages
2
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.
 

Forum statistics

Threads
1,082,382
Messages
5,365,128
Members
400,825
Latest member
Sreekanth_21

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top