Automation Add-ins can be in-process or out-of-process COM servers and implementation of IDTExtensibility2 is optional. This is what you may find on Microsoft’s Website if you’re searching information about Automation Add-Ins. It implies that every COM server can be used as an Excel Automation Add-In due to the fact that implementation of the mentioned interface is optional.

Let’s try coding a simple Automation Add-In with Delphi. We start with a new COM library and add an Automation Object. The interface gets a method SimpleAdd to sum up two floating point values. We want to use this method as a user defined function in Excel.

[
  uuid(1B77B758-6EF2-4F78-8B0E-2BE406B88AAF),
  version(1.0),
  helpstring("AutomAddinSample Library")

]
library AutomAddinSample
{

  importlib("stdole2.tlb");

  interface IDelphiAddin;
  coclass DelphiAddin;


  [
    uuid(4A6B90E0-598C-4795-B645-0D631203307C),
    version(1.0),
    helpstring("Dispatch-Interface for DelphiAddin-Object"),
    dual,
    oleautomation
  ]
  interface IDelphiAddin: IDispatch
  {
    [id(0x000000C9)]
    HRESULT _stdcall SimpleAdd([in] double X, [in] double Y, [out, retval] double* retval);
  };

  [
    uuid(B8D9B2DB-399E-4B5D-8373-BFC7FD129536),
    version(1.0),
    helpstring("DelphiAddin Object")
  ]
  coclass DelphiAddin
  {
    [default] interface IDelphiAddin;
  };

};

The Co-Class unit looks quite simple as follows.

...

type
  TDelphiAddin = class(TAutoObject, IDelphiAddin)
  protected
    function SimpleAdd(X, Y: Double): Double; safecall;
  end;

...

function TDelphiAddin.SimpleAdd(X, Y: Double): Double;
begin
  result:=X + Y;
end;

initialization
  TAutoObjectFactory.Create(ComServer, TDelphiAddin,
    Class_DelphiAddin, ciMultiInstance, tmApartment);
end.

We compile the project and register the server. In the Excel Add-In dialog, we select Automation but our server does not appear.

The Automation object must be registered as ActiveX object. The Programmable flag must exist under the CLSID section.

HKEY_CLASSES_ROOT\CLSID\{Delphi CLSID}\Programmable
or
HKEY_CURRENT_USER\Software\Classes\CLSID\{Delphi CLSID}\Programmable

Delphi does not create these entries. But it’s required to be identified as a Microsoft Office Automation Add-In. The TAutoObjectFactory need an adjustment to do this. The UpdateRegistry-Method is the right place. It is called on DLLRegisterServer.

type
  TAutomationAddInFactory = class(TAutoObjectFactory)
  public
    procedure UpdateRegistry(Register: Boolean); override;
  end;

procedure TAutomationAddInFactory.UpdateRegistry(Register: Boolean);
var
  ClassKey: string;
  RegPrefix: string;
  RootKey: HKEY;
begin
  {$IF CompilerVersion >= 22.0}
  ComServer.GetRegRootAndPrefix(RootKey, RegPrefix);
  {$ELSE} // per user registration not implemented
  Rootkey:=HKEY_CLASSES_ROOT;
  RegPrefix:='';
  {$IFEND}
  ClassKey:=RegPrefix + 'CLSID\' + GUIDToString(ClassID);
  if Register then begin
    inherited UpdateRegistry(Register);
    CreateRegKey(ClassKey + '\Programmable', '', '', RootKey);
  end
  else begin
    DeleteRegKey(ClassKey + '\Programmable', RootKey);
    inherited UpdateRegistry(Register);
  end;
end;

In the initialization section, we exchange the factory.

initialization
  TAutomationAddInFactory.Create(ComServer, TDelphiAddin,
    Class_DelphiAddin, ciMultiInstance, tmApartment);
end.

Now we’ve got an Automation Add-In which appears in the Excel Add-In dialog. Our UDF can be used as worksheet function.

=SimpleAdd(X,Y)

And it is listed as a user defined function in the Excel Function Wizard.