Duplicate named ranges between workbooks causes my custom functions to get confused
I am a bit confused about scope of named ranges in Excel.  I have two versions of an excel workbook.  Occasionally, the users need to copy data from an older version of the workbook into a newer version.  There are some custom VBA functions used in most of the cell calculations.  Each of those functions looks up around 4 to 12 named ranges on the sheet.  This seemed to be working well...  However, I recently found out that when two versions of the file are open, the VBA references to all named ranges return the values only from the first file that was opened (so if the newer version of the form was opened first then the older version will act like some of its data comes from the new version!).  The named ranges from the second file seem to be ignored by the second file, at least in the VBA code, as long as both files remain open.  If I close the workbook that was opened first, the second one will calculate correctly.

I found a partial fix: instead of doing this:

    Blah = Range("valueXYZ").Value
I do this instead:

    Blah = ThisWorkbook.Names("namedCellXYZ").RefersToRange.Value
This works great, but only on the newer version of the workbook.  I can't update the VBA code in the old version of the workbook.  This means that if customers open the old version after the new version is already open, (so both are open, but the old one was opened second), the old version will be getting the values and named ranges from the newer version--and thus report incorrect numbers and ranges.  This is bad.  I need a good way to prevent the old sheet from accessing the named ranges from the new one, but I can only modify the new one.  The only thing I can think of is to rename all the named ranges and update a lot of VBA code in the new version--something that is prone to errors and sounds like excessive work.

Any suggestions?  For example, is it possible to at least show the user a warning when the second file gets opened?  Or is it possible to use VBA to restrict the scope of the named ranges?  Any other ideas?
Duplicate named ranges between workbooks causes my custom functions to get confused
===

I am a bit confused about scope of named ranges in Excel.  I have two versions of an excel workbook.  Occasionally, the users need to copy data from an older version of the workbook into a newer version.  There are some custom VBA functions used in most of the cell calculations.  Each of those functions looks up around 4 to 12 named ranges on the sheet.  This seemed to be working well...  However, I recently found out that when two versions of the file are open, the VBA references to all named ranges return the values only from the first file that was opened (so if the newer version of the form was opened first then the older version will act like some of its data comes from the new version!).  The named ranges from the second file seem to be ignored by the second file, at least in the VBA code, as long as both files remain open.  If I close the workbook that was opened first, the second one will calculate correctly.

I found a partial fix: instead of doing this:

    Blah = Range("valueXYZ").Value
I do this instead:

    Blah = ThisWorkbook.Names("namedCellXYZ").RefersToRange.Value
This works great, but only on the newer version of the workbook.  I can't update the VBA code in the old version of the workbook.  This means that if customers open the old version after the new version is already open, (so both are open, but the old one was opened second), the old version will be getting the values and named ranges from the newer version--and thus report incorrect numbers and ranges.  This is bad.  I need a good way to prevent the old sheet from accessing the named ranges from the new one, but I can only modify the new one.  The only thing I can think of is to rename all the named ranges and update a lot of VBA code in the new version--something that is prone to errors and sounds like excessive work.

Any suggestions?  For example, is it possible to at least show the user a warning when the second file gets opened?  Or is it possible to use VBA to restrict the scope of the named ranges?  Any other ideas?