Excel enables a DLL to call back into Excel. Your Delphi Add-In will need to use this feature provided with the `Excel4v`

and `Excel12v`

callback functions. We import the `Excel4v`

function and implement the `Excel12v`

function. Hence we’ve got the objects to write Delphi XLL Add-Ins.

The `Excel4v`

function is provided by the `xlcall32.dll`

. We import the function in Delphi as follows.

unit dXLCall; {...} const cxloperMax = 30; type PLPXLOPER = ^LPXLOPER; LPXLOPERarray = array[1..cxloperMax] of LPXLOPER; PLPXLOPERarray = ^LPXLOPERarray; const xlcall32 = 'xlcall32.dll'; function Excel4v(xlfn: integer; operRes: LPXLOPER; count: integer; opers: PLPXLOPERarray): integer; stdcall; external xlcall32; overload; {...}

`Excel4v`

is prototyped with the `pascal`

calling convention in the Excel SDK. This calling convention is obsolete and should be replaced with `WINAPI`

. But `WINAPI`

is just another name for `stdcall`

. Next we define the `Excel12v`

function in a similar way.

{...} const cxloper12Max = 255; type PLPXLOPER12 = ^LPXLOPER12; LPXLOPER12array = array[1..cxloper12Max] of LPXLOPER12; PLPXLOPER12array = ^LPXLOPER12array; function Excel12v(xlfn: integer; operRes: LPXLOPER12; count: integer; opers: PLPXLOPER12array): integer; stdcall; overload; {...}

Now we have done with the interface part of our `dXLCall`

unit. The implementation part starts with the type definition of the `EXCEL12PROC`

as follows.

type EXCEL12PROC = function(xlfn: integer; coper: integer; rgpxloper12: PLPXLOPER12; xloper12Res: LPXLOPER12): integer; stdcall;

The source can be translated line-by-line. We call back as follows.

{...} result:= pexcel12(xlfn, count, @opers^[1], operRes);

By the way, I fetched inline instead of using the `inline`

keyword. The `inline`

keyword is only a suggestion to the compiler and obtained a different meaning for older compiler versions.

That’s all. But we add a second unit to make Excel calls a bit easier. The Microsoft Excel SDK provides a framework library. At this point, we won’t implement a Delphi framework but define function wrappers for the `Excel4v`

and `Excel12v`

function to extend the variations in calling Excel.

unit dXLUtils; interface uses dXLCall; function Excel4e(xlfn: integer; operRes: LPXLOPER; count: integer = 0; opers: PLPXLOPERarray = nil): integer; overload; function Excel4e(xlfn: integer; var operRes: XLOPER; count: integer = 0; opers: PLPXLOPERarray = nil): integer; overload; function Excel4e(xlfn: integer; operRes: LPXLOPER; count: integer; opers: array of LPXLOPER): integer; overload; function Excel4e(xlfn: integer; var operRes: XLOPER; count: integer; opers: array of LPXLOPER): integer; overload; {...}

These routines are implemented unsophisticated but can be enhanced as shown in the framework library at a later time. We do only a call to the `Excel4v`

or `Excel12v`

as follows.

implementation function Excel4e(xlfn: integer; operRes: LPXLOPER; count: integer = 0; opers: PLPXLOPERarray = nil): integer; overload; begin result:= Excel4v(xlfn, operRes, count, opers); end; {...} function Excel12e(xlfn: integer; var operRes: XLOPER12; count: integer; opers: array of LPXLOPER12): integer; overload; begin result:= Excel12v(xlfn, @operRes, count, @opers); end; end.

These overloaded functions allow flexible calls to Excel. You can use open array constructors as parameters and you can pass the result parameter as a `XLOPER`

or `XLOPER12`

by reference without the `@`

-operator as in the example below.

procedure VerStackt; var a, b, c: XLOPER12; begin // pass result by ref and use default params Excel12e(xlStack, a); b.xltype:= xltypeInt; b.val.w:= xltypeStr; // pass result by val and params as dynamic array Excel12e(xlCoerce, @c, 2, [@a, @b]); b.val.w:= 3; Excel12e(xlcAlert, nil, 2, [@c, @b]); Excel12e(xlFree, nil, 1, @c); end;

Now we can start writing Excel XLL Add-Ins.

DUYET LEHi Glade,

Do you write any book about these topic.

Thanks,

Duyet Le

EdgarPost authorThere is only one book out there. You could try to buy a used one.

https://www.amazon.com/dp/0470027975/