Today, I want to show you a simple user defined function (UDF) for a Delphi XLL that evaluates a function parameter passed in as an Excel Range. This example was requested by a visitor of my glade.
The arguments for a XLL function are specified as the third parameter to the xlfRegister
function. This is a string that encodes the return type and the argument types of a function. A list of the data types can be found at the MSDN.
The sample function is registered with “PP”. It will return a variable type value and takes a variable type value as an argument. If you are using XLOPER12
, you can use the type Q. The R and U type are also data types for variable values but you must expect xltypeRef
and xltypeSRef
values. It is more complex to handle this.
In Delphi, the sample function is declared as
function SampleFunc(x: LPXLOPER): LPXLOPER; stdcall;
It is registered as RangeFnc which is the name of the Worksheet function you must use when the XLL add-in is installed.
Excel cannot check the parameter types that are used to call the xlfRegister
function to register a new UDF. xlfRegister
will not fail if the parameters do not match your function definition in the DLL. But ideally, Excel will crash in this case, when the function is called the first time.
With the P or Q type you will get only one of the following types: xltypeNum
, xltypeStr
, xltypeBool
, xltypeErr
, xltypeMissing
, xltypeNil
or xltypeMulti
. Excel will do a conversion if necessary. Omitted parameter is converted to xltypeMissing
. Values are converted to the appropriate type. Ranges that refer a single cell are also converted to the appropriate type at which an empty cell is converted to xltypeNil
. xltypeRef
and xltypeSRef
parameters are converted to xltypeMulti
.
An xltypeMulti
XLOPER
consists of elements with type xltypeNum
, xltypeStr
, xltypeBool
, xltypeErr
or xltypeNil
where a xltypeNil
XLOPER
marks an empty cell. With this information, the function code can be completed to evaluate its Excel Range parameter.
function SampleFunc(x: LPXLOPER): LPXLOPER; stdcall; type tmulti = array[0..0] of XLOPER; pmulti = ^tmulti; var oper: XLOPER; i, j: Integer; lsum: Double; lchar, lbool, lerr, lempty: Integer; begin Msg:='Blub'; case (x.xltype and $0FFF) of xltypeNum : Msg:= 'number'; xltypeStr : Msg:= 'string'; xltypeBool : Msg:= 'bool'; xltypeErr : Msg:= 'error'; xltypeMissing : Msg:= 'nothing'; xltypeNil : Msg:= 'empty cell'; xltypeMulti : begin with x.val.xarray do begin lsum:=0; lchar:= 0; lbool:=0; lerr:=0; lempty:=0; for i:=0 to rows - 1 do begin for j:=0 to columns - 1 do begin oper:= pmulti(lparray)[i * columns + j]; case (oper.xltype and $0FFF) of xltypeNum : lsum:= lsum + oper.val.num; xltypeStr : lchar:= lchar + Length(PShortString(oper.val.str)^); xltypeBool: inc(lbool); xltypeErr : inc(lerr); xltypeNil : inc(lempty); end; end; end; Msg:= 'range ' + IntToStr(rows) + 'rows ' + IntToStr(columns) + 'cols ' + '; sum: ' + FloatToStr(lsum) + '; chars: ' + IntToStr(lchar) + '; bools: ' + IntToStr(lbool) + '; errors: ' + IntToStr(lerr) + '; empty: ' + IntToStr(lempty); end; end; end; if (x.xltype and xlbitXLFree) = xlbitXLFree then begin Msg:= Msg + ' XLFree'; Excel4e(xlFree, nil, 1, [x]); end; res.xltype:= xltypeStr; res.val.str:= @Msg; result:=@res; end;
Don’t forget to call xlFree
if xlbitXLFree
is set. You can access a particular cell in the way described in the function code. Just use the lparray
as a zero-based array of XLOPER
with:
lparray[(row - 1) * columns + (column - 1)]
Please, keep in mind that the function uses P as parameter type. It is limited to the XLOPER
type where the rows and columns of the xltypeMulti
are defined in word size. The range array is limited to 65535 elements. As Excel 11 and before are retired in many environments, you should use a Q data type (XLOPER12
) without this limitation.
You can download the add-in and its source from the download page.