Add-in Express™
for Microsoft® Office and CodeGear® VCLAdd-in Express Home > Add-in Express for Office and VCL > Online Guide > Excel Automation add-ins Excel Automation add-ins in Delphi
In Microsoft Excel 2002 - 2010, 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, Excel 2007 and
Excel 2010.
Add-in Express also provides some more features for creating an advanced user interface of your Excel add-in
in Delphi, such as Advanced Excel Task Panes.
Sample Excel Automation add-in
If you want to have a look at the sample Automation add-in described on this page, you can download this
sample as well as many other examples from the HowTo page.
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.
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. 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 for Office and VCL homepage | 
