Writing Excel Addons - Huge Strings are a Huge Mistake
(Page 2 of 8 )
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, 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 recognize 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.
Function Purpose
- xlAutoFree: called by Excel to free the Addin’s allocated memory.
- xlAutoAdd: called when the Addin is first registered.x
- xlAutoOpen: called when Excel loads.
- XlAutoClose: called when Excel exits.
- XlAutoRemove: called when the Addin is removed from Excel.
- XlAutoRegister: only called if a function hasn’t been registered.
- XlAddInManagerInfo: provides 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 functions 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 correspond 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 get something wrong. There is an integer type (5) but num (1) seems far more common. Type 6 is used for ranges, with type 9 for collections of separate cells where you hold the Ctrl key down when selecting cells. There is no type 7.
Next: Xloper Definition >>
More Windows Scripting Articles
More By David Bolton
/ 7 
