Microsoft KB Archive/176251

From BetaArchive Wiki

Article ID: 176251

Article Last Modified on 9/22/2005



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q176251


SYMPTOMS

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

CAUSE

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.


RESOLUTION

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:

151261 OFF97: How to Obtain and Install MS Office 97 SR-2


STATUS

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

MORE INFORMATION

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.

  1. Click the Word document to activate 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.

  1. Click the Word document to activate Word.
  2. 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

  1. Activate the "Worksheet in BeforeCloseTest.doc" workbook.
  2. Type test into cell A1.
  3. 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.


Additional query words: XL97

Keywords: kbbug kbqfe kbprogramming kbhotfixserver KB176251