OnUserFunction / OnUserFunctionEx callback

The OnUserFunction callback lets application code evaluate custom or otherwise unsupported worksheet functions during Calculate. Assign it on TXLSWorkbook or TXLSXWorkbook before calling Calculate

Use OnUserFunctionEx when the callback needs the current formula context. The context reports the 1-based sheet index plus the 1-based row and column of the formula cell; workbook-level Calculate calls that are not evaluating a cell report row and column as 0

Syntax

type
  TXLSUserFunctionContext = record
    SheetIndex: Integer;
    Row: Integer;
    Col: Integer;
  end;

  TXLSUserFunctionEvent = procedure(
    Sender: TObject;
    const FunctionName: WideString;
    const Args: Variant;
    var Value: Variant;
    var Handled: Boolean) of object;

  TXLSUserFunctionExEvent = procedure(
    Sender: TObject;
    const FunctionName: WideString;
    const Args: Variant;
    const Context: TXLSUserFunctionContext;
    var Value: Variant;
    var Handled: Boolean) of object;

property OnUserFunction: TXLSUserFunctionEvent;
property OnUserFunctionEx: TXLSUserFunctionExEvent;

Arguments

FunctionName	The function name from the formula text
Args	A zero-based Variant array containing evaluated argument values. For zero-argument functions this value is `Null`
Context	For `OnUserFunctionEx`, reports `SheetIndex`, `Row`, and `Col`. Sheet, row and column values are 1-based for cell formulas; row and column are `0` when no cell is being evaluated
Value	Set this to the function result
Handled	Set to `True` when the callback supplies a result. Leave `False` to keep the normal unsupported function behavior

Example

procedure TForm1.WorkbookUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DOUBLEPLUS') then
  begin
    Value := Double(Args[0]) * 2 + Double(Args[1]);
    Handled := True;
  end;
end;

Workbook.OnUserFunction := WorkbookUserFunction;
Value := Workbook.Calculate('=DOUBLEPLUS(A1;5)');

Context-aware example

procedure TForm1.WorkbookUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'ROWBONUS') then
  begin
    Value := Double(Args[0]) + Context.Row + Context.Col;
    Handled := True;
  end;
end;

Workbook.OnUserFunctionEx := WorkbookUserFunctionEx;
Value := Worksheet.Calculate('=C2');

Syntax

Arguments

Example

Context-aware example

See also