Develop Excel Automation Add-ins (user-defined functions)
for Excel 2010 - 2002 in Delphi 5 - XE2

Add-in Express™
for Microsoft® Office and Delphi® VCL

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

Excel Automation add-ins in Delphi

The sample Excel Automation add-in described on this page is included in Add-in Express for Office and Delphi sample projects that are available on the Downloads page.

The fact is that Excel Automation Add-ins do not differ from COM Add-ins except for the registry entries. That's why Add-in Express bases Excel Automation Add-in projects on COM Add-in projects.

A bit of theory

Excel 2002 brought in Automation Add-ins - a technology that allows writing user-defined functions for use in Excel formulas. Add-in Express provides you with a specialized module, COM Excel Add-in Module, that cuts down this task to just writing one or more user-defined functions. A typical function accepts one or more Excel ranges and/or other parameters. Excel shows the resulting value of the function in the cell where the user calls it.

Add-in Express allows developing Excel Automation add-ins using the add-in module that has the XLAutomationAddin Boolean property. Set the property to true, add a method to the add-in module's type library, and write the method's code.

Excel user-defined functions (UDFs) are used to build custom functions in Excel for the end user to use them in formulas. This definition underlines the main restriction of an UDF: it should return a result that can be used in a formula - not an object of any given type but a number, a string, or an error value (Booleans and dates are essentially numbers). When used in an array formula, the UDF should return a properly dimensioned array of values of the types above. Excel shows the value returned by the function in the cell where the user calls the function.

There are two Excel UDF types: Excel Automation add-in and Excel XLL add-in. Add-in Express allows creating only an Excel Automation add-in.

Per-user Excel UDFs

An Excel UDF is a per-user thing that requires registering in HKCU. In other words, a UDF cannot be registered for all users on the machine. Instead, it must be registered for every user separately. See also Registry entries.

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 CodeGear Delphi for Microsoft Windows.

Creating an Excel Automation Add-in project in Delphi

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

Selecting Excel Automation add-in project options

The Add-in Express Project wizard creates and opens the Excel Automation Add-in project in Delphi.

Excel Automation project in the Project Manager window

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 in Delphi 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.

Adding Excel Automation add-in functionality

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.

Adding an Excel UDF to the add-in type library

Setting Excel UDF 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;