Fuite du garde AGGREGATE imbriqué dans HotXLS en Delphi

HotXLS, le composant tableur Excel natif pour Delphi et C++Builder, a livré deux correctifs AGGREGATE liés en septembre 2026. La version 2.382.0 a corrigé l'argument options pour que les codes 1/3/5/7 ignorent les lignes masquées, 2/3/6/7 ignorent les erreurs, et 0 à 3 ignorent les cellules SUBTOTAL et AGGREGATE imbriquées, exactement comme le documente Microsoft. La version 2.382.3 a ensuite empêché ces drapeaux de sélection de fuir dans l'évaluation des cellules mêmes que la fonction référence. Le premier défaut est embarrassant de la façon dont les bugs de transcription de table le sont toujours : les positions de bits étaient interverties, donc chaque formule utilisant un code d'options non nul recevait une politique que son auteur n'avait pas demandée. Le second est plus intéressant, parce que c'est une forme que vous rencontrerez dans tout évaluateur qui utilise un champ transitoire pour faire passer du contexte dans un parcours récursif. Une agrégation extérieure arme un drapeau, parcourt une plage et tire une cellule dont la formule n'a pas encore été calculée. Cette formule s'exécute sur le même calculateur, voit le même drapeau armé, et agrège silencieusement les mauvaises lignes, produisant un nombre qui s'écarte d'une quantité que personne ne peut expliquer à partir du seul texte de la formule

Que sélectionnent réellement les options AGGREGATE 0 à 7 ?

L'argument options d'AGGREGATE est une matrice de trois bits, et les trois bits sont indépendants. Le bit 0 (valeur 1) signifie ignorer les lignes masquées, le bit 1 (valeur 2) signifie ignorer les valeurs d'erreur, et le bit 2 (valeur 4) signifie cesser d'ignorer les cellules SUBTOTAL et AGGREGATE imbriquées, parce que les sauter est le défaut des codes bas. Deux choses là-dedans s'inversent facilement. Le bit des lignes masquées est le bit bas, pas celui du milieu, donc AGGREGATE(9,1,...) est la forme total filtré et AGGREGATE(9,2,...) celle tolérante aux erreurs. Et la politique des agrégats imbriqués est inversée par rapport aux deux autres : seuls les codes 4 à 7 traitent une cellule dont la propre formule est un SUBTOTAL ou un AGGREGATE comme une valeur ordinaire. ECMA-376 Partie 1 §18.17.7 définit SUBTOTAL avec le même partage inclure-ou-exclure des lignes masquées entre les codes 1-11 et 101-111, et AGGREGATE, stocké dans les fichiers OOXML sous le préfixe _xlfn., généralise ce partage dans l'argument options, donc la table que Microsoft publie pour la fonction AGGREGATE est le contrat qu'un moteur doit remplir, pas une commodité

Option	Lignes masquées	Valeurs d'erreur	SUBTOTAL / AGGREGATE imbriqué
0	incluses	propagées	ignorés
1	ignorées	propagées	ignorés
2	incluses	ignorées	ignorés
3	ignorées	ignorées	ignorés
4	incluses	propagées	inclus
5	ignorées	propagées	inclus
6	incluses	ignorées	inclus
7	ignorées	ignorées	inclus

Pourquoi HotXLS avait-il les options AGGREGATE à l'envers ?

