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
  TXLSUserArgKind = (akuValue, akuRangeRef, akuArray);

  TXLSUserFunctionContext = record
    SheetIndex: Integer;
    Row: Integer;
    Col: Integer;
    ArgCount: Integer;
    ArgKinds: array of TXLSUserArgKind;
  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;
property AllowUnsafeFormulaCallbacks: Boolean;

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`, plus the argument descriptor: `ArgCount` matches the `Args` array length (0 for zero-argument calls) and `ArgKinds` classifies each argument as `akuValue` (literal value), `akuRangeRef` (cell or range reference), or `akuArray` (array constant). Sheet, row and column values are 1-based for cell formulas; row and column are `0` when no cell is being evaluated. The kinds are captured before the arguments are evaluated, so a SUM- or MAP-style handler can tell that the caller wrote a reference even though the evaluated `Args` entry carries only the value
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

Callback safety policy

AllowUnsafeFormulaCallbacks defaults to False on Classic XLS and XLSX workbooks, so preserving a formula never authorizes host application code to handle a dangerous external or macro-style function name

The default gate covers DDE, CALL, REGISTER, REGISTER.ID, WEBSERVICE, RTD, SQL.REQUEST, EXEC, RUN, CREATE.OBJECT, APP.ACTIVATE, SEND.KEYS, OPEN, SAVE, SAVE.AS, FOPEN, FWRITE, FWRITELN, FCLOSE, and FILE.DELETE after any recognized future-function prefix is normalized

Denial happens before argument evaluation and before workbook-local, process-wide, resolver, or event callbacks run; ordinary custom and unsupported function names retain their existing callback behavior

Set the workbook property to True only when every formula and registered handler is trusted; contextual evaluation can instead opt in for one call through TXLSFormulaEvaluationOptions.AllowUnsafeFormulaCallbacks, while an explicitly supplied False overrides a workbook-wide opt-in

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');

Receiving range arguments

When an argument evaluates to a worksheet range, the Args entry is a varUnknown Variant that holds an ICalcRangeList; the evaluated value of each covered cell is not copied into the Variant. Test and extract it with the VarIsRange overload that returns the list, then read the resolved rectangles through TXLSCalcRange

type
  TXLSCalcRange = class
    property row1: Integer;   { 0-based, inclusive }
    property col1: Integer;   { 0-based, inclusive }
    property row2: Integer;
    property col2: Integer;
    property SheetIndex: Integer;
  end;

  ICalcRangeList = interface
    property Count: Integer;
    property Item[Index: Integer]: TXLSCalcRange;
  end;

function VarIsRange(Value: Variant): Boolean; overload;
function VarIsRange(Value: Variant; var Int: ICalcRangeList): Boolean; overload;
function RangeToVariant(Value: TXLSCalcRangeList): Variant;