Article technique

LAMBDA et LET en Delphi : fermetures HotXLS

La cellule contient =NetOf(B2, C2), B2 vaut 1250, C2 vaut 0.19, Excel affiche 1012.5, et le classeur ne contient ni module VBA ni add-in. NetOf vit dans le gestionnaire de noms : c'est un nom défini dont le texte est LAMBDA(amount, rate, amount*(1-rate)). Un calculateur côté serveur qui traite chaque nom défini comme une plage ou une constante n'y trouve rien à appeler, et la cellule revient en #NAME?

Bien calculer cette cellule demande plus qu'un analyseur qui accepte le mot LAMBDA. Un évaluateur de tableur classique connaît cinq sortes de valeurs : nombres, texte, booléens, erreurs et références à des cellules qui en contiennent. LAMBDA ajoute une sixième, une valeur de fonction qui porte ses noms de paramètres, son corps et les noms visibles là où elle a été écrite. LET est l'autre moitié de l'histoire, parce que LET est l'endroit où les utilisateurs lient ces fonctions à de courts noms locaux et les appellent quelques jetons plus loin, souvent trois niveaux plus bas

Trois endroits où un nom de fonction peut se résoudre

Quand le moteur de formules HotXLS rencontre un appel à un nom qui n'est pas une fonction intégrée, il essaie trois sources dans un ordre fixe. La première est la portée LET ou LAMBDA courante : si f est une liaison locale détenant une fermeture, f(21) l'applique. La deuxième est un nom défini de classeur dont le texte commence par LAMBDA, si bien que NetOf(1250, 0.19) compile le corps du nom et l'applique aux deux arguments. La troisième est le gestionnaire de fonctions utilisateur que vous utilisez peut-être déjà pour vos fonctions Delphi personnalisées, décrit dans les fonctions personnalisées dans le moteur de formules HotXLS. Seulement quand les trois passent la cellule devient #NAME?

L'ordre compte dans une situation précise. Un nom local gagne toujours sur un nom de classeur de même orthographe, même quand la liaison locale contient un nombre ordinaire. Dans ce cas la recherche de nom défini est sautée entièrement et l'appel va directement au gestionnaire de fonctions utilisateur, et une LAMBDA de classeur du même nom n'est jamais consultée pour cet appel

Diagramme des trois chemins de résolution d'appel HotXLS en Delphi essayés dans l'ordre pour un nom de fonction inconnu comme f(21) : une liaison de portée LET ou LAMBDA locale détenant une fermeture, un nom défini de classeur dont le texte RefersTo commence par LAMBDA, et le gestionnaire classique de fonctions utilisateur, une liaison détenant un nombre n'étant jamais appliquée comme fonction et l'appel finissant en #NAME? quand aucun gestionnaire de fonction utilisateur ne revendique le nom
HotXLS essaie d'abord la portée locale, puis les noms définis LAMBDA, puis le gestionnaire classique de fonction utilisateur, et refuse d'appeler une liaison qui ne détient pas de fermeture

Le côté Delphi est court. Une fonction nommée n'est qu'un nom défini ajouté avec TXLSXDefinedNames.Add, et une fermeture à l'intérieur d'une formule n'a besoin d'aucun enregistrement :

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');

    // Add à deux arguments = portée classeur, ce que l'appel par nom consulte
    Book.DefinedNames.Add('NetOf', 'LAMBDA(amount, rate, amount*(1-rate))');

    // 1250 * (1 - 0.19) = 1012.5
    Sheet.Cells[2, 2].Formula := 'NetOf(1250, 0.19)';

    // Une fermeture liée et appliquée à l'intérieur d'une formule : 42
    Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';

    // bump capture base = 100 ; bump(5) = 105, step*2 = 210
    Sheet.Cells[4, 2].Formula :=
      'LET(base, 100, bump, LAMBDA(v, v+base), LET(step, bump(5), step*2))';

    // Application postfixe, aucun nom du tout : 42
    Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';

    Book.Recalculate;
    Book.SaveAs('lambda-model.xlsx');
  finally
    Book.Free;
  end;
