XL97: Error Saving or Closing Files with Defined Names

• Microsoft Excel 97 for Windows

SYMPTOMS

When you close or save a workbook in Microsoft Excel 97, you may receive the following error message:

   This program has performed an illegal operation and will be shut down.
   If the problem persists, contact the program vendor.

   EXCEL caused an invalid page fault in module EXCEL.EXE at
   0137:301e048f.

   EXCEL caused an invalid page fault in module EXCEL.EXE at
   0137:30518c8e.

CAUSE

This problem may occur when all of the following conditions are true:

• You create two workbooks in Microsoft Excel 97, and the workbooks are open.

  For example, you create workbooks Book1 and Book2.

      -and-
  

• In one workbook (Book1), you create a defined name (for example, Name1) that references a relative cell address in the other workbook (Book2).

      -and-
  

• In the workbook (Book1) that contains the defined name (Name1), you create another defined name (for example, Name2) that uses the first defined name (Name 1) in a function.

  For example, you create the following names in Book1.

        Defined name   Refers to                      Examples
        --------------------------------------------------------------------
  
        Name1          A relative or a mixed          =[Book2]Sheet1!A1:B5
                       relative cell range            =[Book2]Sheet1!$A1:$B5
                       in another open                =[Book2]Sheet1!A$1:B$5
                       workbook (Book2)
  
        Name2          Name1 in a function            =AVERAGE(Name1)
                                                      =SUM(Name1)
  
      -and-
  
  

• In the workbook (Book1) that contains the defined names, you enter a formula that refers to the defined name that uses the first defined name in a function (Name2).

  For example, you enter the following function:

        =Name2
  

      -and-
  

• You attempt to save Book1, or you close Book2.

RESOLUTION

To work around this problem, do not define a name that refers to another defined name that refers to a relative or mixed relative cell range in another workbook. If possible, do not use relative or mixed relative cell references in defined names that refer to other workbooks.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.