Parce que le TXLSCalculator.CalcAggregateFunc d'origine avait été écrit d'après une paraphrase de la table plutôt que d'après la table. Il calculait ignoreErrors := (optCode >= 4) and (optCode <= 7) et armait le garde des lignes masquées pour les codes 2, 3, 6 et 7, tandis que la politique des agrégats imbriqués n'était pas implémentée du tout. L'article précédent sur les lignes masquées de SUBTOTAL et AGGREGATE listait cette lacune comme une limite ouverte et décrivait l'ancien mapping tel qu'il était alors livré ; la description était exacte sur le code et fausse sur Excel, et personne ne l'a remarqué pendant longtemps parce que les deux politiques que la plupart des gens combinent, masquées plus erreurs, tombent sur les codes 3 et 7 dans les deux tables. Seul un code à un seul bit exposait l'inversion : AGGREGATE(9,1,A1:A4) renvoyait la somme non filtrée, et AGGREGATE(9,2,...) sautait les lignes masquées tout en propageant encore #DIV/0!. Le défaut a émergé d'une revue statique de lxCalc.pas, consignée comme HXLS-008 dans le registre des problèmes connus du projet, et non d'un fichier client, ce qui dit quelque chose sur la rareté des codes à un seul bit dans les classeurs de production. La version 2.382.0 a réécrit le décodage en trois tests d'appartenance à un ensemble et ajouté un second garde pour la politique imbriquée, câblé via un nouveau callback TXLSIsSubtotalCell que le classeur fournit aux côtés de TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, forme v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel rejette les codes hors 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... mapper function_num vers l'iftab interne, parcourir ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Remarquez que les deux drapeaux sont affectés sans condition plutôt que définis uniquement quand l'option les demande. La version 2.382.0 utilisait encore if ... then FIgnoreHiddenRows := True, ce qui signifiait qu'un AGGREGATE avec le code 4 imbriqué dans un SUBTOTAL(109, ...) héritait du garde des lignes masquées extérieur au lieu de le désactiver. Affecter la valeur décodée à l'entrée et restaurer la valeur précédente dans le bloc finally fait que chaque appel AGGREGATE possède sa politique pour la durée de son parcours et rien de plus. La version 2.382.0 a aussi rendu la forme tableau honnête : quand un argument s'évalue en tableau Variant à une ou deux dimensions, CalcAggregateFunc parcourt désormais chaque élément et applique la politique d'erreur par élément, là où l'ancien code ne testait qu'un double NaN et remettait sinon tout le tableau à ExcelSum

Pourquoi un AGGREGATE extérieur fuit-il dans les formules qu'il référence ?

Parce que FIgnoreHiddenRows et FIgnoreSubtotalCells sont des champs du calculateur, et que le calculateur est partagé par chaque formule évaluée pendant un recalcul. Les gardes ont été conçus comme des champs de travail précisément pour que six boucles de parcours de cellules puissent les consulter sans faire passer un paramètre dans chaque signature, et cette conception tient tant que tout ce qui s'exécute pendant qu'un garde est armé appartient à l'agrégation qui l'a armé. L'hypothèse casse en un point précis : FGetValue. Quand un parcours demande au classeur la valeur d'une cellule et que cette cellule contient une formule sans résultat en cache, le classeur compile la formule et l'évalue sur-le-champ, sur le même TXLSCalculator, avec les gardes extérieurs encore positionnés. La fixture de régression dans HotXLS.WorkbookApiTests.pas montre la défaillance avec quatre cellules. A1 contient 10, A2 contient 20 sur une ligne masquée, A3 contient =1/0, et A4 contient =SUBTOTAL(9,A1:A2), dont la valeur correcte est 30. Évaluez maintenant =AGGREGATE(9,7,A1:A4) : ignorer les lignes masquées, ignorer les erreurs, compter le sous-total imbriqué comme une valeur. Excel renvoie 10 + 30 = 40. Avec A4 non mise en cache, le moteur d'avant la 2.382.3 armait le garde des lignes masquées, allait jusqu'à A4, déclenchait son évaluation, et CalcSubtotalFunc pour le code 9 héritait du garde armé, parce qu'il ne fait que positionner le drapeau pour les codes 101 à 111 et ne l'efface jamais. A4 s'évaluait à 10 au lieu de 30, et le total extérieur revenait à 20. Rien dans l'une ou l'autre formule ne mentionne les lignes masquées sur le chemin qui a produit le mauvais nombre

