Artigo Técnico

LAMBDA e LET no Delphi: closures de fórmulas no HotXLS

A célula tem =NetOf(B2, C2), B2 guarda 1250, C2 guarda 0.19, o Excel mostra 1012.5, e a pasta de trabalho não contém módulo VBA nem add-in. A NetOf vive no Name Manager: é um nome definido cujo texto é LAMBDA(amount, rate, amount*(1-rate)). Uma calculadora server-side que trata todo nome definido como intervalo ou constante não encontra nada que possa chamar ali, e a célula volta como #NAME?

Acertar essa célula exige mais que um parser que aceita a palavra LAMBDA. Um avaliador de planilhas clássico conhece cinco tipos de valor: números, texto, booleanos, erros e referências a células que os guardam. O LAMBDA adiciona um sexto, um valor função que carrega os nomes dos parâmetros, o corpo e os nomes visíveis onde foi escrito. O LET é a outra metade da história, porque o LET é onde usuários vinculam essas funções a nomes locais curtos e as chamam alguns tokens depois, muitas vezes três níveis fundo

Três lugares onde um nome de função pode resolver

Quando o motor de fórmulas do HotXLS encontra uma chamada a um nome que não é função embutida, ele tenta três fontes em ordem fixa. A primeira é o escopo atual de LET ou LAMBDA: se f é uma vinculação local segurando uma closure, f(21) a aplica. A segunda é um nome definido da pasta de trabalho cujo texto começa com LAMBDA, então NetOf(1250, 0.19) compila o corpo do nome e o aplica aos dois argumentos. A terceira é o handler de funções do usuário que você talvez já use para funções Delphi personalizadas, descrito em funções personalizadas no motor de fórmulas do HotXLS. Só quando as três passam é que a célula vira #NAME?

A ordem importa em uma situação específica. Um nome local sempre ganha de um nome da pasta de trabalho com a mesma grafia, mesmo quando a vinculação local guarda um número comum. Nesse caso a busca por nome definido é pulada por completo e a chamada vai direto ao handler de funções do usuário, e um LAMBDA da pasta de trabalho com o mesmo nome nunca é consultado para aquela chamada

Diagrama dos três caminhos de resolução de chamada do HotXLS no Delphi, tentados em ordem para um nome de função desconhecido como f(21): uma vinculação de escopo local de LET ou LAMBDA segurando uma closure, um nome definido da pasta de trabalho cujo texto RefersTo começa com LAMBDA, e o handler clássico de funções do usuário, enquanto uma vinculação local que segura um número nunca é aplicada como função e a chamada termina em #NAME? quando nenhum handler de função do usuário reivindica o nome
O HotXLS tenta primeiro o escopo local, depois nomes definidos LAMBDA, depois o handler clássico de funções do usuário, e se recusa a chamar uma vinculação que não segura uma closure

O lado Delphi é curto. Uma função nomeada é só um nome definido adicionado com TXLSXDefinedNames.Add, e uma closure dentro de uma única fórmula não precisa de registro algum:

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

    // Add com dois argumentos = escopo da pasta de trabalho, que é o que a chamada por nome consulta
    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)';

    // Uma closure vinculada e aplicada dentro de uma fórmula: 42
    Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';

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

    // Aplicação posfixa, sem nome algum: 42
    Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';

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

A última célula mostra uma forma que a gente costuma esquecer. O Excel permite aplicar uma expressão LAMBDA diretamente escrevendo a lista de argumentos logo depois dela, e o HotXLS interpreta LAMBDA(x, x*2)(21) do mesmo jeito. O parser aceita essa lista de argumentos à direita só depois de algo que possa produzir uma função, como um nome, uma expressão LET ou IF, ou outra aplicação. Escrever (1+2)(3) falha em tempo de compilação em vez de em tempo de avaliação

O que uma closure lembra, e por que parâmetros vencem

Avaliar uma expressão LAMBDA no HotXLS produz um valor closure: os nomes dos parâmetros, uma cópia do corpo, a planilha em que foi escrito e um snapshot de toda vinculação LET ou LAMBDA visível naquele momento. Na quarta célula acima, o bump é criado enquanto base já está vinculada a 100, então a closure carrega base = 100 consigo. Quando o bump(5) roda dentro do LET interno, o corpo ainda vê esse 100, mesmo que o LET interno não saiba nada sobre base pelo nome