end;

La dernière cellule montre une forme qu'on a tendance à oublier. Excel vous laisse appliquer une expression LAMBDA directement en écrivant la liste d'arguments juste après, et HotXLS analyse LAMBDA(x, x*2)(21) de la même façon. L'analyseur n'accepte cette liste d'arguments finale qu'après quelque chose qui peut produire une fonction, comme un nom, une expression LET ou IF, ou une autre application. Écrire (1+2)(3) échoue à la compilation plutôt qu'à l'évaluation

Ce dont une fermeture se souvient, et pourquoi les paramètres gagnent

Évaluer une expression LAMBDA dans HotXLS produit une valeur de fermeture : les noms de paramètres, une copie du corps, la feuille sur laquelle elle a été écrite, et un instantané de chaque liaison LET ou LAMBDA visible à cet instant. Dans la quatrième cellule ci-dessus, bump est créé alors que base est déjà liée à 100, donc la fermeture transporte base = 100 avec elle. Quand bump(5) s'exécute à l'intérieur du LET interne, le corps voit toujours ce 100, même si le LET interne ne connaît rien nommé base

Un appel se produit ensuite en trois étapes. Les expressions d'arguments sont évaluées d'abord, dans la portée de l'appelant. Puis l'environnement capturé est empilé, puis les paramètres au-dessus, et le corps est évalué. Les recherches vont du haut vers le bas, donc un paramètre nommé rate masque une liaison capturée nommée rate, et une liaison capturée masque tout ce que l'appelant aurait lié sous le même nom. Quand le corps retourne, ou échoue, la portée est recadrée là où l'appel a commencé

Diagramme d'un appel de fermeture LAMBDA HotXLS en Delphi : la valeur de fermeture porte son paramètre, son corps et la liaison de base capturée depuis le LET englobant, l'appel empile l'environnement capturé avant l'argument v = 5, le corps s'évalue à 105, et la pile de portées est tronquée jusqu'à sa marque d'entrée dans une garde finally
Appeler une fermeture pousse l'environnement capturé avant les liaisons d'arguments, et cet ordonnancement est précisément ce qui fait que les paramètres masquent les noms extérieurs

Évaluer les arguments avant d'installer la moindre liaison ressemble à un détail, jusqu'à ce que vous écriviez LET(x, 10, f, LAMBDA(x, x+1), f(x*2)). L'argument x*2 doit désigner le x extérieur, ce qui donne 20, et le résultat est 21. Si le paramètre était installé en premier, l'argument lirait le paramètre qu'il s'apprête à définir. La règle qui en découle est celle qu'attendent les utilisateurs d'Excel : ce que calcule une fonction nommée dépend de ses arguments et de ce qu'elle a capturé au moment où elle a été écrite, et les autres noms locaux de l'appelant ne fuient jamais dedans

Pourquoi f(21) échoue-t-il quand f contient un nombre ?

Une liaison locale qui contient autre chose qu'une fermeture n'est jamais appliquée comme fonction. LET(f, 3, f(21)) ne multiplie pas 3 par 21, et ne retombe pas non plus sur un nom de classeur appelé f. L'appel passe au gestionnaire de fonctions utilisateur, et si aucun gestionnaire ne revendique le nom, la cellule finit en #NAME?. Ce résultat est le bon pour un tableur : une faute de frappe qui transformerait silencieusement un appel de fonction en multiplication produirait un nombre plausible que personne ne remet en question

