How to : Writing (non com) excel add-ins in delphi
Writing (non com) excel add-ins in delphi
Writing Excel add-ins with Delphi is a challenging project. I show how it can be done, and highlight the various pitfalls waiting for the unwitting traveller in this largely unexplored frontier.
Writing Excel add-ins with Delphi is a challenging project. Here where I show how it can be done, and highlight the various pitfalls waiting for the unwitting traveller in this largely unexplored frontier.
Despite Microsoft's apparent unpopularity in some quarters, Excel really should place high on lists for 'best software ever created'. Of course taking well over ten years to get it right helped. I think that a particular strength of Excel is its relatively open API, allowing developers to create macros and use Visual Basic For Excel/VBA for apps.
One of the lesser known Excel features is add-ins. Though you can create them with VBA, but you can also write add-in Dlls in C++ and Delphi. For those doing serious development you need to buy the Excel 97 Developer's Kit (ISBN 1-57231-498-2) (EDK) book, but of course its C/C++ oriented and there are some traps for the Delphi programmer. In this article I show you enough to get you going. As a developer in an Excel work environment I have successfully developed many add-ins with Delphi 3, and I know of know of no one else doing this. There is Financial CAD, a Canadian firm whose add-ins can be used from Delphi but I think they're written in C++. Hey I might be the only person in the world doing this!
Delphi add-ins make it easy for you to extend Excel in many ways- such as data capture from serial ports, data feeds, all with the speed of Delphi compiled code which is significantly faster than interpreted Excel VBA. Lets get started, theres a lot to cover.
Huge Strings are a Huge Mistake
As the add-in is a DLL and Excel uses short strings, you must ensure that the Huge Strings compiler option is clear (or $H- used). You could probably use long strings internally but make sure you convert before passing them to Excel. For further safety feature use ShortString or String[n] types where n is 1-255. Even if you have Hugestrings enabled, you can use string[n] for parameter passing as these are always of type shortstring. Just remember the golden rule, no long strings in passed parameters or records.
Recognition at last
Excel will only recognise your DLL as an add-in if certain functions are exported. You must always provide these functions, as well as those for the user. These xlAuto family of functions are listed in the table below and documented in the Edk book and in the example code with this article. All of your exported functions must use the STDCALL calling convention.
FunctionPurpose
xlAutoFreeCalled by Excel to free the Addin's allocated memory.
XlAutoAddCalled when the Addin is first registered.
XlAutoOpenCalled when Excel loads.
XlAutoCloseCalled when Excel exits.
XlAutoRemoveCalled when the Addin is removed from Excel.
XlAutoRegisterOnly called if a function hasn't been registered.
XlAddInManagerInfoProvides a text string description of the Addin.
To use any built in Excel function your function calls the Excel function Excel4V. This is defined as
function Excel4v(
xlfn: word;
operRes: lpxloper;
count: integer;
opers: array of lpxloper):integer;
stdcall; external 'xlcall32.dll';
xlfn is the 'Function number' of the Excel Function called.
Operfn is the result and is a pointer to an xloper called an lpxloper (see next section)
Count is the number of elements in Opers.
Opers is an array of lpxloper, i.e. an array of pointers to xlopers.
Note: For many function you can pass a null array for the Opers parameter. Under D3, the empty array construction [] is not allowed, .as it is in D4 so use [nil] under D3.
My development emphasis has been to give users new functions. The EDK documents how to add buttons and controls to Excel but those are a little bit more work and I don't deal with them here. If you wish to push data into Excel there are two other approaches, both shareware based- the TadvExcel component has very fast data transfer using DDE. The TxlsReadWrite read components can output data formatting and formulas direct into Excel workbook files.
Before you start calling Excel functions, you have to know about the XLOper type. This is a pascal record (C struct) some 10 bytes in size, aligned on 16 byte paragraphs in arrays which corresponds to cells in an Excel spreadsheet. The definition is shown below. Blame Microsoft for the brief field names. The Tval type uses the old pascal variant record type, not to be confused with Windows OLE variants, though used in a similar way. The xltype field of XlOper specifies which of the 9 types used is in play. So if the xloper has a type of 1, val.num has a valid double precision number.
I've found that types 1, 2, and 8 are the most used. Type 4 is returned by Excel when you..