A chamada então acontece em três passos. As expressões dos argumentos são avaliadas primeiro, no escopo do chamador. Depois o ambiente capturado é empurrado, então os parâmetros por cima dele, e o corpo é avaliado. As buscas vão de cima para baixo, então um parâmetro chamado rate esconde uma vinculação capturada chamada rate, e uma vinculação capturada esconde qualquer coisa que o chamador tenha vinculado por acaso sob o mesmo nome. Quando o corpo retorna, ou falha, o escopo é cortado de volta até onde a chamada começou

Diagrama de uma chamada de closure LAMBDA do HotXLS no Delphi: o valor da closure carrega o parâmetro, o corpo e a vinculação base capturada do LET ao redor, a chamada empurra o ambiente capturado antes do argumento v = 5, o corpo avalia para 105, e a pilha de escopos é truncada de volta até a marca de entrada dentro de um guard finally
Chamar uma closure empurra o ambiente capturado antes das vinculações de argumentos, e essa ordem é exatamente o que faz parâmetros eclipsarem nomes externos

Avaliar os argumentos antes de qualquer vinculação ser instalada parece um detalhe até você escrever LET(x, 10, f, LAMBDA(x, x+1), f(x*2)). O argumento x*2 precisa significar o x externo, o que dá 20, e o resultado é 21. Se o parâmetro fosse instalado primeiro, o argumento leria o parâmetro que está prestes a definir. A regra que cai daí é a que usuários do Excel esperam: o que uma função nomeada calcula depende dos argumentos dela e do que ela capturou quando foi escrita, e os outros nomes locais do chamador nunca vazam para dentro

Por que f(21) falha quando f guarda um número?

Uma vinculação local que guarda algo que não é uma closure nunca é aplicada como função. LET(f, 3, f(21)) não multiplica 3 por 21, e também não cai para um nome da pasta de trabalho chamado f. A chamada segue para o handler de funções do usuário, e se nenhum handler reivindica o nome, a célula termina como #NAME?. Esse desfecho é o certo para uma planilha: um typo que transformasse silenciosamente uma chamada de função em uma multiplicação produziria um número plausível que ninguém questiona

A aplicação posfixa é ainda mais estrita. O que estiver na frente da lista de argumentos é avaliado, e se o resultado não é uma closure, a célula recebe #VALUE!, então LET(g, 3, (g)(21)) e LET(g, 3, g(21)) falham com erros diferentes. Outras falhas que você pode reproduzir em uma pasta de trabalho de rascunho:

  • Contagem de argumentos errada: LET(add, LAMBDA(a, b, a+b), add(1)) retorna #VALUE! antes que a expressão do argumento seja avaliada
  • Erros dentro de argumentos viajam como valores: em LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0)) o #DIV/0! é vinculado ao x e o IFERROR dentro do corpo o transforma em 0
  • Referências continuam referências: um argumento como A1 chega ao corpo como referência de célula, então funções que se importam com identidade de referência, ISREF entre elas, veem uma referência em vez do valor da célula
  • Uma definição LAMBDA aceita até 253 parâmetros, o mesmo teto que o Excel documenta para LAMBDA
  • Recursão descontrolada para: uma lambda nomeada que chama a si mesma compartilha um teto padrão de 256 níveis aninhados com o aninhamento comum de fórmulas, e além disso a chamada termina com um erro de limite de recursos em vez de estourar a pilha do Delphi
// Log é a sua própria rotina de log, não faz parte do HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
  Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))';                       // #NAME? sem um handler
  Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))';                     // #VALUE!, posfixa
  Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))';     // #VALUE!, aridade
  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;

Vinculações LET no HotXLS, aninhadas e parciais

O HotXLS avalia o LET da esquerda para a direita: cada vinculação é avaliada com todas as anteriores em escopo, empurrada, e o último argumento é avaliado sob o conjunto completo. Então LET(a, 1, b, a+1, LET(c, b*2, c)) retorna 4, e o LET interno vê tanto a quanto b. Quando o LET externo termina, as vinculações dele se vão, o que significa que uma fórmula irmã na célula seguinte começa de um escopo vazio

Duas regras de forma são impostas antes de qualquer coisa rodar. O LET precisa de um número ímpar de argumentos, pelo menos três, então LET(a, 1) é #VALUE!. E todo slot de nome precisa ser um nome simples: LET(A1, 5, A1*2) é rejeitado porque A1 é referência de célula, enquanto nomes curtos como x ou ab, que também soletram letras de coluna, são lidos como nomes locais dentro de LET e LAMBDA. Uma vinculação a um intervalo mantém o intervalo, então LET(r, A1:A10, ROWS(r)) retorna 10 e SUM(r) soma as células

Os mesmos valores closure alimentam as funções auxiliares construídas para eles: MAP, REDUCE, SCAN, MAKEARRAY, BYROW e BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) transborda cinco valores dobrados, e REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) os dobra em uma única soma. O ambiente capturado viaja para essas chamadas exatamente como em uma chamada direta, então uma taxa vinculada por LET continua visível dentro do corpo do MAP

Vírgulas, ponto e vírgula e o que o SaveAs grava

O parser de fórmulas do HotXLS aceita a vírgula e o ponto e vírgula como separadores de argumentos de forma intercambiável. O texto de fórmulas chega de lugares que você não controla: um ticket de suporte escrito em uma máquina com locale alemão, documentação que usa as vírgulas canônicas do Excel, um script que gera strings de fórmulas, código HotXLS antigo que usava ponto e vírgula em tudo. SUM(A1,A2) e SUM(A1;A2) compilam para a mesma coisa, e uma LAMBDA escrita de qualquer dos jeitos também

Constantes de array inline são a exceção. Dentro de chaves o ponto e vírgula mantém o significado do Excel de separador de linhas, então {1,2;3,4} é um array dois por dois, não uma lista de quatro argumentos. Se você gera fórmulas com ponto e vírgula como separador de argumentos, mantenha as constantes de array na sintaxe do Excel e os dois nunca colidem

Cell.Formula retorna o texto que você atribuiu. Quando o SaveAs grava um arquivo XLSX, pontos e vírgulas usados como separadores de argumentos fora de chaves são gravados como vírgulas, porque o formato de arquivo OOXML armazena fórmulas na sintaxe de vírgulas do Excel, e funções que o Excel guarda sob o prefixo _xlfn. recebem esse prefixo na saída. Uma fórmula carregada de um arquivo e deixada intocada é gravada de volta a partir do texto XML original dela

Lendo lambdas nomeadas de pastas de trabalho de clientes

Pastas de trabalho criadas no Excel guardam nomes LAMBDA do jeito que o Excel os escreve: com um prefixo _xlfn. no nome da função e muitas vezes _xlpm. nos parâmetros. O HotXLS remove esses prefixos quando avalia o nome, mas o TXLSXDefinedName.Formula retorna o texto da definição como o arquivo o forneceu. Uma checagem escrita como Copy(Name.Formula, 1, 6) = 'LAMBDA' passa para nomes que você criou em código e perde todo nome que saiu do Excel. Essa discrepância é fácil de perder, porque os valores calculados ficam corretos de qualquer jeito e só o seu próprio código de auditoria discorda

// IsLambdaDefinition é um helper que você mesmo adiciona, não é API do 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 é a sua própria rotina de log
  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;

A checagem de escopo naquele laço existe porque a chamada por nome no HotXLS procura o callee entre os nomes de nível da pasta de trabalho. Mantenha funções nomeadas no escopo da pasta de trabalho. Um nome LAMBDA com escopo de planilha ainda pode ser lido via FindByName(AName, ASheetIndex), mas uma função que as pessoas chamam de várias planilhas pertence à pasta de trabalho, e as regras de escopo por trás disso estão detalhadas em nomes definidos e fórmulas entre planilhas

Editar uma lambda depois é barato. Atribuir um texto novo ao TXLSXDefinedName.Formula invalida o grafo de dependências, então o próximo Recalculate o reconstrói e reavalia as células que chamam a função; a mecânica está em recálculo incremental e o grafo de dependências. Uma célula cuja fórmula avalia para uma closure pura, como =LAMBDA(x, x*2) sem chamada, não recebe valor em cache nenhum, já que não há número nem texto para guardar; o Excel mostra #CALC! para a mesma célula. Para relatórios que precisam abrir em ferramentas sem suporte a LAMBDA, congele os resultados com Sheet.Cells.ConvertFormulasToValues depois do último Recalculate, o que mantém cada valor em cache e descarta o texto da fórmula

LAMBDA, LET, as funções auxiliares e a API de nomes definidos todos vêm na mesma biblioteca nativa Delphi e C++Builder, que lê e grava XLS, XLSX e ODS sem Excel instalado. A lista completa de funções de fórmulas está na página do componente de planilhas HotXLS