Le garde des agrégats imbriqués fuyait de la même façon dans l'autre sens. Avec les codes 0 à 3, FIgnoreSubtotalCells est armé, et le parcours de plage générique de GetValueItemRange l'honore, donc un précédent dont la formule est =SUM(B1:B3) écarterait silencieusement B2 si B2 se trouvait contenir un SUBTOTAL. Pire, CalcSubtotalFunc remet FIgnoreSubtotalCells à False en sortie plutôt que de restaurer la valeur précédente, donc un précédent SUBTOTAL non mis en cache atteint au milieu du parcours désarmait le garde extérieur pour toutes les cellules suivantes. Le registre des problèmes connus du projet classe cela sous HXLS-008 comme fuite d'état de sélection imbriqué, et c'est le bon nom pour cette classe de bug : un drapeau transitoire global qui est correct pour la trame qui l'a posé et faux pour chaque trame qui l'hérite

Comment AggregateGetCellValue et AggregateGetItemValue isolent le parcours

Le correctif de la v2.382.3 pose une frontière autour de chaque point où AGGREGATE lit une valeur qu'il n'a pas calculée lui-même. TXLSCalculator.AggregateGetCellValue enveloppe l'appel brut à FGetValue : il sauvegarde les deux drapeaux, les efface, effectue la récupération, et les restaure dans un bloc finally. L'agrégation extérieure applique toujours sa propre politique à la cellule qu'elle vient de récupérer, parce que les tests de ligne masquée et de cellule imbriquée se font dans le parcours autour de la récupération, mais la formule précédente elle-même s'exécute sans aucune politique, ce que fait Excel

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // une formule précédente possède sa propre politique
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue fait de même pour les arguments hors plage, et il doit faire plus qu'effacer des drapeaux, parce qu'un argument comme A1:A4/(B1:B4-20) est un tableau calculé dont la forme des éléments doit survivre. Le wrapper matérialise une plage simple en tableau Variant à deux dimensions via AggregateGetCellValue, en ramenant une cellule qui a renvoyé un code d'erreur à VarAsError pour que la politique d'erreur puisse encore s'appliquer par élément, et il descend récursivement dans les nœuds d'opérateurs binaires et unaires (SA_ADD, SA_DIV, SA_UNARMINUS et les autres) avec ApplyArrayBinaryOp et ApplyArrayUnaryOp ; tout le reste retombe sur le GetValueItem habituel. Deux gardes précèdent la matérialisation : une plage plus grande que EffectiveFormulaArrayMemoryLimit renvoie lxErrorResourceLimit, et une plage multi-feuilles ou inversée renvoie #VALUE!. Un code de limite de ressources n'est délibérément pas traité comme une erreur de cellule ignorable, même sous les options 2/3/6/7, puisqu'un moteur qui avalerait son propre signal de mémoire insuffisante parce que l'utilisateur a demandé à sauter #N/A mentirait. Les trois parcours AGGREGATE, AggregateCollectRange pour la famille SUM, AggregateReduceVariance pour STDEV, VAR et PRODUCT, et AggregateReduceWithK pour MEDIAN et les formes quantiles, sont passés de FGetValue et GetValueItem aux deux wrappers, et chacun a gagné le test de cellule imbriquée via FIsSubtotalCell

Quelle erreur AGGREGATE renvoie-t-il quand il n'ignore pas les erreurs ?

L'originale, depuis la v2.382.3. La version 2.382.0 détectait correctement les cellules d'erreur mais les écrasait toutes en lxErrorValue, donc AGGREGATE(9,4,A1:A3) sur une cellule #DIV/0! renvoyait #VALUE!, là où Excel propage la première erreur qu'il rencontre sans y toucher. Le helper de remplacement AggregateErrorCode fait correspondre un Variant au code lxError* adéquat, que le Variant soit un vrai varError ou l'une des sept chaînes d'erreur, et AggregateValueIsError n'est plus qu'un test de résultat non nul. Chaque parcours enregistre le premier code d'erreur qu'il voit et renvoie ce code, ce qui signifie aussi qu'une cellule dont la formule n'a jamais été calculée, et dont l'erreur arrive donc comme code de retour de FGetValue plutôt que comme Variant en cache, se propage de la même façon qu'une cellule en cache. Deux fonctions de comptage reçoivent un traitement particulier dans AggregateCollectRange, et ce traitement suit SUBTOTAL plutôt que SUM. Pour la fonction interne 0, COUNT, une cellule d'erreur n'est jamais comptée et jamais propagée quel que soit le code d'options, parce que COUNT ne compte que des nombres. Pour la fonction interne 169, COUNTA, une cellule d'erreur est une valeur non vide et compte pour 1 sauf si le code d'options ignore les erreurs, auquel cas elle est sautée. Cette asymétrie est la façon dont Excel traite COUNT et COUNTA en dehors d'AGGREGATE également, et c'est le genre de détail qu'une règle générique « si erreur alors propager » rate en silence

