Automation Add-ins

Introduced in Excel 2002.

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.
Automation Add-ins can be in-process or out-of-process COM servers and implementation of IDTExtensibility2 is optional.
These are COM add-ins that expose public functions that can be used in Excel formulas.

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.
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:

An Automation add-in provides a way to extend the functionality of Office 2002 and Office 2003.
It is possible to define worksheet functions.
These are COM DLLs (ActiveX DLLs).

Automation add-ins can be in-process or out-of-process COM servers and implementaytion of IDTExtensibility is optional.

If your Automation Add-in needs to obtain a reference to the Excel instance you can implement the IDTExtensibility and use the Application parameter of the OnConnection to automate Excel.

Extending an existing COM Add-in

In the registry editor for the Setup Project we need to create another entry.
You need to create a subkey of the key CLSID with the GUID of the connect class (delimited with braces).


For Example:

Key - HKEY_CLASSES_ROOT\CLSID\ {389DB139-D54D-4cd8-96C8-E8456B732344} 

Set the 'AlwaysCreate' property to TRUE.
Create another subkey of this key with the name Programmable

Key - HKEY_CLASSES_ROOT\CLSID\{389DB139-D54D-4cd8-96C8-E8456B732344}\Programmable 

Set the 'AlwaysCreate' property to TRUE.
There does not have to be any values under this key

Add another attribute to the declaration the connect class.
You need to include a ClassInterface specifying 'AutoDual' as the type.

ProgIdAttribute("TestingExcel.Connect"), _ 
ClassInterface(ClassInterfaceType.AutoDual)> _

Using the AutoDual class interface is usually discouraged because such interfaces allow clients to bind to a specific interface layout that might change as the class evolves, thereby breaking the client.
Instead of using class interfaces you should normally define a custom interface explicitly.

You should use the AutoDual class interfaces only if you understand the limitation.
One consequence of using an AutoDual class interface is that the type information is produced for the interface and published in a type library and the type library is registered. This is essential for an Automation Add-in.

Loading and Unloading

(Tools > Addins) Select Automation button.
This will list all the registered COM servers.

An Automation Add-in that implements the IDTExtensibility2 can be loaded using both the (Tools > Addins) or the (Tools > COM Add-ins) dialog boxes.

There is different behaviour depending on where it is loaded.
1) (Tools > Addins) - The addin is loaded on demand, worksheet functions are allowed.
2) (Tools > COM Addins) - Settings in the registry determine how the addin is loaded, worksheet functions are not allowed.
3) Both (Tools > Addins) & (Tools > COM Addins) - Two separate instances are loaded. They work independently of each other and do not share global variables.

Registry Entries

When you make any changes to the list in the Add-ins dialog box all the changes are stored in the registry.
First Excel use the following registry setting to determine whether or not an Automation Add-in 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"

The /A switch is new to Excel 2002 and Excel 2003 and is used specifically to load Automation addins.
All Automation Addins are "loaded on demand" and this cannot be changed.

When an Automation Add-in is cleared from the Addins dialog box, a subkey with a name equal to the Addin 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 entry ensures that Automation Add-ins you have installed but are not using are retained in the list.

Dim c As TestingExcel.IWorksheetFunctions 
Set c = Application.COMAddIns.Item("TestingExcel.Connect").Object
If iDate = -1 Then
   Age = c.Age(iDayNo, iMonthNo, iYearNo)
   Age = c.Age(Day(iDate), Month(iDate), Year(iDate))
End If

Things to Remember

  • When Excel loads an Automation Addin during cell edit mode Excel fails. This only occurs with Automation addins that have been loaded in demand. Into Excel.

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited