Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Command Bar Handling

Using Command Bar Event Hooks

Toward the end of Chapter 8 Advanced Command Bar Handling, we explained the difference between using the CommandBarControl's OnAction property to call our procedures and using class modules to hook the CommandBarButton's Click event or the CommandBarComboBox's Change event. When creating COM Add-ins, we have to use the event-hook method for all our controls. It would be a good idea to reread that section of Chapter 8, but to summarize it we need to do the following:

Give all our menu items the same Tag property, to uniquely identify them as belonging to our add-in.

Give each menu item a unique Parameter property, to identify them in code.

Have a class module containing a With Events declaration for a CommandBarButton (and/or CommandBarComboBox).

In the class's CommandBarButton_Click event procedure, confirm that the Tag is set to ours then call the procedure appropriate to the Parameter value.

When setting up our menus, create a new instance of the class for each combination of ID and Tag that we use. If we're not using any built-in menu items, we would only need a single instance of the class.

Permanent vs. Temporary Menu Items

In an Excel add-in, we can respond to the AddInInstall event to permanently add our menu items to Excel (by setting the temporary parameter to False when adding them) and respond to the AddInUninstall event to remove them. In the meantime, they will always be available to the user, will feature in their usage counting (if they've elected to show partial menus) and can be moved around, copied to other toolbars and so on. This is the preferred method for general-purpose add-ins that might be expected to always be installed.

Alternatively, we could choose to add our menu items on a temporary basis, in which case we would add them in the Workbook_Open event and remove them in the Workbook_BeforeClose event. This is the preferred method for application-specific add-ins, where the menu items would typically be contained in their own command bar and we wouldn't want the user to move them around.

COM Add-ins differ from normal Excel add-ins in that we aren't told when the add-in is installed or uninstalled (particularly uninstalled); we can only infer it from the value we gave the Initial Load Behavior setting and the parameters passed to the OnConnection and OnDisconnection events.

A Permanent-Menu Architecture

When using permanent menus with COM Add-ins, we set the Initial Load Behavior in the Designer to Load at next startup only. This means Excel will load the add-in the next time it starts, run the OnConnection procedure, then set the load behavior to Load on demand.

In the OnConnection procedure, we need to check the ConnectMode parameter. If it is ext_cm_Startup, we create our command bars and menu items then set up the event hooks for them. If the ConnectMode is ext_cm_AfterStartup, our menu items should already be there, so we only need to set up the event hooks. We should include some code to check whether any of our menu items have been removed and add them back.

In the OnDisconnection procedure, we need to check the RemoveMode parameter. If it is ext_dm_UserClosed, the user has unticked the add-in in the COM Add-ins dialog, so we should delete our menu items. If the RemoveMode is ext_dm_HostShutdown, we don't need to do anything.

When creating our menu items, we need to tell Excel that the menu belongs to our COM Add-in. We do that by setting the command bar control's OnAction property to !<ProgID>, where the ProgID can be obtained from the AddInInst object passed to the event. This tells Excel which add-in owns the control so it loads the add-in and calls the OnConnection event (if necessary), allowing the add-in to set up the event hooks, then raises the Click or Change event.

The code for a permanent-menu architecture is shown in Listing 21-4.

Listing 21-4. A Permanent-Menu Architecture

'Run when the add-in is loaded
Private Sub AddinInstance_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As _
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)
Dim sOnAction As String
'Store a reference to the application object
Set gxlApp = Application
'If we're starting up, it must be the first time,
'so create our menu items permanently
If ConnectMode = ext_cm_Startup Then
'Get the ProgID from the AddInInst object
sOnAction = "!<" & AddInInst.ProgId & ">"
'Create our menus, passing the OnAction string
CreateMenus sOnAction
End If
'Whether at startup or after startup,
'we have to set up our event hooks
HookMenus
End Sub
'Run when the add-in is unloaded
Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
'If the user chose to uninstall the add-in,
'remove our menus
If RemoveMode = ext_dm_UserClosed Then
RemoveMenus
End If
'Tidy up our application reference
Set gxlApp = Nothing
End Sub

