Automation Add-ins

Introduced in Excel 2002.
It is possible to define worksheet functions.

Creating - Worksheet Functions

An Automation add-in can be used to define and expose additional worksheet functions.
These steps are outlined on the C# Functions > With Automation Add-ins page.

IDTExtensibility2 Interface

Implementing the IDTExtensibility2 interface 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.

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

Key - HKEY_CLASSES_ROOT\CLSID\ {GUID} 

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.

<GuidAttribute("389DB139-D54D-4CD8-96C8-E8456B732344"), _ 
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

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 2007 
Key - HKEY_CURRENT_USER\Software\Microsoft\Office\14.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) 
Else 
   Age = c.Age(Day(iDate), Month(iDate), Year(iDate)) 
End If 

Important

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.