strange VBA behavior with named ranges

[Brian Murphy]

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?

 

[Peter T]

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

 

[Charlie]

....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]

comments In-line

"Charlie" wrote in message

...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.

you mean something like -
Name "Sheet1!aaa" refersto: =Sheet2!A1

Although do-able it's confusing. I think there would have to be a special
reason ever to want to do that, but I don't think Excel has any ambiguity
with that. No matter what sheet it is used on and with any other similar
names around, providing it is correctly qualified the right name will be
referred to.

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.

It can be useful but sometimes it's confusing, eg when similar names already
exits in the destination workbook or a name refers back to the source book.

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


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


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?

A bad analagy with sort keys I'm afraid
The way you've got it is exactly what I meant to say, though perhaps not
what I actually said (-:
Thanks for helping to clarify that

Regards,
Peter T

 

[Charlie]

We have an outside vendor's app that creates Excel reports. We set up the
layout, formulas, and DB fields on one sheet as a template. I decided to try
using named ranges in the formulas. I was unaware that the reports could be
configured to produce multiple pages whenever there was a change of data in a
given field, e.g. "Contract No."

I was much surprised the first time I saw one of those multi-page reports,
and the fact that my formulas all worked correctly. Page 1 still used the
Book Level names while the other sheets each had their own Sheet Level names.
Excel generates them during the copy operation. *

That was my first exposure to "Sheet Level" and "Book Level" names, as I
chose to call them. I took me a while to figure out what was going on, and
was the beginning of my adding the optional args to my MakeNamedRange and
other subs.

MakeNamedRange allows for specifying ranges in alpha or numeric formats
(good for when the rows and columns are being calculated)...

MakeNamedRange "Name1", "A1:A10"
MakeNamedRange "Name1", 1, 1, 10, 1

....and the sheet names can be included or supplied separately (in case they
are passed as variables from another routine)...

MakeNamedRange "Name1", "Sheet2!A1:A10"
MakeNamedRange "Name1", "A1:A10", SheetOfRange:="Sheet2"
MakeNamedRange "Sheet2!Name1", "Sheet2!A1:A10"
MakeNamedRange "Name1", "A1:A10", SheetOfRange:="Sheet2",
SheetForName:="Sheet2"

....because example 1 below will fail if ShtName is blank, but example 2 will
simply use the active sheet instead.

MakeNamedRange "Name1", ShtName & "!" & "A1:A10"
MakeNamedRange "Name1", "A1:A10", SheetOfRange:=ShtName

Regards,
Charlie

* This, by the way, is one example of Sheet Level named ranges that can
refer to cells on another sheet. Say, for example that the app always spits
out a conversion factor, but only on page 1 of the reports. If I name that
range as a Book Level name when the sheet gets copied the subsequent sheets
will have that name as a Sheet Level name but they point to the corresponding
cell on their own sheet. Not good in my formulas. And it is beyond my
control. But if I make that particular name a Sheet Level name on Page 1,
when the sheet is copied the subsequent Sheet Level names point back to the
conversion factor on Page 1. Works perfectly.

The only other way to solve this problem would be to loop through each sheet
after Page 1 and delete the local name so the formulas could "see" the global
name. Could get unruly if there are a lot of names to delete on each sheet.

 

[Brian Murphy]

I did not know about the sort order of defined names. So I definitely
have learned something new.

When you explain it as a "visibility" issue in relation to the sort
order, it all now makes sense.

I came across this in a VBA routine which uses Application.Evaluate to
compute some values using named ranges. The easy fix was to put the
sheet name in front of the defined name. For example:

x = Application.Evaluate("MAX(somenamedrange)")

now says

x = Application.Evaluate("MAX('sheet name'!somenamedrange)")

This gives me the result I want even if a chart sheet is the active
sheet.

Cheers,

Brian