Creating Automation and COM Add-Ins | Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

< Day Day Up >

There are times when it isn't desirable to create an add-in using Visual Basic for Applications. Because VBA macros are interpreted, they execute slower than compiled code, which can seriously affect the performance of computation-intensive add-ins. Also, some functions aren't easily implemented using VBA, such as image processing or specialized networking applications.

To address this limitation, all Office applications including Excel have the ability to access an add-in stored in an external DLL or EXE file. Although there are two different techniques used to access the external add-ins, both are created with similar programming techniques.

COM add-ins are typically used to respond to clicking a command button or to some other Excel event such as opening or closing a workbook. Unlike Excel add-ins, you can't create a COM add-in that can be used as part of a cell's formula. Automation add-ins are less restricted and can be used to respond to Excel events as well as to provide functions that can be used in a formula.

COM add-ins are defined only through the COM Add-Ins dialog box on the toolbar, whereas automation add-ins can be defined through the COM Add-Ins dialog box, the Add-Ins dialog box, or both.

EXE or DLL?

COM add-ins can be created as either a DLL or an EXE file. Both have their advantages and disadvantages. Generally, a DLL component will perform better than an EXE component because the DLL is run in-process, which avoids the extra overhead associated with calling an out-of-process component.

However, a DLL component can't include a main program, which can be a serious limitation if you have a utility that you want to access either as a stand-alone program or as an Office add-in. An EXE component also has the advantage of isolating the add-in's execution from that of the host application.

So, unless you really need either the isolation or main program feature of an EXE component, you should develop your add-in as a DLL component.

Another difference between a COM add-in and an automation add-in is that COM add-ins must support the IDTExtensibility2 interface, whereas automation add-ins might or might not use this interface.

Tip

Displaying the COM Add-ins dialog box
Accessing the COM Add-ins dialog box can be tricky because the only way to access this dialog box is by adding the COM Add-Ins button to the toolbar. Right-click a toolbar, and choose the Customize command (lists at the bottom of the context menu). Click the Commands tab. Select Tools in the Category list, and then drag COM Add-Ins from the Commands list to a toolbar. Then click the COM Add-Ins button to display the COM Add-Ins dialog box.

Using the IDTExtensibility2 Interface

The IDTExtensibility2 interface is a general interface that's shared by many products besides Microsoft Office. This interface represents a standard way for an application to communicate with an add-in. Any COM add-in must implement each of the methods listed in Table 11-4.

Table 11-4: *IDTExtensibility*2 Interface Methods
Method	Description
OnAddInsUpdate	Called when a change occurs to the list of add-ins, such as an add-in being loaded or unloaded.
OnBeginShutdown	Called when the application is being shut down. Remember that this method will be called only if the add-in is loaded.
OnConnection	Called when the add-in is loaded into the application. This can occur when the add-in is loaded through the Add-Ins dialog box or if the add-in is automatically loaded when the application starts.
OnDisconnection	Called when the add-in is unloaded from the application.
OnStartupComplete	Called when the application has completed the startup process.

The OnConnection method is called when an add-in is loaded into the application. The add-in receives information about the environment it's running in through a set of parameters. The two key parameters are an object reference to the application object associated with the application and information about how the add-in was started (that is, was the add-in started from the COM Add-Ins dialog box or when the application was first started).

If the OnConnection method returns successfully, the add-in is considered by the application to be loaded. If it returns an error message, the application destroys the object associated with the add-in.

Tip

Initializing the Add-In
You should use the OnConnection method to initialize the add-in and acquire any resources needed by the add-in. Then these resources can be released in the OnDisconnection method.

The OnDisconnection method is called when the application wants the add-in to unload itself. The application informs the add-in why the application is being unloaded (that is, the add-in was unloaded via the COM Add-Ins dialog box or the application itself is shutting down).

The OnStartupComplete and OnBeginShutdown methods are called just before the application is ready to accept user input and just after the user has requested the application to close.

Tip

Making Changes to Excel
You should use the OnStartupComplete method to make any changes to the application, such as adding new menu items or toolbar buttons. Then you can undo these changes in the OnBeginShutdown method.

The OnAddInsUpdate method is called whenever the list of add-ins changes.

Warning

You should be extremely careful about making changes to Excel when loading an add-in because Excel has the ability to dynamically load an add-in while in cell edit mode. Displaying a form or dialog box, changing the current selection, calling a property or method in the Windows collection, or changing some Excel settings could cause Excel to fail.

Registry Keys

COM add-ins are required to provide certain entries in the Windows registry. All registry entries are stored using the following key, where App is the name of the Office application (such as Excel) and ProgID is the ProgID value associated with the add-in. Typically, these entries are made by the add-in's installation program.

HKEY_CURRENT_USER\Software\Microsoft\Office\App\Addins\ProgID

Warning

Manually changing the Windows registry can be dangerous. If you don't have a lot of experience editing the Windows registry, you should look but don't touch, and rely on the setup program for the add-in to make the proper changes. Should you wish to view and/or change registry entries, you can use the RegEdit program. (Click the Start button, click Run, type RegEdit, and click OK.)

Underneath the key specified above are a series of subkeys that contain specific information about the add-in. The LoadBehavior subkey is a DWORD value that determines when an add- in is loaded by the Office application. A value of 0 means that the add-in is not loaded. A value of 3 means that the add-in should be loaded when the application starts. A value of 9 means that the add-in is loaded when requested by the user. A value of 16 means that the add- in is loaded once, the next time the application starts.

The Description subkey is a String value that's displayed in the COM Add-Ins dialog box, whereas the FriendlyName subkey is a String value that's returned by the add-in's Description property.

Add-ins that use the IDTExtensibility2 interface also should have a registry entry named CommandLineSafe. This is a DWORD value that marks an add-in as safe to use in applications that don't support a user interface. A value of 0x00 means that the add-in needs a user interface, whereas a value of 0x01 means that the add-in doesn't rely on a user interface.

< Day Day Up >