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:
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:
…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.
If I type = then select A1 where the global name lives, note that no name comes up in the intellisense:
..but if I do the same with the local name, I get intellisense:
Now things get a little weirder. If I type =SomeName, I get two options in the intellisense:
If I select the SomeName (Workbook) option, Excel qualifies the reference with the workbook name:
…but when I push Enter, it’s still the local name that’s being referenced:
…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’:
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:
What a mess. Push cancel, and you’re left with an invalid name:
Names and Collections
It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection:
You can reference both names by index number just fine:
…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:
Here I retrieve their refersto ranges from those index numbers:
Here I try to retrieve their refersto ranges from their names:
And here I try to retrieve their names from their names:
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!
8 thoughts on “Global name meets Local, Global freaks out”
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.
“It turns out that the local name gets added to both the ActiveWorkbook.Names collection AND the ActiveSheet.Names Collection”
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.
@Gill: That Name Manager Charles refers to has a built-in filter for external references in names.
And an option to list names too. Find it here: http://www.jkp-ads.com/officemarketplacenm-en.asp
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).
@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.
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.
Here’s the source.
You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.
Thanks for that. Weird.
Posting code? Use <pre> tags for VBA and <code> tags for inline.