....and it gets even crazier when a sheet-level name refers to a range of
cells on a different sheet! It boggled me for a while until I worked it all
out. My personal functions now have two optional args, SheetOfRange and
SheetForName, to help me keep it straight.

There's one other nice thing about named ranges: if a named range (local or
global) is on a sheet that gets copied, the new sheet(s) now have that same
named range but they are local to the new sheet(s). Comes in handy.

One thing though, your illustration of the sort order is correct...

Sheet1!aaa
Sheet2!aaa
aaa
Sheet1!zzz
zzz

....but your description of how they are sorted seems off. It is probably
just how I am interpreting what you said

sort key1: name
sort key2: worksheet name (not tab order)
sort key3: workbook level

It seems they are sorted by two keys, not three

Key1: name
Key2: level

whereby if level is null (i.e. a workbook level name) then it is treated as
"greater than" (comes after) a non-null level. Am I missing something?


"Peter T" wrote:

Rather than describe that as a bug I think just accept it the way it is. I
know you already know most of the following but here's a summary -

With Worksheets, when a name is named similarly as Global and Local on the
sheet, only the Local name is visible in cell formulas. Also when the sheet
is active only the Local name is visible if you try and return say -

Range("aaa")
Range("Sheet1!aaa").Address
Range("Book1!aaa").Address
Names("Book1!aaa").RefersToRange.address

All the above will refer to the local name "aaa", with sheet1 active. Even
if the Global name "aaa" refers to cells on sheet1, Book1!aaa will refer to
the Local name.

With chart sheets there is no visibility issue. When a chart sheet is active
and you try to return Range("aaa") it returns the first "aaa" name in the
Names collection.

The Names collection is ordered like this -
sort key1: name
sort key2: worksheet name (not tab order)
sort key3: workbook level

eg
Sheet1!aaa
Sheet2!aaa
aaa
Sheet1!zzz
zzz

So, when a chart sheet is active in VBA Range("aaa") will refer to the local
name on sheet1. However if you qualify with Sheet2! or wbName! you will get
the not only the expected sheet but correct range (if say global & local
names "aaa" refers to a different ranges on same sheet)

If your overall purpose relates to using names in chart series formulas I
don't think there's any problem. Just fully qualify the name with sheet or
workbook name (apostrophes if/as needed or always include them to be on the
safe side)

One more (fortunate) quirk. Although a global name is not visible on a
worksheet when a similarly named local name exists, in an embedded chart on
the sheet there's no visibility problem providing the name is correctly
qualified in the series formula with sheet_name! or book_name!.

Regards,
Peter T


"Brian Murphy" wrote in message
...
Try the following, and see if you think this is strange.

start with an empty workbook with three worksheets.
create a book level defined name "aaa" on Sheet1
create a sheet level defined name "aaa" on Sheet2
Make Sheet1 the activesheet.
In the VBA immediate window do msgbox
range("aaa").address(external:=true)
you should get the range on Sheet1
make Sheet2 the activesheet.
In the VBA immediate window do msgbox
range("aaa").address(external:=true)
you should get the range on Sheet2
make Sheet3 the Activesheet.
In the VBA immediate window do msgbox
range("aaa").address(external:=true)
you should get the range on Sheet1

So far, so good.

Now make a chart and put it on a separate chartsheet.
make the chartsheet the activesheet.
In the VBA immediate window do msgbox
range("aaa").address(external:=true)
I get the range on Sheet2

I think with the chartsheet active, the book level name should be
returned as when Sheet3 was active.

I wonder if this is a bug in VBA?