A grand total that Excel shows as 37 can come back as 67 after a program opens the .xls file and saves it without editing a single cell. The extra 30 is not random. It is exactly the sum of the two group subtotals, 10 and 20, that sit inside the range the grand total covers. Two things have to go wrong together for that number to move: the save has to recompute every formula on the way out, and whatever does the recomputing has to forget that a SUBTOTAL skips other SUBTOTAL cells. Remove either one and the file survives, which is why the first problem can sit unnoticed behind the second
Why does saving an XLS file change a formula value?
A save changes a formula value when the writer computes the number it stores beside each formula instead of copying the one it loaded. Every BIFF8 Formula record carries an eight-byte FormulaValue field ([MS-XLS] §2.4.127, value layout in §2.5.133) that holds the result Excel last displayed for that cell. A writer has to put something in those eight bytes, and the shortest code path is to hand every formula to the calculation engine. Once that happens, each save is a full recalculation nobody asked for, and every spot where that engine and Excel disagree becomes a changed number in a file the user believes was untouched
The SUBTOTAL disagreement makes a good example, because the spreadsheet rule is simple and the trap sits in Delphi's Variant typing. Excel defines every SUBTOTAL function number as ignoring cells whose own formula is another SUBTOTAL. For a sheet whose detail rows add up to 37 (the rows under two groups that subtotal to 10 and 20, plus 7 more outside any group), the grand total is 37. An engine that fails to spot the nested subtotals counts the 10 and the 20 a second time and lands on 67. Spotting them usually means a short check over the formula text, and the obvious Delphi version compiles, runs and never matches anything:
// A helper you write yourself, for example when scanning a sheet
// for formulas that an enclosing SUBTOTAL has to skip
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// The tempting test never succeeds on Unicode Delphi: a String
// assigned to a Variant is varUString, not varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // accepts varString, varOleStr and varUString
Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
SameText(Copy(F, 1, 10), '=SUBTOTAL(') or
SameText(Copy(F, 1, 10), 'AGGREGATE(') or
SameText(Copy(F, 1, 11), '=AGGREGATE(');
end;
// Usage with HotXLS (sheets, rows and columns are 1-based):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr is the test the HotXLS evaluator applies to the same question, and it also treats AGGREGATE cells as excluded from an enclosing subtotal, the way Excel does. Fixing the predicate is not the whole story, though. With a correct check, the recalculating save writes 37 only because the engine happens to agree with Excel on this particular file. The next formula the engine handles slightly differently will move again. The rules for which cells SUBTOTAL and AGGREGATE skip, hidden rows included, are covered in the SUBTOTAL and AGGREGATE hidden-row article. With cache-first saving, a file you never asked to compute goes back to disk with 37 in the cell, whatever the engine would have made of it
What a classic XLS save writes into each Formula record
HotXLS saves a BIFF8 workbook cache first: for each formula cell, TXLSWorkbook.SaveAs writes the cached value whenever TryGetCachedFormulaValue would report one, and runs the evaluator only when it would not. The cache state decides it. A cell in state xlfcsLoaded (the value came from the source file) or xlfcsCalculated (HotXLS computed it during this session) has its CacheInfo.Value written byte for byte. A cell in state xlfcsMissing or xlfcsInvalidated is evaluated at save time. That matches how Excel treats a save: a snapshot of what each cell shows, which in manual calculation mode can be badly out of date, written faithfully anyway
The evaluation path stays for good reasons. A formula you assign in code through Cells[Row, Col].Formula has no cache yet, and assigning a new formula to a loaded cell moves it to xlfcsInvalidated, because the old result belongs to the old expression. Both get computed during the save, so a workbook generated from scratch opens in Excel showing computed numbers instead of zeros. If even the evaluator cannot produce a value (an unsupported function, a broken external link), the writer stores a zero and sets fAlwaysCalc, bit 0 of the record's flags in §2.4.127, so Excel recomputes the cell on open instead of trusting the placeholder
One property looks related and is not. TXLSWorkbook.RecalcOnSave mirrors Excel's "Recalculate workbook before saving" option from the CalcSaveRecalc record ([MS-XLS] §2.4.37). It matters only to Excel, only in manual calculation mode, and HotXLS simply writes it into the file. SaveAs does not consult it when deciding what goes into FormulaValue. The round trip below is the quickest way to see the contract on one of your own files:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Sheet, row and column are 1-based
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // cached cells are written as stored
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Expected: After.Value equals Before.Value, and After.State is
// xlfcsLoaded again, because it came from the saved file
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;The read side of this contract, including what each state means and why a cached blank or False still counts as a value, is described in Read Excel Cached Formula Values in Delphi Without Recalc. Run the check above on the grand total cell of a workbook with nested subtotals and both lines print the same number
Where does a BIFF shared formula root keep its cached value?
In its own Formula record, like every other formula cell, but the expression arrives one record later, and that gap is where a careless reader loses the cache. BIFF8 stores a shared formula as a ShrFmla record ([MS-XLS] §2.4.260) placed right after the Formula record of the group's top-left cell. Every member cell, the root included, carries an rgce made of a single PtgExp token (§2.5.198): byte $01 followed by the row and column of the root. Follower cells are easy, since by the time they appear the root's expression already exists and their own FormulaValue sits in their own record. The root is not easy. When its Formula record is read, it has a cached value of 37 and no expression yet
Installing an expression on a cell clears its cache, and that has to stay true, because a replaced formula really does make the old result meaningless. Applied blindly to the ShrFmla record, though, the same rule throws away the root's 37 the moment the expression arrives, TryGetCachedFormulaValue reports the root as xlfcsMissing, and the save falls back to the evaluator for exactly the cell a user is most likely to look at. The HotXLS reader holds the root's decoded value whilst it waits for the ShrFmla record, installs the expression, then puts the loaded value back with state xlfcsLoaded. Array formulas (the Array record, §2.4.4) follow the same record order and get the same treatment. The String form of the cache never had this problem, because its text arrives in a separate String record and is matched to the cell by its row and column, so its position in the stream never matters
If you also handle the OOXML side, the XLSX shared formula si expansion article explains why the package format has no record-order gap but brings its own expansion pitfalls
Shared formula followers own a shifted copy of the expression
The expression in a ShrFmla record is written relative to the root cell, so each follower needs its own copy displaced by its distance from the root. Copy it verbatim and a group rooted at B1 with =A1*3 hands the same =A1*3 to B2 and B3. HotXLS gives each follower a copy whose relative references are offset by the follower's row and column distance, so B2 owns a real =A2*3 and B3 owns =A3*3
That is also why a plain round trip cannot tell you whether follower expressions are right. Followers keep their loaded values through a save, so a verbatim copy would pass. A check that proves anything has to change an input and recalculate. The routine below writes a shared group with UseSharedFormulas switched on (it defaults to False, which writes one Formula record per cell), reopens the file, edits A2 and watches B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 become one ShrFmla group
Book.SaveAs(FileName); // no caches yet: evaluated to 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // edit the follower's input
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Expected: 12, still loaded
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Expected: 30, from =A2*3
finally
Book.Free;
end;
end;A follower that had reused =A1*3 would print 6 on the second line. The first line matters just as much: it shows that editing a literal leaves the dependent formula's loaded value in place until something recalculates
What cache-first saving leaves to you
Cache-first saving preserves what was loaded. It does not track whether what was loaded is still true. Changing a cell that a formula depends on marks the dependency graph for the next recalculation, but the dependent cell keeps its xlfcsLoaded value, and SaveAs writes that stale number unless you call Recalculate first or read the cell's Value, which computes the formula and moves it to xlfcsCalculated. Recalculate returns the number of formula cells it could not evaluate, so a nonzero result is worth logging before the save
In the common pipeline a program opens a workbook somebody else built, fills in a few labels or a header, and saves. Nothing in that file should change value, and with cache-first saving nothing does. A program that edits inputs owns the recalculation step instead, and that step belongs in one visible Recalculate call next to the edits, where anyone reading the code can see it. Excel makes the same trade in manual calculation mode. The XLSX writer has its own policy, RecalcBeforeSave with the modes xrmManual, xrmIfDirty and xrmAlways, set on the XLSX workbook class and independent of everything above
Two boundaries follow from the design. The cache path only helps cells in state xlfcsLoaded or xlfcsCalculated, so a generator that writes thousands of new formulas and never evaluates them still pays one evaluation per cell during the save. And the contract governs what a save writes, not what an explicit recalculation computes: an untouched file keeps the numbers Excel stored, whilst a Recalculate writes the HotXLS evaluator's results. Compare the two on a few representative workbooks before switching a pipeline over to recalculated saves
The HotXLS Delphi Spreadsheet Component reads and writes classic BIFF8 and XLSX workbooks from Delphi and C++Builder without Excel or OLE automation installed. Its product page documents the workbook, cached-value and recalculation calls used in these examples