Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
....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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
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... 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? 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 "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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
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. "Peter T" wrote: 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... 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? 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 "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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
strange VBA behavior with named ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Named Ranges | Excel Discussion (Misc queries) | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Need Help: Strange, Strange Behavior ( Not Me. My Model! ) | Excel Programming | |||
Strange behavior | Excel Programming | |||
Strange behavior. | Excel Discussion (Misc queries) |