Develop Excel Automation Add-ins (user-defined functions)
in Delphi 5, 6, 7, 2005, 2006 and 2007
Add-in Express
for Borland VCL
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

