Duplicate named ranges between workbooks causes my custom functions to get confused

Question

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?

Answer 1

You might try handling the WorkbookOpen Event in the "ThisWorkbook" code window. When the new version of the workbook is opened, you can loop through all the open workbooks and see if any old versions are in the list. If yes, warn the user that values on the older version of the spreadsheet may be incorrect because data is being read off the newer version.

Answer 2

[Edit]

This sub will tell you if you have duplicate named ranges in any open workbooks. As DanThMan said, you can refer to it in the ThisWorkbook object module:

Sub CheckRangeDups()
    Dim wb As Workbook
    Dim nm As Name
    Dim count As Integer, i As Integer
    Dim arrNm() As String
    Dim dup As Boolean
    Dim rngChk As Range

    ReDim arrNm(0)
    'cycle through workbooks'
    For Each wb In Workbooks
        'cycle through names in workbook'
        For Each nm In wb.Names
            'check if name refers to a range'
            On Error Resume Next
            Set rngChk = Nothing
            Set rngChk = nm.RefersToRange
            On Error GoTo 0
            If Not rngChk Is Nothing Then
                dup = False
                'check if name in array of names'
                For i = 0 To UBound(arrNm)
                    If nm.Name = arrNm(i) Then
                        MsgBox "Named range " & nm.Name & " duplicated."
                        dup = True
                        Exit For
                    End If
                Next i
                'if not then add it'
                If Not dup Then
                    arrNm(count) = nm.Name
                    count = count + 1
                    ReDim Preserve arrNm(count)
                End If
            End If
        Next nm
    Next wb
End Sub

[/Edit]

If I understand you right, fully qualifying your references to the ranges will solve your problem. Example: With two files open named file1 and file2, name cell A1 on sheet 1 "arange" in both files. In file1 type "file1" into the cell, on file2 type "file2" into the cell. Now run this:

Sub whichRange()
    Dim f1 As Workbook, f2 As Workbook
    Set f1 = Workbooks("file1.xls")
    Set f2 = Workbooks("file2.xls")
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = f1.Worksheets(1)
    Set s2 = f2.Worksheets(1)

    Dim r1 As Range, r2 As Range
    Set r1 = s1.Range("arange")
    Set r2 = s2.Range("arange")

    Debug.Print "WB: "; f1.Name; " cell: "; r1.Name; " contents: "; r1
    Debug.Print "WB: "; f2.Name; " cell: "; r2.Name; " contents: "; r2
End Sub

Fully qualifying allows Excel to know which you are referring to.