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
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
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 aoxe o IFERROR dentro do corpo o transforma em 0 - Referências continuam referências: um argumento como
A1chega 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