XL97: WorkBookBeforeClose Event Fires Unexpectedly

When you use a Visual Basic for Applications macro to update a formula in a Microsoft Excel worksheet, the WorkbookBeforeClose event may fire.

This problem may occur when the following conditions are true:

• The macro is updating two or more Excel workbooks that have been inserted into another program (such as Microsoft Word). -and-
  
  
• The macro updates a formula that represents an OLE link between the Excel workbooks. -and-
  
  
• You have created an Application-level event handler for the WorkbookBeforeClose event.

To correct this problem, obtain Microsoft Excel 97 Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Office 97 Service Release 2 (SR-2).

Example of the Problem

The following steps illustrate how to reproduce the problem.

Create the WorkbookBeforeClose Event Handler

1. Start Excel.
2. If you do not have a Personal Macro Workbook (Personal.xls), the following steps illustrate how to create one. Proceed to Step 3 if you already have a Personal Macro Workbook.
   
   
   1. On the Tools menu, point to Macro, and then click Record New Macro.
   2. In the Store Macro In box, click Personal Macro Workbook, and then click OK.
   3. On the Tools menu, point to Macro, and then click Stop Recording.
3. Start the Visual Basic Editor (press ALT+F11).
4. Press CTRL+R to activate the Project Explorer Window.
5. In the Project Explorer window, click to select "VBAProject (PERSONAL.XLS)."
6. On the Insert menu, click Class Module to insert a class module.
7. Type the following code into the class module:

        Public WithEvents App As Application
        Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, _
        Cancel As Boolean)
   
           MsgBox "App_WorkbookBeforeClose"
   
        End Sub
   						

8. In the Project Explorer window of the Visual Basic Editor, double-click ThisWorkbook in the current project.
9. Type the following code into the ThisWorkbook module sheet:

        Dim X As New Class1
        Private Sub Workbook_Open()
   
           Set X.App = Application
   
        End Sub
   						

10. On the Insert menu, click Module to insert a Visual Basic module sheet.
11. Type the following code into the module sheet:

         Sub Test_BeforeClose()
    
            Dim xFormula As String, i As Integer, j As Integer
    
            Windows("Worksheet in BeforeCloseTest.Doc").Activate
    
            i = Workbooks("Worksheet in BeforeCloseTest.Doc 2") _
               .Worksheets("Sheet1").Range("A100").End(xlUp).Row + 1
    
            Range("A1").Name = "RefCopy" & I
    
            Range("A1").Copy
    
            Windows("Worksheet in BeforeCloseTest.Doc 2").Activate
    
            Range("A" & i).Select
    
            ActiveSheet.Paste Link:=True
    
            xFormula = ActiveCell.Formula
    
               For j = Len(xFormula) To 1 Step -1
    
                  If Mid(xFormula, j, 1) = "!" Then Exit For
    
               Next j
    
            ActiveCell.Formula = Left(xFormula, j) & "RefCopy" & i & "'"
    
         End Sub
    						

12. On the File menu, click "Close & Return to Microsoft Excel".
13. On the File menu, click Exit. Click Yes when you are prompted to save the changes in the Personal Macro Workbook.

Inserting Excel Objects into a Word Document

1. Start Word.
2. On the Insert menu, click Object. Click Microsoft Excel Worksheet in the Object Type box, and then click OK.
   
   An Excel worksheet object is inserted into your document.
3. Click the Word document to activate Word.
4. On the Insert menu, click Object. Click Microsoft Excel Worksheet in the Object Type box, and then click OK.
   
   An Excel worksheet object is inserted into your document.
5. Click the Word document to activate Word.
6. On the File menu, click Save As. Type BeforeCloseTest.doc into the File Name box, and then click OK.

Running the Macro that Illustrates the Problem

1. Right-click the first embedded Excel object, point to Worksheet Object on the shortcut menu, and then click Open.
2. Activate Word.
3. Right-click the second embedded Excel object, point to Worksheet Object on the shortcut menu, and then click Open.
4. On the Tools menu, point to macro, and then click Macros. Click Personal.xls!Test_BeforeClose, and then click Run.
   
   An OLE link is created between cell A1 of the "Worksheet in BeforeCloseTest.doc" workbook and cell A2 of the "Worksheet in BeforeCloseTest.doc 2" workbook
5. Activate the "Worksheet in BeforeCloseTest.doc" workbook.
6. Type test into cell A1.
7. On the Tools menu, point to macro, and then click Macros. Click Personal.xls!Test_BeforeClose, and then click Run.

A message box is displayed that indicates that the WorkbookBeforeClose event has fired. The WorkbookBeforeClose event will fire each time you run the Test_BeforeClose procedure.