Develop Automation Add-ins for
Excel 2016, 2013, 2010, 2007 in Delphi
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. Also, if you have Windows Vista, Windows 7, Windows Server 2008 or Windows 8, run Delphi via Run as Administrator.
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:
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:
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 - 2016 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: