Develop Excel Automation Add-ins (user-defined functions)
in Delphi 5, 6, 7, 2005, 2006 and 2007

Add-in Express Home > Add-in Express VCL > Online Guide > Excel Automation add-ins

Excel Automation add-ins in Delphi

In Microsoft Excel 2002 - 2007, COM add-ins can be used to develop new worksheets functions. Such add-ins are called Excel Automation Add-ins (Excel user-defined functions, or UDF) and their only difference from COM add-ins is in a special registration method. That's why Add-n Express for Delphi bases Excel Automation add-in projects on its COM add-in projects. Note! Automation add-ins are supported by Excel 2002 (XP), Excel 2003 and Excel 2007.

Add-in Express also provides some more features for creating an advanced user interface of your Excel add-in, such as Advanced Excel Task Panes.

Step #1 – Creating a new COM add-in project

Add-in Express VCL adds the Add-in Express COM Add-in project template to the New Item dialog of Borland Delphi for Microsoft Windows.

 
When you select the template and click OK, the MS Office COM Add-in wizard starts. In the wizard windows, you choose the project options.

 
The Add-in Express Project wizard creates and opens the COM Add-in project in the IDE.

 
The add-in project includes the following items:

• The project source files (ProjectName.*)
• The type library files: binary (ProjectName.tlb) and Object Pascal unit (ProjectName_TLB.pas)
• The COM add-in module (ProjectName_IMPL.pas and ProjectName_IMPL.dfm) described on the Building your first Microsoft Office COM add-in page.

Step #2 – Including Excel Automation add-in functionality

Before you start adding Excel user-defined functions to the COM add-in, you set the XLAutomationAddin property of the add-in module to true.

Step #3– Creating user-defined functions

Open the project type library (View | Type Library menu). Add a new method to the type library and define its parameters.

 
 
Click the Refresh button and write your code to the TcoMyExcelAutomationAddin1.MyFunc function:

function TcoMyExcelAutomationAddin1.MyFunc(var Range: OleVariant): OleVariant;
begin
  Result := 0;
  case VarType(Range) of
    varSmallint, varInteger, varSingle,
    varDouble, varCurrency, varShortInt, varByte,
    varWord, varLongWord, varInt64: Result := Range * 1000;
  else
    try
      Result := Range.Cells[1, 1].Value * 1000;
    except
      Result := CVErr(xlErrValue);
    end;
  end;
end;

Step #4 – Running the Excel Automation add-in

Choose the Register ActiveX Server item in the Run menu, restart Excel, and check if your add-in works.

Step #5 – Debugging the Excel Automation add-in

To debug your add-in, just indicate the add-in host application as the Start Program in the Project Options window.

Step #6 – Deploying the Excel Automation add-in

Make sure your setup project registers the add-in DLL. Say, in Inno Setup projects you use the 'regserver' command.

Back to Add-in Express VCL homepage