Technischer Artikel

LAMBDA & LET in Delphi: HotXLS-Formel-Closures

Die Zelle liest =NetOf(B2, C2), B2 hält 1250, C2 hält 0,19, Excel zeigt 1012,5, und die Arbeitsmappe enthält kein VBA-Modul und kein Add-in. NetOf wohnt im Name Manager: Es ist ein definierter Name, dessen Text LAMBDA(amount, rate, amount*(1-rate)) lautet. Ein serverseitiger Rechner, der jeden definierten Namen als Bereich oder Konstante behandelt, findet dort nichts Aufrufbares, und die Zelle kommt als #NAME? zurück

Diese Zelle richtig zu berechnen verlangt mehr als einen Parser, der das Wort LAMBDA akzeptiert. Ein klassischer Tabellenkalkulations-Evaluator kennt fünf Arten von Wert: Zahlen, Text, Booleans, Fehler und Verweise auf Zellen, die solche Werte halten. LAMBDA fügt einen sechsten hinzu, einen Funktionswert, der seine Parameternamen, seinen Rumpf und die Namen trägt, die an der Schreibstelle sichtbar waren. LET ist die andere Hälfte der Geschichte, denn LET ist der Ort, an dem Benutzer diese Funktionen an kurze lokale Namen binden und sie wenige Tokens später aufrufen, oft drei Ebenen tief

Drei Stellen, an denen ein Funktionsname auflösen kann

Trifft die HotXLS-Formel-Engine auf einen Aufruf eines Namens, der keine eingebaute Funktion ist, probiert sie drei Quellen in fester Reihenfolge. Die erste ist der aktuelle LET- oder LAMBDA-Scope: Ist f eine lokale Bindung, die eine Closure hält, wendet f(21) sie an. Die zweite ist ein definierter Name im Arbeitsbuch, dessen Text mit LAMBDA beginnt, also kompiliert NetOf(1250, 0.19) den Rumpf des Namens und wendet ihn auf die zwei Argumente an. Die dritte ist der Handler für nutzerdefinierte Funktionen, den Sie vielleicht schon für eigene Delphi-Funktionen nutzen, beschrieben in eigene Funktionen in der HotXLS-Formel-Engine. Erst wenn alle drei vergeblich waren, wird die Zelle zu #NAME?

Die Reihenfolge ist in einer bestimmten Situation wichtig. Ein lokaler Name gewinnt immer gegen einen Arbeitsbuch-Namen mit derselben Schreibweise, auch wenn die lokale Bindung eine gewöhnliche Zahl hält. In dem Fall wird die Suche nach definierten Namen komplett übersprungen, der Aufruf geht direkt an den Handler für nutzerdefinierte Funktionen, und ein Arbeitsbuch-LAMBDA desselben Namens wird für diesen Aufruf nie konsultiert

Diagramm der drei HotXLS-Aufrufauflösungspfade in Delphi, der Reihe nach probiert für einen unbekannten Funktionsnamen wie f(21): eine lokale LET- oder LAMBDA-Scope-Bindung mit einer Closure, ein definierter Name im Arbeitsbuch, dessen RefersTo-Text mit LAMBDA beginnt, und der klassische Handler für nutzerdefinierte Funktionen; eine lokale Bindung mit einer Zahl wird nie als Funktion aufgerufen, und der Aufruf endet an #NAME?, wenn kein Handler den Namen beansprucht
HotXLS probiert zuerst den lokalen Scope, dann LAMBDA-definierte Namen, dann den klassischen Handler für nutzerdefinierte Funktionen, und verweigert den Aufruf einer Bindung, die keine Closure hält

Die Delphi-Seite ist kurz. Eine benannte Funktion ist schlicht ein definierter Name, hinzugefügt mit TXLSXDefinedNames.Add, und eine Closure innerhalb einer einzigen Formel braucht gar keine Registrierung:

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

    // Add mit zwei Argumenten = Arbeitsbuch-Scope, dort sucht call-by-name
    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)';

    // Eine Closure, in einer Formel gebunden und angewendet: 42
    Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';

    // bump erfasst 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))';

    // Postfix-Anwendung, ganz ohne Name: 42
    Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';

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

