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.