The problem with using a permanent-menu design with COM Add-ins is that it is quite likely the add-in would be uninstalled while Excel is closed. As COM Add-in DLLs need to be registered on the user's computer, it is common practice to distribute them using a proper setup file. The setup file will usually include an Uninstall option and/or include the add-in in the user's Add/Remove Programs list. If our users were to use this option to uninstall the add-in, the add-in's menu items would remain, orphaned. This risk has to be weighed against the benefit of allowing the user to move the menus around, create copies and so on. In practice, temporary menu architectures are the most common.

A Temporary-Menu Architecture

A temporary-menu architecture is much simpler. We set the Initial Load Behavior in the Designer to StartUp, so the add-in is loaded every time Excel starts. In the OnConnection event, we always re-create our menus and set up the event hooks. In the OnDisconnection event, we always remove them. If we're creating our own command bars, we need to store their visibility, docked state and position before removing them and make sure they're added back in the same state. Because the add-in will always be open, we do not need to set the OnAction property. Even though we're removing our menu items in the OnDisconnection event, it is good practice to add them with the temporary parameter set to True. The code for a temporary-menu architecture is shown in Listing 21-5.

Listing 21-5. A Temporary-Menu Architecture

'Run when the add-in is loaded
Private Sub AddinInstance_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As _
AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)
'Store a reference to the application object
Set gxlApp = Application
'Always create our menu items
CreateMenus
'Set up our event hooks
HookMenus
End Sub
'Run when the add-in is unloaded
Private Sub AddinInstance_OnDisconnection( _
ByVal RemoveMode As _
AddInDesignerObjects.ext_DisconnectMode, _
custom() As Variant)
'Always remove our menus
RemoveMenus
'Tidy up our application reference
Set gxlApp = Nothing
End Sub

Custom Toolbar Faces

A COM Add-in does not have a worksheet handy to store the pictures and masks that we need for custom toolbar faces. Instead, we store the bitmaps in a resource file within the COM Add-in project and use LoadResPicture to retrieve the image when needed. For more information about the use of resource files in VB6, see Chapter 20 Combining Excel and Visual Basic 6. For Excel 2002 and 2003, we set the Picture and Mask directly, but Excel 2000 gives us more of a problem. When stored in resource files, the bitmaps lose the transparency that we could give them when they were stored in a worksheet. When we Copy/PasteFace the picture onto an Excel 2000 toolbar and disable the button, the image usually turns into an unidentifiable gray blob. This can be worked around by using API calls to create a transparent bitmap during the Copy/Paste procedure and is documented in Microsoft KB article 288771 at [ http://support.microsoft.com/?kbid=288771 ].

The Paste Special Bar COM Add-in

In Chapter 8 Advanced Command Bar Handling, we used a Paste Special command bar to demonstrate the concept of hooking command bar button events. The workbook is called PasteSpecialBar.xls and is located on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder. To demonstrate a working temporary-menu architecture in a COM Add-in and the use of custom toolbar faces, we have converted the workbook to a COM Add-in. The code for it can found in the \Concepts\Ch21Writing Add-ins with Visual Basic 6\PasteSpecialBarVB6 folder. The READ_ME module lists the changes that were made to convert the Excel add-in to a COM Add-in, while each module lists the changes that were required in the module header. The changes are summarized below:

Use the Designer and OnConnection / OnDisconnection instead of Auto_Open / Auto_Close.

Add a global variable to store a reference to the Excel.Application object and use that variable whenever referring to any of Excel's global objects.

Remove all the code for the table-driven command bar builder, replacing it with a simpler procedure to add the toolbar buttons individually.

Copy the custom toolbar images to a resource file and use them instead of the PastePicture module.

Add the MCopyTransparent module, to handle copying transparent bitmaps for Excel 2000.

Change the values of a few of the global variables, so the Excel add-in and COM Add-in can coexist in Excel.

Tidy up a few minor references to ThisWorkbook.

It is interesting to note that the only change required to the add-in's payload of performing the Paste Special was to prefix CommandBars and Selection by our global Application object variable. If you're still unsure of the differences and similarities between Excel and COM Add-ins, open both versions of the Paste Special Bar and compare them. Both add-ins do exactly the same things in exactly the same ways, using the same module and procedure names, except where noted in the comments. Familiarizing yourself with both versions of the add-in will also prepare you for the next chapter, in which we convert it to a Visual Studio Tools for Office (VSTO) solution, using Visual Basic.NET.