Die letzte Zelle zeigt eine Form, die gern vergessen wird. Excel erlaubt es, einen LAMBDA-Ausdruck direkt anzuwenden, indem man die Argumentliste unmittelbar dahinter schreibt, und HotXLS parst LAMBDA(x, x*2)(21) auf dieselbe Weise. Der Parser akzeptiert diese nachgestellte Argumentliste nur nach etwas, das eine Funktion erzeugen könnte, etwa einem Namen, einem LET- oder IF-Ausdruck oder einer weiteren Anwendung. (1+2)(3) zu schreiben scheitert also schon beim Kompilieren, nicht erst bei der Auswertung

Was eine Closure behält, und warum Parameter gewinnen

Die Auswertung eines LAMBDA-Ausdrucks in HotXLS produziert einen Closure-Wert: die Parameternamen, eine Kopie des Rumpfs, das Blatt, auf dem er geschrieben wurde, und einen Snapshot jeder LET- oder LAMBDA-Bindung, die in diesem Moment sichtbar war. In der vierten Zelle oben wird bump erzeugt, während base bereits an 100 gebunden ist, also trägt die Closure base = 100 mit sich. Läuft bump(5) innerhalb des inneren LET, sieht der Rumpf weiterhin diese 100, obwohl das innere LET nichts von base dem Namen nach weiß

Ein Aufruf passiert dann in drei Schritten. Die Argumentausdrücke werden zuerst ausgewertet, im Scope des Aufrufers. Dann wird die erfasste Umgebung geschoben, darauf die Parameter, und der Rumpf wird ausgewertet. Lookups suchen von oben nach unten, also überschattet ein Parameter namens rate eine erfasste Bindung namens rate, und eine erfasste Bindung überschattet alles, was der Aufrufer zufällig unter demselben Namen gebunden hat. Kehrt der Rumpf zurück oder scheitert, wird der Scope auf den Punkt des Aufrufbeginns zurückgeschnitten

Diagramm eines HotXLS-LAMBDA-Closure-Aufrufs in Delphi: Der Closure-Wert trägt seinen Parameter, seinen Rumpf und die aus dem umgebenden LET erfasste base-Bindung, der Aufruf schiebt die erfasste Umgebung vor das Argument v = 5, der Rumpf ergibt 105, und der Scope-Stack schneidet in einem finally-Guard zurück auf seine Eintrittsmarke zurück
Ein Closure-Aufruf schiebt die erfasste Umgebung vor die Argumentbindungen, und genau diese Reihenfolge lässt Parameter äußere Namen überschatten

Die Argumente auszuwerten, bevor irgendeine Bindung installiert ist, klingt nach einem Detail, bis man LET(x, 10, f, LAMBDA(x, x+1), f(x*2)) schreibt. Das Argument x*2 muss das äußere x meinen, was 20 ergibt, und das Ergebnis ist 21. Würde man den Parameter zuerst installieren, läse das Argument den Parameter, den es gerade selbst definieren will. Die Regel, die dabei herausfällt, ist die, die Excel-Benutzer erwarten: Was eine benannte Funktion berechnet, hängt von ihren Argumenten und dem ab, was sie beim Schreiben erfasst hat, und die anderen lokalen Namen des Aufrufers sickern nie hinein

Warum scheitert f(21), wenn f eine Zahl hält?

Eine lokale Bindung, die etwas anderes als eine Closure hält, wird nie als Funktion angewendet. LET(f, 3, f(21)) multipliziert nicht 3 mit 21, und es fällt auch nicht auf einen Arbeitsbuch-Namen namens f zurück. Der Aufruf wandert zum Handler für nutzerdefinierte Funktionen weiter, und wenn kein Handler den Namen beansprucht, endet die Zelle als #NAME?. Dieses Ergebnis ist das richtige für eine Tabellenkalkulation: Ein Tippfehler, der still einen Funktionsaufruf in eine Multiplikation verwandelt, würde eine plausible Zahl produzieren, die niemand anzweifelt

