views:

381

answers:

2

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?

+1  A: 

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.

DanM
Good idea. However, I am having trouble getting this event handler to run.
Kimball Robinson
Hmm...which version of Excel are you using? In 2007, if I put `Private Sub Workbook_Open()` `MsgBox ("Opening!")` `End Sub` in my ThisWorkbook, save and exit Excel, re-open the workbook, and enable macros, the message box appears every time.
DanM
I got it working--I had to restart excel to have the event start working. Thanks!! As an interesting side note, as far as I can tell, the newer version doesn't need to check workbooks that are already open--the first workbook opened gets precedence anyway (so if the older version is opened first, the named ranges work correctly for it, and the new version is designed correctly so it works) (Thus, this is only a problem if the old versions get opened second). So I only need to set up the function to check workbooks that open after the most-updated one.
Kimball Robinson
Yes, that makes sense. If the user opens Old then New, Old will refer to Old (first opened) and New will refer to New (fully qualified references)--so no problem. If user opens New then Old, user needs to be warned. If you want to go even further, you could show the warning then ask the user if it's okay to close and reopen New to solve the problem. If there are unsaved changes to New, the user will need to either save, not save, or cancel.
DanM
A: 

[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.

Ryan Shannon
Fully qualifying all names would definitely be a good idea for all future versions, but if I'm understanding the OP correctly, there are some old version already out there with macros that cannot be changed (they are already on the user's machine).
DanM
I already described how I essentially do the same thing for the newer workbook version. See the second code example I pasted above--my code sample has the added bonus that it doesn't rely on knowing the worksheet where the named range resides.Also, DanThMan is right--I need something to work for me retroactively, or at least warn users of the problem.
Kimball Robinson
I can already check for overridden named ranges with "if Range("nameXYZ").Value <> ThisWorkbook.Names("nameXYZ").RefersToRange.Value" in a for loop on "for each nm in thisworkbook.names". And once again, I simply cannot make this work retroactively, and finally I reiterate that the second code sample in my original post circumvents the problem in the newer version--thus, I do not NEED to detect duplicates. In other words, I appreciate your efforts, but you are not solving the correct problem.
Kimball Robinson