Contextual formula evaluation

Classic XLS and XLSX worksheets expose the same read-only API for evaluating formula text as though it had been entered at a chosen one-based row and column

One-shot evaluation

function EvaluateFormulaAt(Row, Col: Integer;
  const Formula: WideString): TXLSFormulaEvaluationResult; overload;

function EvaluateFormulaAt(Row, Col: Integer;
  const Formula: WideString;
  ReferenceStyle: TXLSFormulaReferenceStyle;
  const Options: TXLSFormulaEvaluationOptions): TXLSFormulaEvaluationResult; overload;

The worksheet supplies the local-name, unqualified-reference, table and external-link context without changing the active sheet, selection, cell model, formula cache, dirty state or dependency graph

xlfrsA1 preserves absolute and mixed references whilst resolving relative references from the target cell; xlfrsR1C1 resolves relative offsets directly from that target

CELL without an explicit reference inspects the supplied target cell; use $A$1 to inspect the fixed first cell from any target, whilst A1 resolves relative to that target under the A1 context contract

Model-backed CELL metadata and INFO("recalc") also work through read-only workbook views without changing either their generation or stored formula caches; CELL("width",reference) retains its one-row, two-column array, whilst unclassified custom formats return xlfeUnsupportedFunction instead of a fabricated format code; see CELL and INFO metadata for the complete headless and locale boundaries

Typed results

FORMULATEXT inspects stored formula metadata through both worksheet engines and read-only views, without calculating the inspected target; GETPIVOTDATA evaluates a detached snapshot of a local PivotTable's existing cache and honours evaluation budgets and cancellation; missing visible aggregates remain typed #REF! values, whilst incomplete or unsupported Pivot models return xlfeUnsupportedFunction; see formula text inspection and visible Pivot data for storage, array and cache boundaries

TXLSFormulaEvaluationResult returns Status, the original typed Value, IsVolatile, and a stable Issue record containing status, native code, and message

TXLSFormulaEvaluationStatus = (xlfeOk, xlfeInvalidArgument,
  xlfeWorksheetUnavailable, xlfeParseError, xlfeUnsupportedFunction,
  xlfeCircularReference, xlfeExternalReferenceDenied, xlfeCancelled,
  xlfeResourceLimit, xlfeFailed, xlfeUnsafeFunctionDenied);

TXLSFormulaEvaluationIssue = record
  Status: TXLSFormulaEvaluationStatus;
  NativeCode: Integer;
  Message: WideString;
end;

Formula values such as #DIV/0!, #REF!, empty strings, arrays, Boolean values, and dates remain distinguishable values; parse failures, unsupported functions, circular references, denied external references, denied unsafe callbacks, cancellation, invalid arguments, unavailable worksheets, and resource exhaustion use explicit API statuses Issue.NativeCode carries the underlying engine error code and Issue.Message the human-readable description when Status is not xlfeOk

Reusable immutable templates

function CreateFormulaEvaluationTemplate(Row, Col: Integer;
  const Formula: WideString;
  ReferenceStyle: TXLSFormulaReferenceStyle): IXLSFormulaEvaluationTemplate;

A template compiles the formula once at its anchor and exposes EvaluateAt and EvaluateAtWithOptions for repeated evaluation at different target cells

Ordinary A1 and R1C1 formulas reuse the compiled tree directly; A1 structured references whose meaning depends on the target table row are resolved and compiled against that target context

Templates keep no writable worksheet state, safely report xlfeWorksheetUnavailable after workbook destruction, and fall back to context-aware recompilation when workbook changes leave the original compiled context stale

Resource, external-reference, and callback options

AllowExternalReferences	Defaults to true but permits only explicitly registered workspace targets or file-local caches and never performs any implicit file or network access
AllowUnsafeFormulaCallbacks	Defaults to false and blocks dangerous function names from reaching a user-function registry or callback before any argument is evaluated; a denial returns `xlfeUnsafeFunctionDenied`
CancelFlag	Optional Boolean pointer checked before and after evaluation
MaxFormulaLength	Defaults to 8192 UTF-16 code units
MaxEvaluationSteps	Defaults to 1,000,000 syntax-item evaluations
MaxRecursionDepth	Defaults to 256 nested formula evaluations
MaxArrayBytes	Defaults to 64 MiB for generated array payloads

A zero or negative maximum disables that per-call limit; the workbook-wide array memory policy still applies, and reaching an enabled limit yields xlfeResourceLimit after releasing temporary evaluation state

The overload that accepts explicit options treats AllowUnsafeFormulaCallbacks as authoritative for that call; the default one-shot and template evaluation inherit the workbook property of the same name

Delphi example

Template := Sheet.CreateFormulaEvaluationTemplate(2, 2,
  '=R[-1]C[-1]+1', xlfrsR1C1);

Result := Template.EvaluateAt(2, 2);
if Result.Status = xlfeOk then
  UseValue(Result.Value);

Options := XLSDefaultFormulaEvaluationOptions;
Options.MaxEvaluationSteps := 10000;
Result := Template.EvaluateAtWithOptions(20, 4, Options);

C++Builder example

Lxcalc::_di_IXLSFormulaEvaluationTemplate evalTemplate =
  sheet->CreateFormulaEvaluationTemplate(1, 1,
    WideString(L"=ROW()+COLUMN()"), Lxcalc::xlfrsA1);

Lxcalc::TXLSFormulaEvaluationResult result =
  evalTemplate->EvaluateAt(7, 8);

The direct regular expression backend API documents per-instance matching, replacement, statuses and caller budgets

REGEX formulas share the current evaluation context's work, memory and cancellation controls, including PCRE2 workspace and output allocations; cancellation or a resource limit yields an explicit status without leaving a partial extraction or replacement result