Postfix-Anwendung ist noch strenger. Was vor der Argumentliste sitzt, wird ausgewertet, und ist das Ergebnis keine Closure, bekommt die Zelle #VALUE!, also scheitern LET(g, 3, (g)(21)) und LET(g, 3, g(21)) mit unterschiedlichen Fehlern. Weitere Fälle, die Sie in einem Wegwerf-Arbeitsbuch reproduzieren können:

  • Falsche Argumentzahl: LET(add, LAMBDA(a, b, a+b), add(1)) gibt #VALUE! zurück, bevor der Argumentausdruck überhaupt ausgewertet wird
  • Fehler in Argumenten reisen als Werte: In LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0)) wird das #DIV/0! an x gebunden, und das IFERROR im Rumpf verwandelt es in 0
  • Referenzen bleiben Referenzen: Ein Argument wie A1 erreicht den Rumpf als Zellreferenz, also sehen Funktionen, denen Referenzidentität wichtig ist, ISREF eingeschlossen, eine Referenz statt des Zellwerts
  • Eine LAMBDA-Definition akzeptiert bis zu 253 Parameter, dieselbe Obergrenze, die Excel für LAMBDA dokumentiert
  • Ausufernde Rekursion stoppt: Ein benanntes Lambda, das sich selbst aufruft, teilt sich eine Vorgabe-Obergrenze von 256 verschachtelten Ebenen mit der gewöhnlichen Formelverschachtelung, und darüber hinaus endet der Aufruf mit einem Ressourcengrenzen-Fehler, statt den Delphi-Stack aufzubrauchen
// Log ist die eigene Logging-Routine, kein Teil von HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
  Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))';                       // #NAME? ohne Handler
  Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))';                     // #VALUE!, Postfix
  Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))';     // #VALUE!, Arität
  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;

LET-Bindungen in HotXLS, verschachtelt und partiell

HotXLS wertet LET von links nach rechts aus: Jede Bindung wird mit allen früheren Bindungen im Scope ausgewertet, geschoben, und das letzte Argument wird unter dem vollständigen Satz ausgewertet. LET(a, 1, b, a+1, LET(c, b*2, c)) gibt also 4 zurück, und das innere LET sieht sowohl a als auch b. Ist das äußere LET fertig, sind seine Bindungen weg, was bedeutet, dass eine Nachbarformel in der nächsten Zelle aus einem leeren Scope startet

Zwei Formregeln werden durchgesetzt, bevor irgendwas läuft. LET braucht eine ungerade Anzahl von Argumenten, mindestens drei, also ist LET(a, 1) ein #VALUE!. Und jeder Namensslot muss ein schlichter Name sein: LET(A1, 5, A1*2) wird abgelehnt, weil A1 eine Zellreferenz ist, während kurze Namen wie x oder ab, die auch Spaltenbuchstaben schreiben, innerhalb von LET und LAMBDA als lokale Namen gelesen werden. Eine Bindung an einen Bereich behält den Bereich, also gibt LET(r, A1:A10, ROWS(r)) 10 zurück, und SUM(r) addiert die Zellen

Dieselben Closure-Werte speisen die Helferfunktionen, die dafür gebaut wurden: MAP, REDUCE, SCAN, MAKEARRAY, BYROW und BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) verschüttet fünf verdoppelte Werte, und REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) faltet sie zu einer Summe zusammen. Die erfasste Umgebung reist in diese Aufrufe genauso mit wie bei einem direkten Aufruf, also ist eine per LET gebundene Rate weiterhin im MAP-Rumpf sichtbar

Komma, Semikolon und was SaveAs schreibt

Der HotXLS-Formel-Parser nimmt das Komma und das Semikolon als Argumenttrenner austauschbar. Formeltext kommt aus Quellen, die Sie nicht kontrollieren: ein Support-Ticket von einer Maschine mit deutschem Locale, Dokumentation, die die kanonischen Excel-Kommas verwendet, ein Skript, das Formelstrings generiert, älterer HotXLS-Code, der durchweg Semikolons verwendete. SUM(A1,A2) und SUM(A1;A2) kompilieren zu demselben Ding, und ein LAMBDA, das in beiden Schreibweisen geschrieben ist, ebenfalls

