Excel hides a small debugger in plain sight. Select a cell, open Formulas and click Evaluate Formula, and a dialog shows the formula with one subexpression underlined. Press Evaluate and that subexpression collapses into its value, then the next one underlines, and you watch a long expression shrink to a single number one reduction at a time. It is the fastest way to find which branch of a nested IF actually fired, or which reference fed a wrong total. HotXLS reproduces that exact behaviour through TXLSFormulaTracer, so a Delphi or C++Builder program can render the same step list for auditing a workbook, debugging a generated formula, or teaching someone why a result came out the way it did. Each recorded step carries the subexpression text and the value it reduces to
How the reduction engine walks the expression
The tracer does not reach into the calculation engine. It tokenizes the formula and parses it with a recursive-descent parser, then reduces the tree depth-first, innermost evaluable subexpression first. When a node reduces to a value, that value is substituted back into the surrounding expression as a literal, and the engine asks the real calculator to recompute the now-simpler expression. Because every step is evaluated through the worksheet's public Calculate method rather than a private shortcut, each step agrees exactly with what a full recalculation of the cell would produce. The parser is non-invasive by design, which is what lets it run against any worksheet without disturbing its state
The parser follows an operator-precedence ladder, with one recursive level per precedence band. From lowest binding to highest the bands are: level 0 comparison (=, <>, <, >, <=, >=), level 1 string concatenation (&), level 2 addition and subtraction, level 3 multiplication and division, level 4 exponentiation, and finally unary plus and minus below that. Each level parses the level above it for its operands, so a higher band binds tighter. This is the same precedence Excel applies, which is why A1*B1+A2*B1 reduces the two products before the sum: multiplication sits at level 3, addition at level 2, so the multiplications are deeper in the tree and reduce first
Tracing a formula and walking the steps
Usage mirrors the shipped demo at Demo/Delphi/FormulaTrace/FormulaTrace.dpr. Build a worksheet (or open an existing workbook), construct a tracer over the sheet, call Trace, and iterate the returned array. Each TXLSFormulaStep exposes Depth for indentation, Source for the original subexpression, Expression for that subexpression with its operands already substituted, and Value for the result of the step
uses
SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Tracer: TXLSFormulaTracer;
Steps: TXLSFormulaStepArray;
Final: Variant;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Order');
Sheet.Cells[1, 1].Value := 10; // A1 units
Sheet.Cells[1, 2].Value := 25; // B1 unit price
Sheet.Cells[1, 3].Value := 0.08; // C1 tax rate
Tracer := TXLSFormulaTracer.Create(Sheet);
try
Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
for I := 0 to High(Steps) do
Writeln(StringOfChar(' ', Steps[I].Depth * 2),
Steps[I].Source, ' -> ', Steps[I].Expression,
' = ', VarToStr(Steps[I].Value));
Writeln('result = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;The cell references resolve first and appear as their own steps, then the products reduce, then the parenthesized tax factor, and the final multiplication closes it out. The Depth field lets you indent so the innermost reductions visibly sit deepest, exactly as Excel underlines the innermost term before any outer one
The locale-free literal trap
The most dangerous detail in this whole scheme is invisible on an English machine and breaks loudly on a German one. When a computed number is substituted back into the formula text, it has to be written as a string and then re-parsed by the calculation engine, which treats . as the decimal point. If the substitution used the system locale, a German TFormatSettings would write 1,08 for the tax factor, the comma would be read as an argument separator, and the recomputation of A1*B1*1,08 would either parse into the wrong shape or fail outright
The tracer avoids this by formatting every numeric literal through a private TFormatSettings that it pins at construction, with DecimalSeparator forced to . and ThousandSeparator set to #0 so no grouping character is ever emitted. FloatToStr then produces a literal the engine can always read back, regardless of the operator's regional settings
// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)This is the kind of bug that never appears in the author's own testing and only surfaces when a customer in another locale runs the same code, so it is worth stating plainly: round-tripping a value through formula text is a serialization problem, and serialization must be locale-free
Booleans reduce to 1 and 0
A related substitution decision concerns logical values. When a subexpression evaluates to a boolean, the tracer writes it back as 1 or 0, not as TRUE or FALSE. The reason is that the reduced literal has to re-parse cleanly in whatever context surrounds it, and arithmetic is the demanding case. If a comparison like A1>A2 reduced to the text TRUE and that text landed inside TRUE*B1, the recomputation would depend on the engine accepting a bare boolean keyword in a multiplication. Substituting 1 sidesteps the question entirely, because 1*B1 is unambiguous in any arithmetic position. It also matches Excel's own coercion, where TRUE behaves as 1 and FALSE as 0 the moment a number is expected
Function calls reduce atomically
A naive step engine would reduce a function's arguments first and then the call. That is wrong for Excel, and the tracer deliberately does not do it. A function call is evaluated as a whole, from its original text, in a single step. The reason is short-circuit semantics. IF, CHOOSE and IFERROR evaluate only the branch they select, and reducing arguments first would force the engine to compute branches Excel never touches. The classic casualty is a divide-by-zero guard such as IF(B1=0,0,A1/B1): if the tracer reduced A1/B1 before evaluating the IF, the guard would misfire and raise the very error it exists to prevent. By evaluating the whole call atomically, the tracer preserves the lazy evaluation that makes such guards work
// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.The trade-off is that you do not see inside the function call as separate steps, but that is the correct behaviour. Showing argument reductions Excel never performs would be a more misleading trace than treating the call as the single evaluation unit it really is
Argument separators and intact ranges
Two more normalizations keep recomputation honest. The calculation engine's compiler expects ; as the function argument separator, so when the tracer rebuilds a function call from its parsed tree it joins arguments with ;, even if the user originally typed ,. A formula written as SUM(A1,A2,A3) is recomputed as SUM(A1;A2;A3), which the engine accepts. The substitution of values is what makes this rebuild necessary, and getting the separator right is what makes the rebuild parse
Range references are the other case. A range such as A1:A3 is not a scalar and must not be split into three separate values, because the function that consumes it expects a range argument. The tracer keeps a range intact as its original text and lets the enclosing function reduce as a whole. In SUM(A1:A3)*B1 the range stays whole, SUM(A1:A3) reduces to one number in one atomic step, and only then does the outer multiplication run. This is the same boundary Excel draws between a range operand and the scalar it eventually contributes
// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));Put together, these rules make the step list a faithful mirror of Excel's Evaluate Formula command rather than an approximation of it. The reductions happen in the order Excel performs them, the substituted literals survive any locale, booleans coerce the way Excel coerces them, and lazy functions stay lazy. If you want to push the engine further with your own functions, the formula engine and custom functions article shows how to register them, and for heavier numeric work the statistical distribution functions in Delphi article covers the built-in library the tracer evaluates against. All of it ships as part of the HotXLS spreadsheet component for Delphi and C++Builder, alongside the reading, writing, formatting, and calculation APIs covered elsewhere on this blog