Named Range: Same Names, Multiple Workbooks with Same Sheet Name

[BEEJAY]

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.

 

[Jim Thomlinson]

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.

 

[BEEJAY]

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]

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.

 

[BEEJAY]

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.