Excel Automation Add-Ins with Delphi

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.

5 thoughts on “Excel Automation Add-Ins with Delphi

  1. Peter

    Hello, thanks for you article.
    I’ve compiled you app and when I run the “bat” the dll file is registred sucefully .
    but I can’t see on excel the new function into add in options.

    Can you help me please?
    regards

    Reply
    1. Edgar Post author

      In the Add-Ins dialog, there are four buttons on the right. You must click the “Automation” button. Then scroll down in the list of available Automation Servers and search for “DelphiAddin Object”. Select the server and click “OK”. Now the Add-In appears in the Excel Add-Ins Dialog and you can close the Add-In dialog box with “OK”.
      Even if the function does not appear in the Insert Function dialog box, it will be available as a Worksheet function as described. Just try it.

      Reply
      1. Peter

        Thank you for you reply Edgard.
        I’ve followed your instruction to register the Add-in into excel 2013 but still I can’t see “DelphiAddin Object” in the list of server available.
        I’m running windows 10 , Excel 2013 .
        I would appreciate ayou help.

        Regards

        Reply
      2. Peter

        good news,
        I’ve solved the problem.
        My Operating system is 64x, in the sample downloaded the default compiler is 32x, I’ve changed to 64x then it works!!!

        thank you for you help and sharing..

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *