Pour calculer des références circulaires intentionnelles dans Delphi, HotXLS propose un calcul itératif sur son moteur XLSX : définissez TXLSXWorkbook.Iterate sur True et TXLSXWorkbook.Recalculate parcourt chaque cycle de référence détecté vers un point fixe — jusqu'à IterateCount passages, ou jusqu'à ce que chaque cellule varie de moins de IterateDelta — plutôt que de renvoyer #REF! et d'abandonner
Cette distinction est plus importante que ne le laisse supposer le simple booléen. Le même moteur qui détecte un cycle de référence et refuse de boucler dessus va, avec une seule propriété modifiée, évaluer ce cycle délibérément jusqu'à ce que il se stabilise. Distinguer clairement ces deux comportements — quand un cycle est un défaut à signaler et quand il s'agit d'un modèle à résoudre — constitue l'objet même de cet article
Pourquoi une référence circulaire génère-t-elle une erreur par défaut ?
Par défaut, HotXLS traite tout cycle de référence comme une erreur de saisie et le signale au lieu de le calculer. TXLSXWorkbook.Recalculate construit un graphe de dépendance des formules, évalue chaque cellule de formule dans l'ordre topologique, et renvoie lxErrorRef dès qu'il détecte un cycle — les nœuds qui ne peuvent jamais être libérés lors du tri topologique. Ces éléments du cycle conservent leurs valeurs de cache antérieures ; toutes les formules extérieures au cycle s'évaluent normalement. Le fonctionnement de ce graphe, et les raisons pour lesquelles les membres du cycle sont ignorés plutôt que de boucler, sont traités dans l'article connexe sur le recalcul incrémentiel des formules et le graphe de dépendance
Le choix par défaut est le plus sûr car la plupart des cycles sont des bugs : une ligne de synthèse incluse par inadvertance dans sa propre plage de SUM, un copier-coller qui a déplacé une référence sur elle-même. Un code d'erreur explicite lors du recalcul est précisément ce que vous souhaitez dans ces cas. Cependant, une catégorie spécifique et importante de modèles est volontairement circulaire. Les tableaux d'intérêts sur intérêts, les répartitions de coûts ou de frais généraux circulaires entre départements et les calculs de commissions basés sur le solde décrivent tous une valeur qui revient légitimement alimenter ses propres entrées — et Excel ne les calcule qu'une fois que l'utilisateur coche Fichier → Options → Formules → Activer le calcul itératif
Comment activer le calcul itératif dans HotXLS ?
HotXLS reproduit cette option d'Excel avec trois propriétés sur TXLSXWorkbook : Iterate active le mode, et lorsqu'il est défini sur True, un cycle détecté est transmis à un solveur itératif au lieu de produire lxErrorRef. Considérons la structure classique de calcul d'intérêt sur intérêt, où le solde de clôture dépend des intérêts et les intérêts dépendent du solde
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: opening principal
Sheet.Cells[2, 2].Value := 0.05; // B2: period rate
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: balance = principal + interest
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: interest = balance * rate
Book.Iterate := True; // opt in to iterative calculation
if Book.Recalculate = lxOk then
// B3 converges to 1052.63..., B4 to 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 fait référence à B4 et B4 fait référence à B3, de sorte que le graphe de dépendance signale un cycle à deux nœuds. Si Iterate conserve sa valeur par défaut False, cette paire renvoie l'erreur lxErrorRef et aucune des deux cellules ne se stabilise. Si elle est définie sur True, Recalculate initialise le cycle à partir des valeurs de cache actuelles et réévalue ses membres passage après passage, en réinjectant les sorties de chaque passage comme entrées du passage suivant, jusqu'à ce que les chiffres ne varient plus. Ici, la formule fermée est principal / (1 - rate), de sorte que le solde se stabilise à 1052,63 et l'intérêt à 52,63 — les mêmes valeurs que celles produites par Excel lorsque l'itération est activée
Qu'est-ce qui arrête l'itération ?
Deux conditions d'arrêt indépendantes encadrent le solveur, et la compréhension des deux évite qu'un modèle ne génère une erreur ou ne boucle indéfiniment. IterateCount représente la limite stricte du nombre de réévaluations des membres du cycle ; sa valeur par défaut est 100, comme dans Excel. IterateDelta est le seuil de convergence : après chaque passage, le solveur mesure la plus grande variation numérique sur toutes les cellules du cycle, et dès que cette variation maximale descend en dessous de IterateDelta — par défaut 0,001 — la boucle de passage s'interrompt prématurément. La première condition satisfaite met fin à l'itération
Une conséquence mérite d'être énoncée clairement, car elle constitue la limite même de la fonctionnalité. Lorsque la limite est atteinte sans que la variation ne descende en dessous de IterateDelta, SolveCycleIteratively ne lève pas d'erreur — il renvoie lxOk et laisse les cellules contenir les valeurs de leur dernière itération, tout comme Excel écrit les derniers chiffres calculés lorsque sa propre limite d'itération est atteinte sans convergence. Ainsi, un code de retour de réussite de Recalculate en mode itératif signifie "le solveur a fonctionné", et non "le solveur a convergé". Un modèle dont la boucle de rétroaction diverge ou oscille consommera silencieusement tous les passages IterateCount et renverra des chiffres qui ne correspondent absolument pas à un point fixe, sans qu'aucune exception ne signale la différence
Comment le paramètre est-il enregistré dans les fichiers XLSX et XLS ?
Les paramètres de calcul itératif persistent dans les deux formats de feuille de calcul, de sorte qu'un classeur ouvert dans Excel se comporte de la manière configurée par votre code Delphi. Du côté XLSX, l'enregistreur n'émet l'élément OOXML <calcPr> que si Iterate est à True, et il omet tout attribut qui conserve sa valeur par défaut afin de limiter la taille de la sortie : un classeur utilisant les valeurs par défaut écrit simplement <calcPr iterate="1"/>, tandis que iterateCount n'apparaît que s'il diffère de 100 et iterateDelta uniquement s'il diffère de 0,001. À l'ouverture, TXLSXWorkbook relit les trois mêmes attributs, garantissant la symétrie de l'aller-retour
L'ancien moteur BIFF8 (.xls), TXLSWorkbook, gère l'état équivalent via trois enregistrements distincts sous un trio de propriétés différent. EnableIteration correspond à l'enregistrement CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations à l'enregistrement CalcCount ($000C, [MS-XLS] §2.4.31) et MaxIterationChange à l'enregistrement CalcDelta ($0010, [MS-XLS] §2.4.32). Les modificateurs (setters) appliquent les plages de la spécification — CalcCount devant se situer dans la plage 1..32767, MaxIterations est verrouillé, et un MaxIterationChange négatif revient à la valeur par défaut de 0,001. Définissez-les sur un classeur .xls chargé et les trois enregistrements de calcul seront fidèlement écrits lors de l'enregistrement
var
Book: TXLSWorkbook; // BIFF8 (.xls) engine
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // CalcIter record $0011
Book.MaxIterations := 500; // CalcCount record $000C (clamped to 1..32767)
Book.MaxIterationChange := 0.0001; // CalcDelta record $0010
Book.SaveAs('model.xls'); // the three calc records round-trip
finally
Book.Free;
end;
end;Notez la distinction de nomenclature délibérée : le moteur XLSX utilise Iterate / IterateCount / IterateDelta (le vocabulaire OOXML), tandis que le moteur BIFF8 emploie EnableIteration / MaxIterations / MaxIterationChange (aligné sur les noms d'enregistrements du document [MS-XLS]). Les deux trios décrivent les trois mêmes réglages — un commutateur d'activation, une limite d'itérations et un écart de convergence — avec les mêmes valeurs par défaut : désactivé, 100 et 0,001
Quand une référence circulaire est-elle un bug plutôt qu'un modèle ?
Activer l'itération n'est pas un moyen de faire disparaître les avertissements de référence circulaire, et l'utiliser ainsi constitue un piège. Activer Iterate de manière globale convertit chaque cycle accidentel — ceux que le code d'erreur par défaut visait à intercepter — en un nombre silencieusement convergé ou non. La règle est inverse : conservez Iterate à False comme mode standard pour que les véritables erreurs de conception apparaissent sous la forme lxErrorRef, et n'activez l'itération que sur les classeurs dont la circularité est volontaire et comprise
Lorsqu'un cycle apparaît et que vous hésitez sur sa nature, le traceur d'évaluation de formule est l'outil qui permet de les distinguer : tracez la formule suspecte et la chaîne de référence qui se replie sur elle-même devient visible étape par étape, afin de déterminer si elle code une véritable boucle de rétroaction ou une simple autoréférence accidentelle. Rappelez-vous également qu'une cellule du cycle peut appeler n'importe quelle fonction intégrée au cours de la boucle — le même calculateur qui résout une formule d'ingénierie ou de nombre complexe évaluant les membres du cycle à chaque passage — de sorte qu'un modèle divergent provient souvent d'un problème de formule au sein de la boucle, et non d'un problème de paramètres d'itération
La liste de contrôle pratique est courte. Confirmez que la boucle possède un véritable point fixe avant de vous fier à l'itération ; maintenez IterateDelta suffisamment serré pour que la "convergence" corresponde à ce dont votre modèle a besoin ; et après un recalcul (Recalculate) attendu pour converger, vérifiez une sortie connue plutôt que de vous fier au seul code lxOk, car ce code ne permet pas de distinguer la convergence d'une limite d'itérations atteinte
Le calcul des références circulaires itératives fait partie du moteur XLSX du composant HotXLS Delphi Excel, aux côtés du recalcul incrémentiel par graphe de dépendance sur lequel il s'appuie, et de la persistance OOXML et BIFF8 qui transfère le paramètre dans chaque fichier écrit. Pour les modèles financiers et d'ingénierie volontairement circulaires, c'est la différence entre un code d'erreur et une réponse