Excel COM Add-Ins with Delphi Part I

COM Add-Ins have become more important when Microsoft introduced the Office Fluent User Interface. But you can use COM Add-Ins to extend functionality starting with Microsoft Office 2000 and with access to the object model of its host application. They are also used to provide add-in functionality to Visual Studio.

A COM Add-In is an in-process COM-Server that must implement the IDTExtensibility2 interface. The IRibbonExtensibility interface must be implemented to gain access to the Ribbon UI. That’s why we will use Delphi to create COM Add-Ins. A first simple project will describe the things to do in addition to the implementation of interfaces.

A COM Add-In needs some additional registry entries. We override the UpdateRegistry method of the TAutoObjectFactory to create the required entries.

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

There are different locations in the registry depending on the target application.

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\<ProgId>
or in general
HKEY_CURRENT_USER\Software\Microsoft\Office\<Office App>\Addins\<ProgId>
except
HKEY_CURRENT_USER\Software\Microsoft\Visio\Addins\<ProgId>
and
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\<Ver>\Addins\<ProgId>
HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\<ProgId>

A Subkey with the name of the Add-In’s ProgID must be created. The Values under the Subkey describe the COM add-in. We use TRegistry because the values have type REG_SZ and REG_DWORD.

LoadBehavior is a Value that is not optional. In most cases the default value 3 will be fine. You can find a description of possible values here.

The CommandLineSafe Value is optional. It is marked as not applicable for Office Add-Ins. If set to value 1 it indicates that the add-in avoids any UI manipulation when it is called with ConnectMode = ext_cm_CommandLine.

The Add-In Manager shows a list of available Add-Ins. An optional FriendlyName Value will appear in the list instead of the Add-In’s ProgID and we can add an optional Description Value.

We delete the Subkey and its Values when the Add-In is unregistered.

{...}

procedure TXLComAddinFactory.UpdateRegistry(Register: Boolean);
var
  RootKey: HKEY;
  AddInKey: String;
  r: TRegistry;
begin
  Rootkey:=HKEY_CURRENT_USER;
  AddInKey:='Software\Microsoft\Office\Excel\Addins\' + ProgID;
  r:=TRegistry.Create;
  r.RootKey:=RootKey;
  try
    if Register then
      if r.OpenKey(AddInKey, True) then begin
        r.WriteInteger('LoadBehavior', 3);
        r.WriteInteger('CommandLineSafe', 0);
        r.WriteString('FriendlyName', 'Delphi Sample Add-In');
        r.WriteString('Description', 'Sample Add-In written in Delphi');
        r.CloseKey;
      end else
        raise EOleError.Create('Can''t register Add-In ' + ProgID)
    else
      if r.KeyExists(AddInKey) then
        r.DeleteKey(AddInKey);
  finally
    r.Free;
  end;
  inherited;
end;

The UpdateRegistry method of the ancestor may raise an EOleRegistrationError exception when DLLRegisterServer is called to register the COM server under HKCR without administrative rights. But DLLRegisterServer is called not only by regsvr32. The Add-In Manager contains two buttons to Add and Remove Add-Ins. The Subkey we had created is deleted by clicking Remove. You can add COM Add-Ins by clicking Add. The Add-In Manager calls DLLRegisterServer if you select a file that is an already registered COM Server. This is done to force the Add-In to re-create the Subkey and the Values mentioned above. Thus, the Subkey should be created regardless that the following registration may fail. This is the reason why inherited is called at the end.

Now, we can start writing a first simple Add-In by implementing the IDTExtensibility2 interface. You can find the definition in the Microsoft Add-In Designer (MSADDNDR.DLL). The interface can be extracted from the TLB as follows.

type
// *********************************************************************//
// Interface: IDTExtensibility2
// Flags:     (4432) Hidden Dual OleAutomation Dispatchable
// GUID:      {B65AD801-ABAF-11D0-BB8B-00A0C90F2744}
// *********************************************************************//
  IDTExtensibility2 = interface(IDispatch)
    ['{B65AD801-ABAF-11D0-BB8B-00A0C90F2744}']
    procedure OnConnection(const Application: IDispatch;
                           ConnectMode: ext_ConnectMode;
                           const AddInInst: IDispatch;
                           var custom: PSafeArray); safecall;
    procedure OnDisconnection(RemoveMode: ext_DisconnectMode;
                              var custom: PSafeArray); safecall;
    procedure OnAddInsUpdate(var custom: PSafeArray); safecall;
    procedure OnStartupComplete(var custom: PSafeArray); safecall;
    procedure OnBeginShutdown(var custom: PSafeArray); safecall;
  end;

// *********************************************************************//
// DispIntf:  IDTExtensibility2Disp
// Flags:     (4432) Hidden Dual OleAutomation Dispatchable
// GUID:      {B65AD801-ABAF-11D0-BB8B-00A0C90F2744}
// *********************************************************************//
  IDTExtensibility2Disp = dispinterface
    ['{B65AD801-ABAF-11D0-BB8B-00A0C90F2744}']
    procedure OnConnection(const Application: IDispatch;
                           ConnectMode: ext_ConnectMode;
                           const AddInInst: IDispatch;
                           var custom: {PSafeArray} OleVariant); dispid 1;
    procedure OnDisconnection(RemoveMode: ext_DisconnectMode;
                              var custom: {PSafeArray} OleVariant); dispid 2;
    procedure OnAddInsUpdate(var custom: {PSafeArray} OleVariant); dispid 3;
    procedure OnStartupComplete(var custom: {PSafeArray} OleVariant); dispid 4;
    procedure OnBeginShutdown(var custom: {PSafeArray} OleVariant); dispid 5;
  end;

We add a new Automation Object to our project and add IDTExtensibility2 to the list of implemented interfaces.

type
  TDelphiAddin1 = class(TAutoObject, IDelphiAddin1, IDTExtensibility2)
  private
    procedure OnConnection(const Application: IDispatch;
                           ConnectMode: ext_ConnectMode;
                           const AddInInst: IDispatch;
                           var custom: PSafeArray); safecall;
    procedure OnDisconnection(RemoveMode: ext_DisconnectMode;
                              var custom: PSafeArray); safecall;
    procedure OnAddInsUpdate(var custom: PSafeArray); safecall;
    procedure OnStartupComplete(var custom: PSafeArray); safecall;
    procedure OnBeginShutdown(var custom: PSafeArray); safecall;
  protected

  end;

You can find a description of the five methods and a VBA example here. We want to keep the first project simple. So, we only write code to the OnConnection method and leave the others empty.

{ TDelphiAddin1 }

procedure TDelphiAddin1.OnConnection(const Application: IDispatch;
  ConnectMode: ext_ConnectMode; const AddInInst: IDispatch;
  var custom: PSafeArray);
var
  s: WideString;
  x: OleVariant;
begin
  if ConnectMode <> ext_cm_CommandLine then
    try
      x:= Application;
      s:= 'Hello World!';
      s:= s + ' You''re running ' + x.Name + ' version ' + x.Version;
      MessageBoxW(0, PWideChar(s), '', MB_OK);
    except
    end;
end;

{...}

We check the ConnectMode parameter and display a message box which shows the name and version of the Add-In’s host Application. The last step is to exchange the factory.

initialization
  TXLComAddinFactory.Create(ComServer, TDelphiAddin1, Class_DelphiAddin1,
    ciMultiInstance, tmApartment);
end.

Leave a Reply

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