Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 247
Default Named Range: Same Names, Multiple Workbooks with Same Sheet Name

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Named Range: Same Names, Multiple Workbooks with Same Sheet Name

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 247
Default Named Range: Same Names, Multiple Workbooks with Same Sheet Na

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default Named Range: Same Names, Multiple Workbooks with Same Sheet Na

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 247
Default Named Range: Same Names, Multiple Workbooks with Same Sheet Na

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named sheet to change Cells names Skrapik New Users to Excel 3 August 11th 07 05:46 AM
Variable names for named range Barb Reinhardt Excel Discussion (Misc queries) 4 March 19th 07 06:37 PM
Strange named range issue with multiple workbooks Adam White Excel Discussion (Misc queries) 0 November 14th 06 03:34 AM
How do you point to a named range in linked workbooks? KG Excel Discussion (Misc queries) 5 February 18th 05 10:55 PM
How to Link named ranges from multiple Workbooks into a single Wo. PMAP_HELP Excel Discussion (Misc queries) 1 December 14th 04 06:51 PM


All times are GMT +1. The time now is 12:01 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"