Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
1: (will) have massive amounts of coding with Named Ranges.
Multiple WorkBooks, each having a sheet called "PRICE" and each having multiple range names that are identical (although physical range would be different). Assume 2 or more workbooks are OPEN. Is there any possibility that under any circumstances a range name can be "picked up" from an open (but not active) workbook? 2: Based on the knowledge from the experts out there, even if there is no problem as regards # 1, above, would it still be wise to custom name each range name, based on the workbook it is in? Thanks for your input in advance. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Named ranges are interesting once you get into code... The XL application
holds worksbooks which holds worksheets which hold ranges. That is how the object model is structured. So ranges only exist as part of a sheet as part of a book. If you do not specify in code the book and sheet then the default assumption is active workbook and active worksheet. This means that you either have to select the book and sheet to get to the named range (certainly not an optimal solution) or you need to explicitly reference the book and sheet Thisworkbook.sheets("Sheet1").Range("MyRangeName") The other method to get at the global named ranges is ThisWorkbook.Names("MyRangeName").RefersToRange In either case you just need to be explicit in your references. If you are not explicit then the activeworkbook and active worksheet will be the default. ***Note that when you are dealing with multiple workbooks and worksheets you really want to be very explicit with ALL of your references. It makes the code a lot easer to debug and less prone to errors as you are always in charge of where your code is operating... As for your second question keep the names as they are. It actually keeps things a lot neater and tidier. -- HTH... Jim Thomlinson "BEEJAY" wrote: 1: (will) have massive amounts of coding with Named Ranges. Multiple WorkBooks, each having a sheet called "PRICE" and each having multiple range names that are identical (although physical range would be different). Assume 2 or more workbooks are OPEN. Is there any possibility that under any circumstances a range name can be "picked up" from an open (but not active) workbook? 2: Based on the knowledge from the experts out there, even if there is no problem as regards # 1, above, would it still be wise to custom name each range name, based on the workbook it is in? Thanks for your input in advance. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Jim:
Thanks so much for your clear explanation. One further question, if I may. What are the benefits of "local to active Sheet" vs Global? Using Name Manager 4.1 I find I currently have 129 names, (and lots more to come). I converted all my "PRICE" sheet names to "Local to active sheet" ( 88 of them). All the other sheets in the workbook are of a total different "character" (contact info on page 1, Warranty sheet, etc....) so I think the chances of duplicates are quit slim. There should also be NO reason whatsoever of ever needing the named ranges on "PRICE" to be in any fashion referenced by any other sheet in the work book. Looking forward to your response. "Jim Thomlinson" wrote: Named ranges are interesting once you get into code... The XL application holds worksbooks which holds worksheets which hold ranges. That is how the object model is structured. So ranges only exist as part of a sheet as part of a book. If you do not specify in code the book and sheet then the default assumption is active workbook and active worksheet. This means that you either have to select the book and sheet to get to the named range (certainly not an optimal solution) or you need to explicitly reference the book and sheet Thisworkbook.sheets("Sheet1").Range("MyRangeName") The other method to get at the global named ranges is ThisWorkbook.Names("MyRangeName").RefersToRange In either case you just need to be explicit in your references. If you are not explicit then the activeworkbook and active worksheet will be the default. ***Note that when you are dealing with multiple workbooks and worksheets you really want to be very explicit with ALL of your references. It makes the code a lot easer to debug and less prone to errors as you are always in charge of where your code is operating... As for your second question keep the names as they are. It actually keeps things a lot neater and tidier. -- HTH... Jim Thomlinson "BEEJAY" wrote: 1: (will) have massive amounts of coding with Named Ranges. Multiple WorkBooks, each having a sheet called "PRICE" and each having multiple range names that are identical (although physical range would be different). Assume 2 or more workbooks are OPEN. Is there any possibility that under any circumstances a range name can be "picked up" from an open (but not active) workbook? 2: Based on the knowledge from the experts out there, even if there is no problem as regards # 1, above, would it still be wise to custom name each range name, based on the workbook it is in? Thanks for your input in advance. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
With named ranges as with programming, keep the scope as local as possible.
When in doubt make the named ranges local. If you need to increase the scope then change the named ranges to global. The benefit of doing this is it keeps things simple. If a named range has no integrity outside of the sheet that it is in then don't expose it to other sheets. If you expose it there is the possiblility that it will be misconstrued. If I have a localy defined named range called "Sales" on as sheet called Dept1 then it is refering to the Dept1 Sales. To use that named range outside of the Dept1 sheet it requires the sheet reference (=Dept1!Sales). Now the data has integrity as the Sales are defined in terms of where they came from. I might have 5 different department sheets all with the name Sales on them. the value in this as that in code I can referenct the values by referencing the sheet without having to worry what the range name is. -- HTH... Jim Thomlinson "BEEJAY" wrote: Jim: Thanks so much for your clear explanation. One further question, if I may. What are the benefits of "local to active Sheet" vs Global? Using Name Manager 4.1 I find I currently have 129 names, (and lots more to come). I converted all my "PRICE" sheet names to "Local to active sheet" ( 88 of them). All the other sheets in the workbook are of a total different "character" (contact info on page 1, Warranty sheet, etc....) so I think the chances of duplicates are quit slim. There should also be NO reason whatsoever of ever needing the named ranges on "PRICE" to be in any fashion referenced by any other sheet in the work book. Looking forward to your response. "Jim Thomlinson" wrote: Named ranges are interesting once you get into code... The XL application holds worksbooks which holds worksheets which hold ranges. That is how the object model is structured. So ranges only exist as part of a sheet as part of a book. If you do not specify in code the book and sheet then the default assumption is active workbook and active worksheet. This means that you either have to select the book and sheet to get to the named range (certainly not an optimal solution) or you need to explicitly reference the book and sheet Thisworkbook.sheets("Sheet1").Range("MyRangeName") The other method to get at the global named ranges is ThisWorkbook.Names("MyRangeName").RefersToRange In either case you just need to be explicit in your references. If you are not explicit then the activeworkbook and active worksheet will be the default. ***Note that when you are dealing with multiple workbooks and worksheets you really want to be very explicit with ALL of your references. It makes the code a lot easer to debug and less prone to errors as you are always in charge of where your code is operating... As for your second question keep the names as they are. It actually keeps things a lot neater and tidier. -- HTH... Jim Thomlinson "BEEJAY" wrote: 1: (will) have massive amounts of coding with Named Ranges. Multiple WorkBooks, each having a sheet called "PRICE" and each having multiple range names that are identical (although physical range would be different). Assume 2 or more workbooks are OPEN. Is there any possibility that under any circumstances a range name can be "picked up" from an open (but not active) workbook? 2: Based on the knowledge from the experts out there, even if there is no problem as regards # 1, above, would it still be wise to custom name each range name, based on the workbook it is in? Thanks for your input in advance. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Jim:
Please accept my belated thanks!!! As I have been mulling over your explanations, I have continued assigning names. Around 230 now, on one sheet. With JKP's Name Manager, I feel like I actually have proper control and over-sight of all these names. What is REALLY great is that all my code, using named ranges is working exactly as expected. I did a lot of testing for adding or deleting rows, etc. and that also works as per expectations. Jim, again, my thanks. Your logical explanations have far reaching benefits. "Jim Thomlinson" wrote: With named ranges as with programming, keep the scope as local as possible. When in doubt make the named ranges local. If you need to increase the scope then change the named ranges to global. The benefit of doing this is it keeps things simple. If a named range has no integrity outside of the sheet that it is in then don't expose it to other sheets. If you expose it there is the possiblility that it will be misconstrued. If I have a localy defined named range called "Sales" on as sheet called Dept1 then it is refering to the Dept1 Sales. To use that named range outside of the Dept1 sheet it requires the sheet reference (=Dept1!Sales). Now the data has integrity as the Sales are defined in terms of where they came from. I might have 5 different department sheets all with the name Sales on them. the value in this as that in code I can referenct the values by referencing the sheet without having to worry what the range name is. -- HTH... Jim Thomlinson "BEEJAY" wrote: Jim: Thanks so much for your clear explanation. One further question, if I may. What are the benefits of "local to active Sheet" vs Global? Using Name Manager 4.1 I find I currently have 129 names, (and lots more to come). I converted all my "PRICE" sheet names to "Local to active sheet" ( 88 of them). All the other sheets in the workbook are of a total different "character" (contact info on page 1, Warranty sheet, etc....) so I think the chances of duplicates are quit slim. There should also be NO reason whatsoever of ever needing the named ranges on "PRICE" to be in any fashion referenced by any other sheet in the work book. Looking forward to your response. "Jim Thomlinson" wrote: Named ranges are interesting once you get into code... The XL application holds worksbooks which holds worksheets which hold ranges. That is how the object model is structured. So ranges only exist as part of a sheet as part of a book. If you do not specify in code the book and sheet then the default assumption is active workbook and active worksheet. This means that you either have to select the book and sheet to get to the named range (certainly not an optimal solution) or you need to explicitly reference the book and sheet Thisworkbook.sheets("Sheet1").Range("MyRangeName") The other method to get at the global named ranges is ThisWorkbook.Names("MyRangeName").RefersToRange In either case you just need to be explicit in your references. If you are not explicit then the activeworkbook and active worksheet will be the default. ***Note that when you are dealing with multiple workbooks and worksheets you really want to be very explicit with ALL of your references. It makes the code a lot easer to debug and less prone to errors as you are always in charge of where your code is operating... As for your second question keep the names as they are. It actually keeps things a lot neater and tidier. -- HTH... Jim Thomlinson "BEEJAY" wrote: 1: (will) have massive amounts of coding with Named Ranges. Multiple WorkBooks, each having a sheet called "PRICE" and each having multiple range names that are identical (although physical range would be different). Assume 2 or more workbooks are OPEN. Is there any possibility that under any circumstances a range name can be "picked up" from an open (but not active) workbook? 2: Based on the knowledge from the experts out there, even if there is no problem as regards # 1, above, would it still be wise to custom name each range name, based on the workbook it is in? Thanks for your input in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Named sheet to change Cells names | New Users to Excel | |||
Variable names for named range | Excel Discussion (Misc queries) | |||
Strange named range issue with multiple workbooks | Excel Discussion (Misc queries) | |||
How do you point to a named range in linked workbooks? | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) |