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
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é
É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é àxet IFERROR à l'intérieur du corps le transforme en 0 - Les références restent des références : un argument comme
A1atteint 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