United States*
Quick Links|Home|Worldwide
Microsoft*
Search Microsoft.com for:
Help and Support 
|Select a Product|Advanced Search

Excel COM add-ins and Automation add-ins

Article ID:291392
Last Review:January 10, 2007
Revision:4.0
This article was previously published under Q291392

SUMMARY

Microsoft Office Excel 2002 and Microsoft Office 2007 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 2000, Office XP, Office 2003 and Office 2007 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 2002 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 2007 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 (http://support.microsoft.com/kb/238228/) HOWTO: Build an Office 2000 COM Add-In in Visual Basic
230689 (http://support.microsoft.com/kb/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 (http://support.microsoft.com/ofd)

Automation Add-ins

In addition to COM Add-ins, Excel 2002, and Excel 2003 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, 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"
					
The /A switch that is used in the string value is new to Excel 2002 / Excel 2003 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:
Excel 2002:
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
					
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 (http://support.microsoft.com/kb/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 (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 (http://support.microsoft.com/kb/284876/) BUG: Excel Fails When Automation Add-In Loads
(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.


APPLIES TO
Microsoft Office Excel 2007
Microsoft Office Excel 2003
Microsoft Excel 2002 Standard Edition

Back to the top

Keywords: 
kbautomation kbinfo KB291392
 
Provide feedback on this article
Did this article help you solve your problem?
Yes
No
Partially
I do not know yet
Strongly AgreeStrongly Disagree
987654321
The article is easy to understand
The article is accurate
Additional Comments:
To protect your privacy, do not include contact information in your feedback.

Search Support (KB)

 
Switch to Advanced Search

Article Translations

 

Related Support Centers

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.

Page Tools


© 2007 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks |Privacy Statement