Inline-Array-Konstanten sind die Ausnahme. Innerhalb von geschweiften Klammern behält das Semikolon seine Excel-Bedeutung als Zeilentrenner, also ist {1,2;3,4} ein Zwei-mal-zwei-Array, keine Liste mit vier Argumenten. Generieren Sie Formeln mit Semikolons als Argumenttrennern, halten Sie Array-Konstanten in der Excel-Syntax, und die beiden kollidieren nie

Cell.Formula gibt den Text zurück, den Sie zugewiesen haben. Schreibt SaveAs eine XLSX-Datei, werden Semikolons als Argumenttrenner außerhalb von Klammern als Kommas geschrieben, denn das OOXML-Dateiformat speichert Formeln in der Komma-Syntax von Excel, und Funktionen, die Excel unter einem _xlfn.-Präfix ablegt, bekommen dieses Präfix auf dem Weg hinaus. Eine Formel, die aus einer Datei geladen und nicht angefasst wurde, wird aus ihrem originalen XML-Text zurückgeschrieben

Benannte Lambdas aus Kunden-Arbeitsmappen lesen

In Excel erstellte Arbeitsmappen speichern LAMBDA-Namen so, wie Excel sie schreibt: mit einem _xlfn.-Präfix auf dem Funktionsnamen und oft _xlpm. auf den Parametern. HotXLS streift diese Präfixe ab, wenn es den Namen auswertet, aber TXLSXDefinedName.Formula liefert den Definitionstext so, wie die Datei ihn geliefert hat. Eine Prüfung der Form Copy(Name.Formula, 1, 6) = 'LAMBDA' besteht für Namen, die Sie im Code erzeugt haben, und verfehlt jeden Namen, der aus Excel kam. Diese Diskrepanz ist leicht zu übersehen, denn die berechneten Werte stimmen ohnehin in beiden Fällen, und nur der eigene Audit-Code meckert

// IsLambdaDefinition ist ein selbst geschriebener Helfer, keine HotXLS-API
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 ist die eigene Logging-Routine
  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;

Die Scope-Prüfung in dieser Schleife existiert, weil call-by-name in HotXLS den Aufgerufenen unter den Arbeitsbuch-Namen sucht. Halten Sie benannte Funktionen im Arbeitsbuch-Scope. Einen blattbezogenen LAMBDA-Namen können Sie weiterhin über FindByName(AName, ASheetIndex) lesen, aber eine Funktion, die aus mehreren Blättern aufgerufen wird, gehört ins Arbeitsbuch, und die Scoping-Regeln dahinter legen definierte Namen und blattübergreifende Formeln dar

Ein Lambda später zu bearbeiten ist billig. Einen neuen Text TXLSXDefinedName.Formula zuzuweisen, invalidiert den Abhängigkeitsgraph, also baut das nächste Recalculate ihn wieder auf und wertet die Zellen neu aus, die die Funktion aufrufen; die Mechanik steht in inkrementelle Neuberechnung und der Abhängigkeitsgraph. Eine Zelle, deren Formel zu einer nackten Closure auswertet, etwa =LAMBDA(x, x*2) ohne Aufruf, bekommt gar keinen gecachten Wert, denn es gibt keine Zahl und keinen Text zu speichern; Excel zeigt für dieselbe Zelle #CALC!. Für Berichte, die in Werkzeugen ohne LAMBDA-Unterstützung geöffnet werden müssen, frieren Sie die Ergebnisse mit Sheet.Cells.ConvertFormulasToValues nach dem letzten Recalculate ein, was jeden gecachten Wert behält und den Formeltext verwirft

LAMBDA, LET, die Helferfunktionen und die Defined-Name-API sind alle Teil derselben nativen Delphi- und C++Builder-Bibliothek, die XLS, XLSX und ODS liest und schreibt, ohne dass Excel installiert ist. Die vollständige Formelfunktionsliste steht auf der HotXLS-Spreadsheet-Komponentenseite