Formula entry validation policy

Classic XLS and XLSX expose the same side-effect-free contract for classifying formula input before a caller changes a cell

Shared policy

TXLSFormulaEmptyStringMode =
  (xlfesmClearCell, xlfesmPreserveString);
TXLSFormulaUnknownSymbolMode =
  (xlfusmReject, xlfusmPreserve);

TXLSFormulaEntryPolicy = record
  EmptyStringMode: TXLSFormulaEmptyStringMode;
  UnknownFunctionMode: TXLSFormulaUnknownSymbolMode;
  UnknownNameMode: TXLSFormulaUnknownSymbolMode;
end;

property FormulaEntryPolicy: TXLSFormulaEntryPolicy;

Classic IXLSWorkbook and TXLSWorkbook expose the property together with TXLSXWorkbook; XLSDefaultFormulaEntryPolicy clears empty input and rejects unknown functions and unknown defined names

The workbook property is copied by value and validated atomically when assigned, so unsupported enumeration values raise EArgumentOutOfRangeException without changing the previous policy

Validation result

function ValidateFormulaEntry(Row, Col: Integer;
  const Formula: WideString): TXLSFormulaEntryValidationResult;

TXLSFormulaEntryValidationResult = record
  Issue: TXLSFormulaEntryIssue;
  Action: TXLSFormulaEntryAction;
  Symbol: WideString;
end;

Issue distinguishes no error, unknown function, unknown name, syntax error, target-format limitation, and invalid target coordinates

Action selects clear cell, store string, store formula, or reject, while Symbol identifies an unknown function or name when one was recognised

Empty text and formulas

Exact empty input returns either xlfeaClearCell or xlfeaStoreString according to EmptyStringMode

A formula that returns an empty string, such as ="", remains a formula and returns xlfeaStoreFormula when the target format can represent it

Formula text may include or omit one leading equals sign

Assigning an exact empty string through the normal Classic XLS or XLSX cell Value property now applies this mode directly: the default removes any existing value or formula, while xlfesmPreserveString replaces the cell content with a constant string whose length is zero

Null and Unassigned always clear the cell in either mode, while a real formula such as ="" remains a formula with an independently stored or calculated empty-string result

XLSX string and Variant row or column writers apply the same rule without materialising cleared holes and retain packed storage whenever the remaining values are representable there

Native XLS, XLSX, and ODS readers and XLS-to-XLSX or XLSX-to-XLS converters treat stored values as trusted input, so a file-level constant empty string survives even when the destination workbook keeps the default user-entry policy

Unknown symbols and target formats

Registered workbook-local or process-wide user functions and assigned OnUserFunction or OnUserFunctionEx callbacks count as known during validation without invoking a handler, while unregistered calls retain their function name in Symbol

Strict mode returns xlfeaReject for unknown functions or names; assigning through the normal cell Formula property rejects before changing the value, formula cache, or dependencies

Preservation mode retains the diagnostic and stores the formula through the normal cell API; Classic XLS serialises an unknown call through BIFF add-in user-function tokens and retains unknown-name text through save, reopen, and XLS/XLSX conversion

Classic XLS additionally compiles known formulas through the BIFF writer and returns xlfeiTargetFormatUnsupported when the expression cannot be represented, while XLSX accepts known modern formula functions supported by its formula model

XLSX validation recognises LAMBDA, lexical LET callables, defined names that refer to lambdas, report placeholders, registered external workbook links, and valid qualified or unqualified structured references including nested and current-row forms

Compatibility mode can also retain unresolved external structured references for later processing, while strict mode still rejects unresolved table names

Calculation and bulk audit

A preserved unknown function that remains unhandled after lambda and user-function dispatch, or a preserved unknown name, evaluates as a Variant error with code $1D, the Excel #NAME? error; the same typed error propagates through dependent formulas and contextual evaluation

Known built-in functions that are not implemented remain distinct from an unknown call, while registering, replacing, removing, or clearing a workbook-local or process-wide user function and changing a defined-name formula invalidates stale dependency and calculation state

function AuditFormulaSymbols: TXLSFormulaSymbolIssues; overload;
function AuditFormulaSymbols(
  out AStats: TXLSFormulaSymbolAuditStats):
  TXLSFormulaSymbolIssues; overload;

TXLSFormulaSymbolIssueKind = (xlfsiUnknownFunction, xlfsiUnknownName,
  xlfsiSyntaxError);
TXLSFormulaSymbolLocationKind = (xlfslCell, xlfslDefinedName);

TXLSFormulaSymbolIssue = record
  Kind: TXLSFormulaSymbolIssueKind;
  LocationKind: TXLSFormulaSymbolLocationKind;
  Sheet: Integer;
  Row: Integer;
  Col: Integer;
  DefinedNameIndex: Integer;
  DefinedName: WideString;
  Formula: WideString;
  Symbol: WideString;
end;

TXLSWorkbook and TXLSXWorkbook provide the same bulk-audit overloads; each issue identifies an unknown function, unknown name, or syntax error together with either a worksheet cell location or a defined-name location, the source formula, and the unresolved symbolLocationKind = xlfslCell) or a defined-name location (LocationKind = xlfslDefinedName), the source formula, and the unresolved symbol

The audit traverses parsed formula trees or stored BIFF tokens instead of scanning text, so function-like text inside a string is not reported; XLSX compilation uses an operation-local cache keyed by resolved formula text, worksheet, locale, defined-name generation, user-function registry generations, and callback availability

EntryCount reports audited formulas, UniqueFormulaCount reports formulas compiled or inspected independently, and CacheHitCount reports repeated entries served by the operation cache

No worksheet mutation

Validation does not create or edit a cell, change cached values, update the dependency graph, mark the workbook dirty, or invoke a registered user-function handler

Direct validation remains mutation-free; normal formula assignment applies the same decision internally and commits only after validation and target-format preparation succeed

Delphi example

Policy := Workbook.FormulaEntryPolicy;
Policy.UnknownFunctionMode := xlfusmReject;
Policy.UnknownNameMode := xlfusmReject;
Workbook.FormulaEntryPolicy := Policy;

Validation := Sheet.ValidateFormulaEntry(4, 2, FormulaText);
if Validation.Action = xlfeaStoreFormula then
  Sheet.Cells[4, 2].Formula := FormulaText;

Issues := Workbook.AuditFormulaSymbols(Stats);
for I := 0 to High(Issues) do
  if Issues[I].Kind = xlfsiUnknownFunction then
    LogUnknownSymbol(Issues[I].Symbol);