Formula tokens and user functions
HotXLS exposes caller-owned formula token streams for lossless inspection and guarded writeback in both workbook engines, together with hierarchical user-function registries for custom calculation
Token model
TXLSFormulaTokenClass, TXLSFormulaTokenKind, and TXLSFormulaTokenSource classify each TXLSFormulaToken and record whether it came from parsed text, classic BIFF bytes or another supported source
| TXLSFormulaToken | Kind, Opcode, BaseOpcode, TokenClass, RawBytes, TextValue, NumberValue, IntegerValue, ArgumentCount, FunctionIndex, ExternSheetIndex, Row1, Col1, Row2, Col2, Row1Relative, Col1Relative, Row2Relative, Col2Relative, ReferenceUsesOffsets, Supported, ArrayRows, ArrayCols, and ArrayValueCount, and ArrayValues |
| TXLSFormulaTokenStream | DecodeBiff, Clone, CloneSyntaxTree, BindContext, CanCompileFor, CanWriteBiffAt, ReplaceReference, Count, Items, Source, FormulaText, FullyDecoded, Rgce, RgbExtra, BiffWritable, and BiffVersion |
| TXLSCompiledFormula | CreateTokenStream returns a caller-owned clone of the compiled formula token stream |
| TXLSFormula | ArrayFormulaMode, FormulaLocale, MaxRow, MaxCol, FuncNamebyPtg, FuncArgsCntByPtg, GetCompiled, GetCompiledPreservingUnknownNames, GetCompiledR1C1, GetTokenStream, GetTranslated, GetStoreData, GetStoreDataExt, CanStoreData, BindUserDefinedFunctions, GetTExpData, SetMode, IsKnownFunction, GetFunctionNames, and FunctionArgumentClass |
Each workbook engine compiles and translates formulas through one shared TXLSFormula instance: GetCompiled parses A1-style text into a syntax tree, GetCompiledR1C1 resolves R1C1 references against the coordinates of the cell holding the formula, GetTranslated rebuilds a tree from classic BIFF bytes, and the GetStoreData family writes the token bytes back out for storage. MaxRow and MaxCol bound the reference grid the compiler accepts — the XLSX engine raises them from the BIFF8 defaults to the XLSX grid, and references that do not fit BIFF8 have no ptg form, so the BIFF8 writers report those references as not encodable
ArrayFormulaMode is set while the rgce of an ARRAY record (an array formula) is written: value-class references and array constants then serialise as array class (tAreaA / tArrayA), the way Excel itself writes them. With value-class operands Excel evaluates the array formula with implicit intersection rather than treating it as a true array formula, which is why the engines raise the flag for the ARRAY record's own rgce
Bounded inspection of a simple stored leaf
TXLSCompiledFormula.ReadSimpleLeafInfo(ExpectedOwner, MaxBytes, MaxTextCharacters, AllowUnboundBiff) inspects the main syntax tree and any retained token stream in place, without cloning, decompiling, evaluating or materialising tokens or arrays; the default AllowUnboundBiff is False
The supported shapes are one absolute REF3D or RANGE3D leaf and one string literal, with exactly one coherent token when a stream is retained; expressions, relative or offset references, arrays, external array data, unknown tokens, mismatched main and retained leaves and foreign bound owners return fslkUnsupported without changing cached values or calculation state
| TXLSFormulaSimpleLeafKind | fslkUnsupported, fslkRange, or fslkLiteral; inspect Kind before using the other result fields |
| TXLSFormulaLeafOwnerState | flosNoRetainedStream reports no token-stream provenance, flosUnboundStream reports imported unbound BIFF, flosSameOwner reports an exact bound owner match, and flosForeignOwner rejects a different bound owner |
| TXLSFormulaSimpleLeafInfo | A string-free value record containing Kind, OwnerState, RangeType, ExternId, FirstRow, FirstCol, LastRow, LastCol, TextCharacters, StoredTextCharacters, ReadBytes, SyntaxNodes, and TokenCount |
| Range coordinates and identity | Coordinates are zero-based; ExternId is the logical one-based syntax-tree ID, rather than the BIFF wire index; the caller must resolve it through the pinned workbook and reject external books, sheet spans, missing sheets or coordinates outside its supported capture range |
| Source costs | TextCharacters is the literal output length in UTF-16 code units, or zero for a range; StoredTextCharacters sums retained source text, token text, and inspected syntax text; ReadBytes sums their two-byte text cost and retained raw byte lengths, including separately retained representations; these counters describe the bounded inspection inputs, not total heap usage |
| Syntax and token counts | SyntaxNodes is one for a plain compiled leaf, or two when both main and retained leaves are checked; TokenCount is zero without a stream or one with a supported coherent retained stream |
| TXLSFormulaTokenStream.ReadSimpleLeafInfo | The lower layer takes the same owner and quotas, optional ComparedLeaf, and optional ReferenceContext; it checks the stream's own private syntax, token and raw bytes directly; ComparedLeaf adds main-leaf coherence checks, whilst ReferenceContext maps serialised text-formula extern indexes without copying |
| TXLSCompiledFormula.ReadSimpleLiteral | Takes the same arguments, repeats the leaf checks and returns a caller-owned WideString only once the remaining byte and character quotas also cover its output; unsupported leaves or insufficient remaining capacity raise EInvalidOperation |
ExpectedOwner must be the exact calculator attached to the consuming source and must remain alive and stable throughout inspection and literal extraction; a missing owner raises EArgumentException, negative quotas raise EArgumentOutOfRangeException, and oversized retained source inputs raise EInvalidOperation before content comparisons or output copying
A plain leaf without a retained stream does not establish its original owner; an unbound imported stream also carries no workbook identity, so explicit AllowUnboundBiff=True is appropriate only when the caller has already pinned the source calculator and will validate the local sheet identity before reading cells; imported reference inspection currently accepts BIFF8 references and rejects the distinct BIFF5 extern representation
The legacy syntax fields named FFirstRowRel, FFirstColRel, FLastRowRel, and FLastColRel are true for absolute coordinates; the helper also checks the independent token relative flags and offset representation, so consumers need not infer absolute coordinates from these historical names
Syntax tree
CloneSyntaxTree returns a caller-owned parsed tree of TXLSSyntaxItem nodes for structural inspection: each node reports its ItemType, literal payload (Value, IntValue, DecValue), leading Spaces, tree links (Parent, Child, ChildCount), the reference leaf as a TXLSRangeItem through Range, and array-constant data (ArrayRows, ArrayCols, ArrayValueCount, and the TXLSArraySerVal entries in ArrayValues). The tree is a detached copy, so walking it never touches the workbook's compiled formulas; the mutation members (AddChild, PushChild, CopyObject, OffsetCopyReferences, AdjustForShift, and TXLSRangeItem.CopyObject / AdjustForShift) exist for tools that rewrite a cloned tree before recompilation
Workbook entry points
Tokens := Worksheet.ParseFormulaTokens('SUM(A1:A4)', 1, 1);
try
if Tokens.ReplaceReference(OldRef, NewRef) then
Worksheet.SetFormulaTokens(1, 1, Tokens);
finally
Tokens.Free;
end;ParseFormulaTokens, GetFormulaTokens, FormulaTokensToText, and SetFormulaTokens keep ownership explicit and validate context, BIFF version, token support, array ownership and reference bounds before a cell is changed
User-function registries
TXLSUserFunctionResolver and TXLSUserFunctionRegistry provide by-name lookup through RegisterHandler, UnregisterHandler, Clear, Contains, TryGetHandler, and Count
Workbook.RegisterUserFunction('NETMARGIN', CalculateNetMargin);
Workbook.RegisterGlobalUserFunction('FISCALYEAR', CalculateFiscalYear);
try
Workbook.Calculate;
finally
Workbook.UnregisterUserFunction('NETMARGIN');
Workbook.UnregisterGlobalUserFunction('FISCALYEAR');
end;IXLSWorkbook, TXLSWorkbook, and TXLSXWorkbook expose RegisterUserFunction, UnregisterUserFunction, ClearUserFunctions, RegisteredUserFunctionCount, RegisterGlobalUserFunction, UnregisterGlobalUserFunction, ClearGlobalUserFunctions, and RegisteredGlobalUserFunctionCount
The equivalent unit-level entry points are XLSRegisterGlobalUserFunction, XLSUnregisterGlobalUserFunction, XLSClearGlobalUserFunctions, XLSRegisteredGlobalUserFunctionCount, and XLSGlobalUserFunctionRegistered
TXLSCalculator.OnUserFunctionResolve participates before the legacy event fallback, and registry handlers run outside the registry lock so a handler may unregister itself safely
Dangerous external and macro-style function names are blocked ahead of argument evaluation and handler lookup unless AllowUnsafeFormulaCallbacks is explicitly enabled for the workbook or an individual contextual evaluation; ordinary user-function names remain available by default
Formula diagnostics
TXLSXFormulaIssueKind, TXLSXFormulaLocation, and TXLSXFormulaIssue are returned by TXLSXWorkbook.AnalyzeFormulaIssues for missing functions, external references, and circular dependency components
Macro-sheet inspection
TXLSSheetKind distinguishes worksheet, macro sheet, chart sheet, VBA module and unknown sheet records through TXLSWorksheet.SheetKind; TXLSMacroRisk, TXLSMacroCommandInfo, and TXLSMacroCommandArray describe the typed results from TXLSWorksheet.ScanMacroCommands
Merged AutoFit and sorting types
TXLSMergedAutoFitStrategy and TXLSXMergedAutoFitStrategy choose first, last, or balanced growth for merged spans, while TXLSSortOrientation, TXLSSortFormulaMode, and TXLSSortCompareEvent control vertical or horizontal stable sorting and formula movement