Excel COM add-ins and Automation add-ins

Article translations Article translations
Article ID: 291392 - View products that this article applies to.
This article was previously published under Q291392
Expand all | Collapse all

Summary

 Microsoft Office Excel support Automation Add-ins in addition to Component Object Model (COM) Add-ins. This article explains the differences between these two types of Add-ins. 

More information

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:  
  1. On the View menu, point to Toolbars and then click Customize.
  2. 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.
  3. 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:  
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. Click Add-Ins.
  3. 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:
Office Add-ins
http://support.microsoft.com/ofd

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:
  1. On the Tools menu, click Add-Ins.
  2. In the Add-Ins dialog box, click Automation. From the list of registered COM servers, select your Automation Add-in and click OK.
  3. 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:  
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. Click Add-Ins.
  3. Under Manage, click Excel Add ins, and then click Go.
  4. 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:
Office Add-ins
http://support.microsoft.com/ofd

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.

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 291392 - Last Review: October 29, 2013 - Revision: 1.0
Applies to
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbautomation kbinfo KB291392

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com