COM Add-ins
COM Add-ins present the developer with a way to extend the functionality of Office applications for custom tasks. COM Add-ins are typically used to automate Excel in response to a click of a CommandBar button, a form or dialog box, or some other event specific to Excel such as opening or closing workbooks or entering data on worksheets. COM Add-in functions cannot be directly called from cell formulas in worksheets.
A COM Add-in is an in-process COM server (an ActiveX DLL) that must implement the
IDTExensibility2 interface. All COM Add-ins must implement each of the five methods of this interface:
OnConnection,
OnStartupComplete,
OnAddinsUpdate,
OnBeginShutDown, and
OnDisconnection.
When a COM Add-in is installed on a user's system, registry entries are created for the Add-in. In addition to normal COM registration, a COM Add-in is registered for each Office application in which it runs. COM Add-ins used by Excel are registered in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\
This key contains a subkey for each installed COM Add-in. The name of the subkey is the ProgID for the COM Add-in. The subkey for a COM Add-in also contains values that describe the COM Add-in's friendly name, description, and load behavior. The load behavior describes how the Add-in is loaded in Excel: loaded at startup, loaded at next startup only, loaded on demand, or not loaded.
COM Add-ins can also be loaded and unloaded through the Excel user interface. To do this, follow these steps:
- On the View menu, point to Toolbars and then click Customize.
- In the Toolbars dialog box, click the Advanced tab. In the list of categories, select Tools. Locate COM Add-ins in the list of commands and drag the command to a menu or CommandBar of your choice. Close the Toolbars dialog box.
- Click the COM Add-ins command that you added to display the COM Add-ins dialog box. The dialog box lists all COM Add-ins that are installed on your system, and the COM Add-ins that are currently loaded are selected.
COM Add-ins can also be loaded and unloaded through the Excel user interface. To do this, follow these steps:
- Click the Microsoft Office Button, and then click Excel Options.
- Click Add-Ins.
- Under Manage, click COM Add ins, and then click Go .
The COM Add-ins dialog box lists all COM add-ins that are installed on the computer. The COM add-ins that are currently loaded are selected.
For additional information about COM Add-ins, click the following article numbers to view the articles in the Microsoft Knowledge Base: 238228 HOWTO: Build an Office 2000 COM Add-In in Visual Basic
230689 SAMPLE: Comaddin.exe Office 2000 COM Add-In Written in Visual C++
For more information, see the following Microsoft Web site:
Automation Add-ins
In addition to COM Add-ins, Excel supports Automation Add-ins. Automation Add-ins build on COM Add-ins in that functions in Automation Add-ins can be called from formulas in Excel worksheets. COM Add-ins must be in-process COM servers that support the
IDTExtensibility2 interface; however, Automation Add-ins can be in-process or out-of-process COM servers and implementation of
IDTExtensibility2 is optional.
To use functions from an Automation Add-in in Excel, follow these steps:
- On the Tools menu, click Add-Ins.
- In the Add-Ins dialog box, click Automation. From the list of registered COM servers, select your Automation Add-in and click OK.
- The Automation Add-in appears in the Add-ins dialog box. Click OK to close the Add-Ins dialog box.
To use functions from an Automation Add-in in Excel 2007 and newer, follow these steps:
- Click the Microsoft Office Button, and then click Excel Options.
- Click Add-Ins.
- Under Manage, click Excel Add ins, and then click Go.
- In the Add-Ins dialog box, click Automation. In the list of registered COM servers, click your Automation Add-in, and then click OK.
The Automation Add-in appears in the Add-ins dialog box. Click OK to close the Add-Ins dialog box.
When you make additions to the list in the
Add-Ins dialog box or when you select and clear Add-ins in the list, Excel stores your changes in the registry. First, Excel uses the following registry setting to determine whether or not an Automation Add-in in the Add-in list is loaded:
Excel 2002
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options
String: OPENx
Sample Value: /A "ServerName.ClassName"
Excel 2003
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
String: OPENx
Sample Value: /A "ServerName.ClassName"
Excel 2007
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
String: OPENx
Sample Value: /A "ServerName.ClassName"
Note: Change the Office version number based upon the version you are using.
The
/A switch that is used in the string value is new to Excel and older and is used specifically to load Automation Add-ins. All Automation Add-ins are loaded on demand; there is no setting that can change the load behavior for an Automation Add-in.
When an Automation Add-in that is listed in the
Add-Ins dialog box is cleared, a subkey with a name equal to the Add-in's ProgID is created in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Add-in Manager
Excel 2003:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Add-in Manager
This registry setting ensures that Automation Add-ins that you have added to the Add-ins list are retained in the list even when you have chosen not to load them.
For more information about Automation Add-Ins, see the following articles in the Microsoft Knowledge Base:
285337 HOWTO: Create a Visual Basic Automation Add-in for Excel Worksheet Functions
For more information, see the following Microsoft Web site:
Automation Add-ins That Implement IDTExtensibility2
As previously mentioned, an Automation Add-in may implement
IDTExtensibility2, but it is not required in order for Excel to call the functions in the Add-in from a worksheet. If you require that your Automation Add-in obtains a reference to the Excel instance, you can implement
IDTExtensibility2 and use the
Application parameter of
OnConnection to automate Excel.
An Automation Add-in that implements
IDTExtensibility2 can be loaded in the Excel user interface through both the
COM Add-Ins dialog box and the
Add-Ins dialog box. The following describes the behavior of an Automation Add-in based on whether it is loaded in one or both of these dialog boxes:
- Loaded only in the Add-ins dialog box.
The Add-in is loaded on demand. Functions in the Add-in may be called from formulas in a worksheet. - Loaded only in the COM Add-ins dialog box.
The Add-in is loaded as a COM Add-in and its load behavior is determined from settings in the registry. Functions in the Add-in cannot be called from formulas in a worksheet. - Loaded in both the COM Add-ins dialog box and the Add-ins dialog box.
Two separate instances of the Add-in are loaded. One instance is loaded as a COM Add-in and the other instance is loaded as an Automation Add-in. The COM Add-in instance uses the load behavior indicated in the registry; the Automation Add-in instance loads on demand. The two instances work independently of one another and do not share global variables.
Because Automation Add-ins are loaded on demand, Excel may attempt to load the Add-in while it is in cell edit mode. Therefore, when you develop an Automation Add-in that supports
IDTExtensibility2, you should be careful not to do anything that attempts to change Excel's state while the Add-in loads. For more information, see the following article in the Microsoft Knowledge Base:
284876 BUG: Excel Fails When Automation Add-In Loads
(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.