Excel only trusts a cached #SPILL! or #CALC! when the workbook carries the full modern error contract: Rich Data metadata whose XLRICHVALUE extension URI is the lowercase GUID, a fileVersion of 7, a calcId of at least 191029, and the microsoft.com:RD calculation feature. HotXLS, the native Delphi XLSX component, emits all four whenever a saved cell holds one of those errors
Miss any piece and the failure is rarely loud. Sometimes Excel 16 refuses to open the file at all. More often it opens fine and shows #VALUE! where your calculation engine produced #SPILL!, which looks like a calculation bug and sends you hunting through formula code that is perfectly correct. Press F9 and the right error appears, so the problem tends to get closed as "works on my machine" until a customer opens the report with automatic calculation off. This post walks through what each piece does, the GUID casing trap that looks like it cannot possibly matter, and the array text cache rule that fails in exactly the same quiet way
Why does Excel show #VALUE! instead of a cached #SPILL!?
Excel shows #VALUE! because that is literally what the cell stores. The legacy error type in ECMA-376 SpreadsheetML has a fixed list of error literals, and #SPILL! and #CALC! are not on it. A cell with t="e" can only say #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! or #N/A, so newer Excel versions write #VALUE! as the backward-compatible fallback and hang the real error type off a value-metadata index
That index, the vm attribute, points into xl/metadata.xml, which points into a Rich Data value describing the actual error. Older readers see #VALUE! and move on. Current Excel follows the chain and displays the modern error, but only if it decides the chain is trustworthy. Here is what a spilled-and-blocked formula looks like on disk:
<!-- sheet1.xml: legacy literal plus a value-metadata pointer -->
<c r="A1" t="e" cm="1" vm="1">
<f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
<v>#VALUE!</v>
</c>
<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
<k n="errorType" t="i"/>
<k n="colOffset" t="i"/>
<k n="rwOffset" t="i"/>
</s>
<!-- xl/richData/rdrichvalue.xml: errorType 8 is #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>HotXLS writes error type 8 with column and row offsets for #SPILL!, and error type 13 with no offsets for #CALC!. The legacy <v> stays #VALUE! in both cases on purpose, because that is the value any reader that ignores Rich Data is supposed to see
The lowercase GUID that Excel 16 actually checks
The XLRICHVALUE future-metadata block must use the extension URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} in lowercase, character for character. Uppercase the hex letters and the local Excel 16 build refused to open the workbook normally, even though every XML part parsed, every relationship resolved, and every count and index lined up
<futureMetadata name="XLRICHVALUE" count="1">
<bk>
<extLst>
<ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
<xlrd:rvb i="0"/>
</ext>
</extLst>
</bk>
</futureMetadata>
<valueMetadata count="1">
<bk><rc t="2" v="0"/></bk>
</valueMetadata>Everyone who has worked with COM or the registry knows GUIDs compare case-insensitively, and that habit is exactly what bites here. We isolated it the slow way: built a minimal package that Excel rejected, then swapped one thing at a time. Replacing the content types did not fix it. Adding a rich value types part did not fix it. Changing namespace prefixes or rewriting the rich value data did not fix it. Lowercasing that single uri attribute fixed it, and the same package then opened and displayed both #CALC! and #SPILL!. Treat the extension URI as an opaque string literal, copy it from the format definition, and never run it through a GUID formatter that picks its own casing
There is a practical corollary for anyone writing diagnostic packages by hand. Do the edits with an XML parser, not with a global text replace over serialized XML. A blanket substitution aimed at one token can corrupt entities such as > elsewhere in the part, and then you are debugging a broken probe instead of the original question
What else does Excel need to read a cached modern error?
The metadata parts and their relationships are necessary but not sufficient. With a perfectly valid Rich Data chain in place, Excel still ignored the cached error until the workbook part declared itself as coming from a modern calculation engine. HotXLS writes three declarations into workbook.xml whenever a saved cell holds #SPILL! or #CALC!:
<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
<ext uri="...">
<calcFeatures>
<feature name="microsoft.com:RD"/>
</calcFeatures>
</ext>
</extLst>The fileVersion element is plain ECMA-376 Part 1 workbook metadata, and lastEdited / lowestEdited of 7 tell Excel the file was last saved by an application generation that understands Rich Data. The calcId on calcPr identifies the calculation engine version, and 191029 is the minimum we found Excel accepting for these caches. The microsoft.com:RD entry in the calculation features extension, in the http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures namespace, declares that the engine that produced the cached values evaluated with Rich Data semantics. Supplying only the subtype, only the types part, or only different prefixes and relationship paths did not change the outcome. All three declarations had to be present together
Three details in how HotXLS applies them are worth knowing if you set these values yourself:
TXLSXWorkbook.CalcIdis not overwritten. The writer raises the emitted value to 191029 only in the output, so a caller who configured a higherCalcIdkeeps it, and the in-memory property stays exactly what you assigned- Existing calculation features and unrelated workbook extensions that came in with an opened file are preserved, not replaced
- The writer checks for an existing
microsoft.com:RDfeature before adding one, so saving the same workbook repeatedly never stacks duplicate declarations
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('forecast.xlsx');
Sheet := Book.Sheets[1];
// A1 spills three rows; A2 is occupied, so the result is #SPILL!
Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
Sheet.Cells[2, 1].Value := 'blocker';
Book.Recalculate;
// A higher CalcId survives; the writer only raises it to 191029 if lower
Book.CalcId := 191029;
Book.SaveAs('forecast-out.xlsx'); // rich error parts written automatically
finally
Book.Free;
end;
end;You never build the Rich Data parts yourself. Before writing, TXLSXWorkbook scans every cell for #SPILL! or #CALC! values, creates or extends xl/metadata.xml and the two rich value parts, and drops stale value-metadata links from cells whose error has since changed or cleared. The explicit CalcId assignment above is optional and only shown to make the point that it is respected
Array text caches belong in t="str", not shared strings
The same quiet failure shows up for text results inside arrays. HotXLS saves the cached text of a formula cell, and of every follower cell in a CSE or dynamic array range, as t="str" with the literal string in <v>. Writing those caches as shared strings with t="s" makes Excel display #VALUE! in the array cells when it reads the cached file
<!-- Correct: literal text cache on an array follower -->
<c r="B3" t="str"><v>Northwind</v></c>
<!-- Wrong: shared-string index, Excel shows #VALUE! from the cache -->
<c r="B3" t="s"><v>17</v></c>This one is easy to get wrong because t="s" is the right choice for ordinary value cells, and most writers route every string through the shared string table by default. The trap is in the verification. Recalculate in Excel and the array cells show the correct text, which proves the formula is fine and proves nothing about the cache you wrote. If your users open reports with manual calculation, or a downstream tool reads cached values without calculating, the shared-string version is simply broken
Formula storage syntax: leading equals, commas and _xlws
A related cleanup lands in the same writer: formulas going into OOXML are stored in the file grammar, not the grammar a user typed. HotXLS keeps whatever text you assigned to Formula for round-trip display, but the serialized formula drops the leading =, uses commas between function arguments, and leaves array constant row separators and quoted text alone. FILTER and SORT are stored with the _xlfn._xlws. prefix that Excel expects for those worksheet-only functions, while other future functions such as SEQUENCE get the plain _xlfn. prefix
// Typed with semicolon separators, stored as
// _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate; // no row above 1000 gives #CALC!, saved with Rich DataOrder matters inside the writer. XML escaping runs after the syntax normalization, never before, for the same reason the diagnostic packages need a parser: a separator rewrite applied to already escaped text would mangle entities like > in comparison operators
How to verify a cached error without fooling yourself
The only trustworthy test is to open the file in Excel with calculation already set to manual, read the cells, and only then recalculate. Excel inherits calculation mode from the first workbook opened in a session, so our acceptance harness first creates its own seed workbook, switches it to manual, then opens the file under test. Probes that skipped the seed step only proved the file opened and calculated correctly. Whatever they labeled as cached had already been recomputed
That distinction is the whole article in miniature. A modern error cache is correct when Excel displays it before any calculation runs, and every requirement above, from the lowercase GUID to t="str" text followers, was found by checking that state rather than the recalculated one. For the background on how HotXLS sizes and owns spill ranges in the first place, see dynamic array spill formulas in Delphi with HotXLS. If your symptom is a repair prompt rather than a wrong error, the package-level rules in why Excel repairs a valid XLSX are the better starting point
FAQ
Does HotXLS need any setting to save #SPILL! and #CALC! correctly?
No. HotXLS detects #SPILL! and #CALC! values at save time and writes the Rich Data parts, the fileVersion, the minimum calcId and the microsoft.com:RD feature automatically
Why does the cell XML still contain #VALUE!?
The legacy SpreadsheetML error type has no literal for #SPILL! or #CALC!, so #VALUE! is the backward-compatible fallback. Current Excel reads the real error through the vm value-metadata link
Will HotXLS lower a CalcId I set myself?
No. TXLSXWorkbook.CalcId is only raised to 191029 in the written file when a modern error is present, and a higher value you configured is kept as is
HotXLS reads and writes XLSX, XLSM and legacy XLS from Delphi and C++Builder with its own calculation engine, dynamic arrays included. Details and a trial download are on the HotXLS Delphi spreadsheet component page