TryGetCachedFormulaValue method
Reads a stored formula result without evaluating the formula or changing workbook state
The method is available on both IXLSWorkbook and TXLSXWorkbook; the package workbook implementation covers XLSX and ODS files
Engine-neutral code can obtain an IXLSFormulaCacheReader from either engine through CreateFormulaCacheReader and use the same query method
Syntax
function TryGetCachedFormulaValue( SheetIndex, Row, Col: Integer; out AInfo: TXLSFormulaCacheInfo): Boolean;
Coordinates and return value
SheetIndex, Row, and Col are one-based
The method returns True only for a formula cell whose cache state is xlfcsLoaded or xlfcsCalculated
Invalid coordinates, absent cells, and non-formula cells return False with xlfcsNotFormula
A formula without a stored result returns False with xlfcsMissing; a formerly cached formula whose formula or calculation failed after mutation returns False with xlfcsInvalidated
Cache information
The TXLSFormulaCacheInfo record classifies the cache through two enums declared in lxStandard: State is a TXLSFormulaCacheState and Kind a TXLSFormulaCacheValueKind
| Field | Meaning |
|---|---|
State (TXLSFormulaCacheState) | xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated, or xlfcsInvalidated |
Kind (TXLSFormulaCacheValueKind) | xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean, or xlfcvError |
Value | The owned cached Variant when the method returns True, otherwise Unassigned |
Blank results, empty strings, Boolean false, numeric zero, and errors retain distinct kinds and are not used as proxies for cache presence
BIFF numeric caches remain Double because the record has no date type marker; error caches are returned as varError
For package workbooks, OOXML t="d" and ODF date values return varDate, OOXML t="e" returns varError, and an ODF cached blank is preserved with the void value type
Changing a formula invalidates its former cache, while assigning a cached value and then assigning its formula records a calculated cache
Each shared-formula member retains its own cache presence and value; a follower without a stored result reports xlfcsMissing instead of inheriting the root member's cache
No-calculation contract
The method performs a direct sparse-cell lookup and never invokes the calculator, a user-function callback, formula-token decompilation, dependency updates, cache writes, or dirty-state changes
Use the normal cell Value property when calculation is required
Reader lifetime
CreateFormulaCacheReader returns a read-only adapter backed by a workbook lifetime lease without transferring workbook ownership
After the workbook is destroyed, any query through that adapter raises EXLSFormulaCacheReaderInvalidated instead of dereferencing released workbook state
Example
Reader := Workbook.CreateFormulaCacheReader;
if Reader.TryGetCachedFormulaValue(1, 4, 2, Info) then
Memo1.Lines.Add(VarToStr(Info.Value))
else if Info.State = xlfcsMissing then
Memo1.Lines.Add('Formula has no stored result');