L'application postfixe est encore plus stricte. Ce qui se trouve devant la liste d'arguments est évalué, et si le résultat n'est pas une fermeture, la cellule reçoit #VALUE!, si bien que LET(g, 3, (g)(21)) et LET(g, 3, g(21)) échouent avec des erreurs différentes. Autres échecs que vous pouvez reproduire dans un classeur de brouillon :

  • Mauvais nombre d'arguments : LET(add, LAMBDA(a, b, a+b), add(1)) renvoie #VALUE! avant même que l'expression d'argument ne soit évaluée
  • Les erreurs à l'intérieur des arguments voyagent comme des valeurs : dans LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0)), le #DIV/0! est lié à x et IFERROR à l'intérieur du corps le transforme en 0
  • Les références restent des références : un argument comme A1 atteint le corps comme référence de cellule, si bien que les fonctions qui se soucient de l'identité de référence, ISREF au premier chef, voient une référence plutôt que la valeur de la cellule
  • Une définition LAMBDA accepte jusqu'à 253 paramètres, le même plafond qu'Excel documente pour LAMBDA
  • La récursion folle s'arrête : une lambda nommée qui s'appelle elle-même partage un plafond par défaut de 256 niveaux imbriqués avec l'imbrication ordinaire des formules, et au-delà l'appel se termine par une erreur de limite de ressources au lieu d'épuiser la pile Delphi
// Log est votre propre routine de journalisation, pas une API HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
  Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))';                       // #NAME? sans gestionnaire
  Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))';                     // #VALUE!, postfixe
  Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))';     // #VALUE!, arité
  Sheet.Cells[5, 4].Formula := 'LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0))'; // 0
  Book.Recalculate;
  Log(Sheet.Cells[2, 4].FormattedText);
  Log(Sheet.Cells[3, 4].FormattedText);
  Log(Sheet.Cells[4, 4].FormattedText);
  Log(Sheet.Cells[5, 4].FormattedText);
end;

Liaisons LET dans HotXLS, imbriquées et partielles

HotXLS évalue LET de gauche à droite : chaque liaison est évaluée avec toutes les liaisons antérieures en portée, empilée, et le dernier argument est évalué sous l'ensemble complet. Ainsi LET(a, 1, b, a+1, LET(c, b*2, c)) renvoie 4, et le LET interne voit à la fois a et b. Quand le LET externe se termine, ses liaisons disparaissent, ce qui signifie qu'une formule sœur dans la cellule suivante repart d'une portée vide

Deux règles de forme sont appliquées avant que quoi que ce soit ne s'exécute. LET exige un nombre impair d'arguments, au moins trois, donc LET(a, 1) est un #VALUE!. Et chaque emplacement de nom doit être un nom simple : LET(A1, 5, A1*2) est rejeté parce que A1 est une référence de cellule, tandis que des noms courts comme x ou ab, qui épellent aussi des lettres de colonne, sont lus comme des noms locaux à l'intérieur de LET et LAMBDA. Une liaison à une plage garde la plage, donc LET(r, A1:A10, ROWS(r)) renvoie 10 et SUM(r) additionne les cellules

Les mêmes valeurs de fermeture alimentent les fonctions d'aide construites pour elles : MAP, REDUCE, SCAN, MAKEARRAY, BYROW et BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) déborde cinq valeurs doublées, et REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) les replie en une seule somme. L'environnement capturé voyage dans ces appels exactement comme pour un appel direct, donc un taux lié par LET reste visible à l'intérieur du corps du MAP

Virgules, points-virgules et ce que SaveAs écrit

L'analyseur de formules HotXLS prend la virgule et le point-virgule comme séparateurs d'arguments de façon interchangeable. Le texte des formules arrive d'endroits que vous ne contrôlez pas : un ticket de support écrit sur une machine en locale allemande, une documentation qui utilise les virgules canoniques d'Excel, un script qui génère des chaînes de formules, du code HotXLS ancien qui utilisait des points-virgules partout. SUM(A1,A2) et SUM(A1;A2) compilent la même chose, et une LAMBDA écrite de l'une ou l'autre façon aussi

Les constantes de tableau en ligne sont l'exception. À l'intérieur d'accolades, le point-virgule garde son sens Excel de séparateur de lignes, donc {1,2;3,4} est un tableau deux par deux, pas une liste à quatre arguments. Si vous générez des formules avec des points-virgules comme séparateurs d'arguments, gardez les constantes de tableau dans la syntaxe Excel et les deux ne se percutent jamais

