Develop Excel Automation Add-ins n Delphi

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

Add-in Express Home > Add-in Express for Office and Delphi 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

Make sure that you have administrative permissions before running Delphi. Run Delphi via the Run as Administrator command.

Add-in Express for Office and Delphi VCL adds the Add-in Express COM Add-in project template to the New Item dialog.

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.

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

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. Creating an Excel Automation add-in

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.

Now, set up the parameters of you user-defined function:

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 Automation add-in

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

To debug your add-in in Excel 64-bit, you have to register the add-in DLL using regsvr32; run it from an elevated 64-bit Command Prompt. In addition, you must explicitly specify to run Excel 2010 - 2021 64-bit in the dialog window shown above.

Step 6. Deploying the Automation add-in

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

• Registering with User Privileges
• GDIPLUS.DLL