Knowledge Base

BUG: COM Add-In unloads before the application is shut down in Microsoft Excel

Article ID: 831200

Article Last Modified on 12/18/2003

APPLIES TO

Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition
Microsoft Excel 2000 Standard Edition

SYMPTOMS

When you work with a Microsoft Excel application, you can extend application functionality by using a custom Component Object Model (COM) Add-In. The add-in can be set to load at startup. The add-in remains loaded until the application is shut down. However, if Excel is started for OLE or for remote Automation, and the application is made visible to you, when you try to close the Excel window, the Excel window is only hidden, and the application remains running. When this behavior occurs, the COM Add-In is unloaded as if the application has been shut down. The OnBeginShutdown event and the OnDisconnection event are called, and the add-in is removed from memory. If the application is made visible again, the COM Add-In is no longer loaded. The menu items or the toolbar items that were added by the COM Add-In may no longer be available. You may be unable to use the COM Add-In again in that instance of Excel.

CAUSE

Excel is designed to remain loaded until all external connections have been released. If you manually close the Excel window, but an external OLE host or an Automation host still has a reference count to the application, Excel hides but continues to run. This behavior is intentional, and this behavior follows the correct COM rules about object lifetime. However, an internal event is triggered during this process that can cause Excel to start a shutdown and then to unload all COM Add-Ins before Excel determines that the application will not be closed because of the external references. Because of this problem, COM Add-Ins can be prematurely unloaded before the actual application termination.

STATUS

Microsoft has confirmed that this is a bug in Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003.

MORE INFORMATION

Steps to Reproduce the Problem

The following steps demonstrate the problem by using a Microsoft Visual Basic 6.0 Add-In and Internet Explorer. However, you may experience this problem in a number of other ways. Other add-ins, other OLE clients, and other Automation clients may also experience the same problem. The impact of the problem varies and depends on the add-in design and the implementation.

Create the test files in Excel

Start Excel 2000, Excel 2002, or Office Excel 2003. Create three new workbooks.
Add data to the first two workbooks, and then save the workbooks as TestBook1.xls and TestBook2.xls.

Note Save the workbooks to a location such as your Desktop or My Documents that you can easily access from Windows Explorer.

On the Insert menu for the third workbook, click Hyperlink. Locate TestBook1.xls in the Insert Hyperlink dialog box, and then click OK to add a link to TestBook1.xls.
On the File menu, click Save As, and then save the third workbook as a Web page. Save the third workbook as TestPage.htm. Put TestPage.htm in the same location as the other workbooks.
Close all the documents. Quit Excel.

Create a Visual Basic COM Add-In for Excel

Start Visual Basic 6.0, and then select Addin as the project type.

The Connect designer class and the frmAddin form are added automatically.
Change the project name to MyExcelAddin.
Open the Designer window for the Connect class, and then select Microsoft Excel from the Application drop-down list. In the Initial Load Behavior drop-down list, select Startup.
Remove frmAddin from the project.
In the Project window, right-click Connect item and select view code.

Remove all the code in the Designer code window, and then replace that code with the following code:

Option Explicit

Dim WithEvents m_oCommandBarButton As Office.CommandBarButton

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
    Dim oCommandBar As Office.CommandBar
    Dim oButton As Office.CommandBarButton
    On Error Resume Next
    ' Get the Standard toolbar for Excel to add the button.
    Set oCommandBar = Application.CommandBars.Item("Standard")
    ' Try to connect to an existing item.
    Set oButton = oCommandBar.Controls("My Button")
    ' If this fails, you do not have one yet. Try to make one.
    If Err Then
        Err.Clear
        Set oButton = oCommandBar.Controls.Add(1)
        If Not Err Then
            oButton.Caption = "My Button"
            oButton.Style = msoButtonCaption
            oButton.Tag = "MyButtonTag"
        End If
    End If
    'Display an error if button creation fails.
    If Err Or oButton Is Nothing Then
        MsgBox "Error loading add-in: " & Err.Description, _
            vbCritical Or vbMsgBoxSetForeground
    Else ' Otherwise, you set the button to handle the events.
        Set m_oCommandBarButton = oButton
    End If
End Sub

Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode _
    As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    On Error Resume Next
    MsgBox "The Add-In is being disconnected now.", _
        vbInformation Or vbMsgBoxSetForeground
    Set m_oCommandBarButton = Nothing
End Sub

Private Sub m_oCommandBarButton_Click(ByVal Ctrl As _
    Office.CommandBarButton, CancelDefault As Boolean)
    On Error Resume Next
    MsgBox "This event was handled by the COM Add-In.", _
        vbInformation Or vbMsgBoxSetForeground
End Sub

On the File menu, click Save, and then click Make MyExcelAddin.dll.

Visual Basic registers the add-in.

Run the test page to reproduce the problem

Close all applications. Make sure that Excel is not running.
Locate the TestPage.htm file that you saved earlier, and then open the TestPage.htm file in Internet Explorer.
Click the hyperlink.

By default, Excel for OLE in-place activation starts, and TestBook1.xls opens in the Web browser. If TestBook1.xls does not open in the Web browser, you can read the following article.

For additional information about how to reconfigure Internet Explorer to in-place activate XLS files, click the following article number to view the article in the Microsoft Knowledge Base:

259970 PRB: In-Place Activating Document Servers in Internet Explorer
Continue to let Internet Explorer run, find TestBook2.xls in Windows Explorer, and then double-click TestBook2.xls to open this file outside the Web browser.
On the Standard toolbar, click My Button.

You receive a message from the add-in that tells you that the event was handled.
Switch back to Internet Explorer, and then click Back to move away from the embedded Excel file.

The workbook window is closed, but the reference to Excel is kept. Internet Explorer caches the object.
Switch back to Excel, and then close TestBook2.xls.
On the File menu, click Exit.

You receive a message from the add-in that the add-in is being unloaded. Excel hides the Excel main window. However, Excel continues to run.
Find TestBook2.xls again, and then double-click TestBook2.xls to reopen the file in Excel.
On the Standard toolbar, click My Button.

Nothing occurs because the add-in was previously unloaded.

Keywords: kbbug kbpending KB831200

Microsoft KB Archive/831200

Contents