Microsoft KB Archive/172709

From BetaArchive Wiki
Knowledge Base


XL97: Error Saving or Closing Files with Defined Names

Article ID: 172709

Article Last Modified on 10/21/2000



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q172709

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.

If you click Details, you receive an error message similar to either of the following:

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.

NOTE: The problem does not occur in earlier versions of Microsoft Excel. Also, the problem does not occur if the range referenced by Name1 uses absolute cell referencing (for example, =[Book2]Sheet1!$A$1:$B$5) or if Name2 does not use Name1 in a function (for example, =Name1, for example).

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.


Additional query words: XL97 ipf gpf

Keywords: kberrmsg kbbug KB172709