Range arguments for a XLL function

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.

Leave a Reply

Your email address will not be published. Required fields are marked *