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:
? 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!