Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The AddinInstance Events

Now that we've created, installed and tested a simple Hello World COM Add-in, it's time to take a more detailed look at how Excel interacts with the COM Add-in, using the Designer's code module. During the life of a session, Excel tells us what's going on by raising events through the Designer's AddinInstance object, in exactly the same way that it raises events through the Workbook object of a workbook's ThisWorkbook code module. These events are listed below, in the order in which they're fired during a typical Excel session.

Initialize

Private Sub AddinInstance_Initialize()

The Initialize event is fired when the class is first instantiated and is exactly the same as the Class_Initialize event of a class module. It's rare to see this event used.

OnConnection

Private Sub AddinInstance_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As _
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)

Every COM Add-in uses the OnConnection event, which is raised by Excel during its startup processing, when a demand-loaded add-in is loaded (see the Command Bar Handling section later) or when the add-in is enabled in the COM Add-ins dialog. This is the equivalent of the VBA Auto_Open or Workbook_Open procedures and should be used to initialize the project, set up menu items and so on. This procedure should not display any forms or show any message boxes.Command Bar Handling section later). The AddInInst object also enables us to expose the functions in our add-in to VBA, by including the following line:

AddInInst.Object = Me

in the OnConnection procedure. Any Public properties and methods we include in the Designer class can then be called from VBA using code like this:

'Run the SomeSub() procedure in the Hello World COM Addin
Application.COMAddins("HelloWorld.Connect").Object.SomeSub

The custom() parameter allows the COM Add-in host application to send extra information to a COM Add-in. For Excel, the first element of the custom() array is used to tell us how Excel was started: 1 = Opened from the user interface, 2 = Opened as an embedded object, 3 = Opened through automation.

OnStartupComplete

Private Sub AddinInstance_OnStartupComplete( _
custom() As Variant)

Excel raises the OnConnection event within its startup processing, as it encounters a COM Add-in to open. The OnStartupComplete event is raised after Excel has completed its startup processing, just before returning control to the user. This is the place to display any forms you want to show on startup, such as "Thanks for Installing" or "Did You Know?" dialogs. In practice, it is rarely used.

OnAddInsUpdate

Private Sub AddinInstance_OnAddInsUpdate( _
custom() As Variant)

The OnAddInsUpdate event is raised whenever another COM Add-in is loaded or unloaded (although we're not told which, or whether it's just been loaded or unloaded). We've never seen it used.

OnBeginShutdown

Private Sub AddinInstance_OnBeginShutdown( _
custom() As Variant)

The OnBeginShutdown event is raised when Excel starts its shutdown processing, if the add-in is loaded at the time. It is not called if the add-in is unloaded using the COM Add-ins dialog. We've never seen it used.

OnDisconnection

Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)

The OnDisconnection event is raised when Excel shuts down or when the add-in is unloaded using the COM Add-ins dialog. This procedure is where you should put your add-in's shutdown code, such as tidying up menus and so forth.Command Bar Handling section later).

Terminate

Private Sub AddinInstance_Terminate()

The Terminate event is fired when the class is destroyed and is exactly the same as the Class_Terminate event of a class module. It's rare to see this event used.