Global name meets Local, Global freaks out

So here’s something I came across today. Somehow I’d managed to set up both local AND global names in a sheet, and found that I pretty much couldn’t do anything with the Global name via VBA as a result.

Say I set up this global name:
 
SomeName - Global
 
 
Note that it says SomeName in the NameBox when I have the name selected. Nothing weird about that.

Now I add a local name with the same name, pointing to A2:
 
SomeName - Local
 
 
…and here you can see both names in the Name Manager. Note that it says SomeName in the NameBox when I have the local name selected. Nothing weird about that, either…local names take precedence over global.
 

SomeName - Both_Name Manager
 
 
If I type = then select A1 where the global name lives, note that no name comes up in the intellisense:
 
Equals Global
 
 
..but if I do the same with the local name, I get intellisense:
 
Equals Local
 
 
Now things get a little weirder. If I type =SomeName, I get two options in the intellisense:
 
Equals SomeName
 
 
If I select the SomeName (Workbook) option, Excel qualifies the reference with the workbook name:
 
Book2 SomeName
 
 
…but when I push Enter, it’s still the local name that’s being referenced:
 
Book2 SomeName Not
 
 
…and when I select it again, I see that Excel has bizarrely changed that Book1! qualifier to a Sheet1! qualifier. In other words, it’s said ‘I know you asked for workbook, but I’m giving you local’:
 
Book2 SomeName Local Qualifier
 
 
So what the hell is the point of those two options in the intellisense, if no matter what you do, Excel gives you the local name? Beats me. What’s worse, if your workbook name has a space in it, then Excel forgets to wrap apostrophes around it, and the Update Values dialog comes up:
 
Update Values dialog
 
 
What a mess. Push cancel, and you’re left with an invalid name:
 
Book2 SomeName spaces
 
 

Names and Collections

It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection:
? activeworkbook.Names.Count
2
? activesheet.names.count
1

You can reference both names by index number just fine:
? activeworkbook.Names(1).Name
Sheet1!SomeName
? activeworkbook.Names(2).Name
SomeName

…but any attempt to reference the globally scoped one by name gives you the local name. Here, I retrieve their names from their index numbers:
? activeworkbook.Names(1).Name
Sheet1!SomeName
? activeworkbook.Names(2).Name
SomeName

Here I retrieve their refersto ranges from those index numbers:
? activeworkbook.Names(1).RefersTo
=Sheet1!$A$2
? activeworkbook.Names(2).RefersTo
=Sheet1!$A$1

Here I try to retrieve their refersto ranges from their names:
? activeworkbook.Names(“Sheet1!SomeName”).RefersTo
=Sheet1!$A$2
? activeworkbook.Names(“SomeName”).RefersTo
=Sheet1!$A$2

And here I try to retrieve their names from their names:
? activeworkbook.Names(“Sheet1!SomeName”).Name
Sheet1!SomeName
? activeworkbook.Names(“SomeName”).Name
Sheet1!SomeName

As per this Stack Overflow thread, If you want to identify the global one, you have to loop through the collection of names and find the one that has an “!” in its name.

So there you have it: if you’re referencing names by name, don’t assume you’ll get the one you want. Tedious!

7 thoughts on “Global name meets Local, Global freaks out

  1. If you use our (Jan Karel Pieterse & me) Name Manager it specifically warns about duplicate global local names and has a separate filter for them, and special code to handle them properly.

  2. “It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection”

    That’s interesting…

    I built myself a utility to locate external references in a workbook names, it’s supposed to list local names by worksheet, and global names separately. I assumed the fact that local names get duplicated under the global heading was down to me being a twit, and added it to the list of things to get round to fixing some time this decade.

    At least I know exactly what’s causing it now! Thanks.

  3. And not only is the Name Manager awesome, but it’s FREE. (There is a premium version available with even more firepower if you need it, I believe).

  4. @Jeff Weir: In a recent blog post by Mynda Treacy, she notes:
    Note: you cannot override the scope for the first worksheet, this will always use the local name if there is a name conflict.
    http://www.myonlinetraininghub.com/excel-named-range-shortcut

    I originally went through your example step by step and experienced the same results that you did. After reading Mynda’s post I tried it again with sheet2 and had no issues. I guess the takeaway is to avoid using sheet1 and then you’ll be good to go.

Leave a Reply

Your email address will not be published. Required fields are marked *