Cell.Formula renvoie le texte que vous avez affecté. Quand SaveAs écrit un fichier XLSX, les points-virgules utilisés comme séparateurs d'arguments hors accolades sont écrits en virgules, parce que le format de fichier OOXML stocke les formules dans la syntaxe à virgules d'Excel, et les fonctions qu'Excel stocke sous un préfixe _xlfn. reçoivent ce préfixe en sortie. Une formule chargée depuis un fichier et laissée intacte est réécrite depuis son texte XML d'origine

Lire des lambdas nommées depuis des classeurs clients

Les classeurs créés dans Excel stockent les noms LAMBDA comme Excel les écrit : avec un préfixe _xlfn. sur le nom de fonction et souvent _xlpm. sur les paramètres. HotXLS retire ces préfixes quand il évalue le nom, mais TXLSXDefinedName.Formula renvoie le texte de définition tel que le fichier l'a fourni. Une vérification écrite Copy(Name.Formula, 1, 6) = 'LAMBDA' passe pour les noms que vous avez créés en code et rate tous les noms venus d'Excel. Ce décalage est facile à manquer, parce que les valeurs calculées sont correctes dans les deux cas et que seul votre propre code d'audit est en désaccord

// IsLambdaDefinition est un helper que vous ajoutez vous-même, pas une API HotXLS
function IsLambdaDefinition(const Text: WideString): Boolean;
var
  S: WideString;
begin
  S := UpperCase(Trim(Text));
  if (S <> '') and (S[1] = '=') then
    Delete(S, 1, 1);
  if Copy(S, 1, 6) = '_XLFN.' then
    Delete(S, 1, 6);
  Result := Copy(S, 1, 7) = 'LAMBDA(';
end;

procedure AuditLambdas(const FileName: string);
var
  Book: TXLSXWorkbook;
  Name: TXLSXDefinedName;
  I: Integer;
begin
  // Log est votre propre routine de journalisation
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) = 1 then
    begin
      for I := 0 to Book.DefinedNames.Count - 1 do
      begin
        Name := Book.DefinedNames[I];
        if IsLambdaDefinition(Name.Formula) and (Name.SheetIndex >= 0) then
          Log('Sheet-scoped lambda, not callable by name: ' + Name.Name);
      end;
      Book.Recalculate;
      Log(VarToStr(Book.Sheets[1].Cells[2, 2].Value));
    end;
  finally
    Book.Free;
  end;
end;

La vérification de portée dans cette boucle est là parce que l'appel par nom dans HotXLS cherche le callee parmi les noms de niveau classeur. Gardez les fonctions nommées à portée classeur. Un nom LAMBDA à portée de feuille reste lisible via FindByName(AName, ASheetIndex), mais une fonction que plusieurs feuilles appellent appartient au classeur, et les règles de portée derrière cela sont posées dans les noms définis et les formules inter-feuilles

Éditer une lambda plus tard coûte peu. Affecter un nouveau texte à TXLSXDefinedName.Formula invalide le graphe de dépendances, si bien que le prochain Recalculate le reconstruit et réévalue les cellules qui appellent la fonction ; la mécanique est dans le recalcul incrémental et le graphe de dépendances. Une cellule dont la formule s'évalue en une fermeture nue, comme =LAMBDA(x, x*2) sans appel, ne reçoit aucune valeur en cache, puisqu'il n'y a ni nombre ni texte à stocker ; Excel affiche #CALC! pour la même cellule. Pour les rapports qui doivent s'ouvrir dans des outils sans support LAMBDA, figez les résultats avec Sheet.Cells.ConvertFormulasToValues après le dernier Recalculate, ce qui garde chaque valeur en cache et abandonne le texte de formule

LAMBDA, LET, les fonctions d'aide et l'API des noms définis partent tous dans la même bibliothèque native Delphi et C++Builder, qui lit et écrit XLS, XLSX et ODS sans Excel installé. La liste complète des fonctions de formules est sur la page du composant tableur HotXLS