Ce que vérifie la matrice de régression à huit options

La fixture décrite plus haut est exercée comme matrice complète dans AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates : pour chaque code d'options de 0 à 7, elle évalue la forme SUM et la forme MEDIAN sur A1:A4 et confronte le résultat à une attente dérivée à la main. Les codes 0, 1, 4 et 5 doivent propager le #DIV/0! de A3, puisqu'aucun d'eux n'ignore les erreurs. Le code 2 donne SUM 30 et MEDIAN 15, à partir de 10 et 20 avec l'imbriquée A4 sautée. Le code 3 donne 10 et 10. Le code 6 donne 60 et 20, parce que le 30 de A4 compte désormais. Le code 7 donne 40 et 20, qui est le cas qui renvoyait 20 avant le correctif de fuite. La campagne d'acceptation plus large consignée dans le registre des problèmes connus couvre les dix-neuf numéros de fonction contre les huit codes, avec chaque précédent en cache et non en cache, soit 304 scénarios sur Win32 et Win64

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';   // sous-total de groupe = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  masquées sautées, erreur propagée
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       masquées + erreurs + imbriqués sautés
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       seules les erreurs sont sautées
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       valait 20 avant la v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Où se situe encore la frontière

Trois limites méritent d'être connues avant de construire là-dessus. D'abord, le prédicat d'agrégat imbriqué est textuel. TXLSXWorkbook.GetCalcIsSubtotalCell et son jumeau du moteur classique répondent True quand la formule d'une cellule commence par SUBTOTAL(, AGGREGATE( ou _xlfn.AGGREGATE(, avec ou sans le signe égal initial, donc une formule comme =IF(C1,SUBTOTAL(9,B1:B9),0) ou =SUBTOTAL(9,B1:B9)*2 n'est pas reconnue comme imbriquée et sera doublement comptée par les codes 0 à 3 là où Excel la sauterait ; un générateur qui émet des sous-totaux calculés devrait garder l'appel d'agrégation en tête de formule. Ensuite, l'isolation vit dans les trois parcours AGGREGATE. CalcSubtotalFunc passe toujours par GetValueItemRange, CollectRangeValues et SubtotalReduceVariance, qui appellent FGetValue directement, donc un SUBTOTAL(109, ...) dont la plage contient une formule précédente non mise en cache peut encore transmettre son garde de lignes masquées à ce précédent. Un Recalculate complet évalue les précédents avant les dépendants, donc le chemin en cache est pris et le garde n'est jamais hérité ; l'exposition se limite à l'évaluation ad hoc via Calculate et aux classeurs chargés sans valeurs en cache, et si vous comptez sur le recalcul incrémental sur le graphe de dépendances pour garder les gros modèles réactifs, la même garantie d'ordre est ce qui garde cette fuite dormante. Enfin, les deux gardes sont conditionnés par Assigned(FIsRowHidden) et Assigned(FIsSubtotalCell). Les deux façades de classeur câblent les callbacks dans leur constructeur, mais du code qui construit un TXLSCalculator à la main avec seulement les deux arguments d'origine obtient en silence le comportement hérité qui inclut tout, quel que soit le code d'options. Quand un total semble faux et que le texte de la formule semble juste, tracer l'évaluation pas à pas est le moyen le plus rapide de voir si un précédent a été évalué sous un garde hérité ou si un callback n'a tout simplement jamais été attaché

Le moteur de calcul décrit ici, le décodeur d'options, les wrappers de récupération isolés et la matrice de régression qui les verrouille sont tous livrés sous forme de source avec le composant tableur HotXLS pour Delphi, qui lit, écrit et recalcule les classeurs XLS, XLSX et ODS en Delphi et C++Builder sans installation d'Excel