How does Names object resolution work?

[TheAntiGates]

Puzzler...I have a range MyRange on Sheet2. I copied Sheet2 (alt-e-m) as Sheet1, so there's a local Myrange there now.

If I look at the refers-to with control-F3 while on sheet1, it refers to Sheet1, and you can see that it's local. If I look at the refers-to with control-F3 while on sheet2, it refers to Sheet2, and you can see that it's NOT local. So far all cool.

Now in the VBE, ?Names("myRange").RefersTo
refers to sheet1 - EVEN WHEN Sheet2 is active. It goes out of its way to grab Sheet1!MyRange, while I have a "global," unqualified Myrange available to it.

Why in the heck would it do that? This is a specific technical question, regarding the name resolution process for Names object. Is there some cute secret undocumented property like ".Nickname" that it resolves against - in which case both ranges would have that nickname? Grrr. If so, are locals resolved first? Is it alphabetically ordered then? Last come, first served???

[Simon Lloyd]

Hi, i'm not an expert or even very good but dont you have to have a sheet name in order to use the same range name? like this
Sheet1!MyRange, Sheet2!MyRange etc this way when ever you got to a sheet select the name from the drop down you are taken to that named range on the sheet you are viewing, so if your range refers to "A1:A10" you will always get that range, and i think im right in saying that the range needs to be absolute i.e A$1 so the column is relative but the row is absolute.

Just a thought!,

Regards,
Simon

[Bob Phillips]

Names are odd when it comes to global and local variants (IMO).

For instance, if Sheet2 is active

?Names("myRange").RefersTo

will give the global variant, whereas

?Range("myRange").Address(,,,True)

gives the local version?

Personally, I always use the sheet qualifier, or avoid local names altogether.

This might also help,

http://www.xldynamic.com/source/xld.Names.html (http://www.xldynamic.com/source/xld.Names.html)
Names In Excel - A Discussion

[Jan Karel Pieterse]

Things are even worse than you'd think.

If the worksheet with the local name is active and you issue:

ActiveWorkbook.Names("MyName").Delete

The LOCAL name is deleted, not the global one!

In order to work with any globally defined name, you must make sure you are not on an activesheet which has an identical local defined name.

My Name Manager takes this into account:
www.jkp-ads.com/officemarketplacenm-en.asp

[Simon Lloyd]

I guess i was missing the point of the thread! its just that i use the version i posted on a few of my workbooks and it works fine.

Regards,
Simon

[TheAntiGates]

Jan I am pleased that you saw the thread. Everyone, Jan is a well regarded authority and part of an amazing and generous crew of newsgroup volunteers.
In order to work with any globally defined name, you must make sure you are not on an activesheet which has an identical local defined name.Well, I wasn't!! xld: you might also double check your post. I'm seeing .RefersTo returning the local ... even when active sheet has the nonlocal!
Name ManagerAvailable in SEVEN languages! You rock!

[Jan Karel Pieterse]

Jan is a well regarded authority

<blush>


seven languages

My translators rock!

[TheAntiGates]

Another portrayal of this nightmare is
debug.print Sheets(Range("MyRange").Parent.Name)
If you "globally" defined myRange on sheet2,
and you "locally" added myRange on sheet1,
and Sheet2 is active,
You guessed it. Debug window shows "Sheet1"
(the same thing I've [painfully] been saying all along)

The upshot of this is that I can never only refer to a range by its name, unless competely assured that there is no local counterpart in the workbook! How disgusting. So in the general case, I cannot use Names("MyRange") to refer to the global; I have to use Names("Sheet2!MyRange") - for the GLOBAL. Seems pretty absurd.

If anyone has specific knowledge of the resolution, I'm asking. See the last paragraph of initial post here, where I thrash about in pathetic speculation, a la "Last come, first served?"

xld and Jan, thanks for the comments but they consider Sheet1 as active. It's Sheet2 in this problem.