Para calcular referências circulares intencionais no Delphi, o HotXLS expõe o cálculo iterativo em seu motor XLSX: defina TXLSXWorkbook.Iterate como True e o TXLSXWorkbook.Recalculate percorrerá cada ciclo de referência detectado em direção a um ponto fixo — até a quantidade de passagens definida em IterateCount, ou até que cada célula seja alterada por menos de IterateDelta — em vez de retornar #REF! e desistir
Essa distinção importa mais do que o booleano único sugere. O mesmo motor que detecta um ciclo de referência e se recusa a entrar em loop nele, ao ter uma propriedade invertida, avaliará esse ciclo deliberadamente até que ele se estabilize. Manter esses dois comportamentos claros — quando um ciclo é um defeito a relatar e quando é um modelo a resolver — é o assunto central deste artigo
Por que uma referência circular gera erro por padrão?
Por padrão, o HotXLS trata qualquer ciclo de referência como um erro de criação e o relata em vez de calculá-lo. O TXLSXWorkbook.Recalculate constrói um grafo de dependência de fórmulas, avalia cada célula de fórmula em ordem topológica, e retorna lxErrorRef no momento em que encontra um ciclo — os nós que nunca podem ser liberados durante a ordenação topológica. Esses membros do ciclo mantêm seus valores anteriores em cache; cada fórmula fora do ciclo ainda é avaliada normalmente. A mecânica desse grafo, e por que os membros do ciclo são ignorados em vez de entrarem em loop, são abordadas no artigo complementar sobre recalculação de fórmula incremental e o grafo de dependência
O padrão é o mais seguro porque a maioria dos ciclos são bugs: uma linha de resumo acidentalmente incluída em seu próprio intervalo de SUM, ou uma cópia e cola que deslocou uma referência sobre si mesma. Um código de erro visível no momento da recalculação é exatamente o que você deseja para esses casos. No entanto, uma classe específica e importante de modelos é circular de propósito. Cronogramas de juros sobre juros, alocações circulares de custos ou despesas gerais entre departamentos e cálculos de taxas baseados em saldo descrevem um valor que legitimamente realimenta suas próprias entradas — e o Excel só os calcula se o usuário marcar a caixa Arquivo → Opções → Fórmulas → Habilitar cálculo iterativo
Como habilitar o cálculo iterativo no HotXLS?
O HotXLS espelha essa caixa de seleção do Excel com três propriedades em TXLSXWorkbook: Iterate ativa o modo, e quando está definido como True, um ciclo detectado é entregue a um resolvedor iterativo em vez de produzir lxErrorRef. Considere o par clássico de células de juros sobre juros, onde o saldo final depende dos juros e os juros dependem do saldo:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
Sheet.Cells[1, 2].Value := 1000; // B1: principal de abertura
Sheet.Cells[2, 2].Value := 0.05; // B2: taxa do período
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: saldo = principal + juros
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: juros = saldo * taxa
Book.Iterate := True; // opta pelo cálculo iterativo
if Book.Recalculate = lxOk then
// B3 converge para 1052.63..., B4 para 52.63...
Report(Sheet.Cells[3, 2].Value);
finally
Book.Free;
end;
end;B3 faz referência a B4 e B4 faz referência a B3, de modo que o grafo de dependência relata um ciclo de dois nós. Com Iterate mantido em seu padrão de False, esse par retornaria como lxErrorRef e nenhuma das células se estabilizaria. Com ele definido como True, o Recalculate inicializa o ciclo a partir dos valores atuais em cache e reavalia seus membros passagem após passagem, alimentando as saídas de cada passagem de volta como entradas da próxima passagem, até que os números parem de mudar. Aqui, a forma fechada é principal / (1 - rate), de modo que o saldo se estabiliza em 1052.63 e os juros em 52.63 — os mesmos números que o Excel produz com a iteração habilitada
O que faz a iteração parar?
Duas condições de parada independentes limitam o resolvedor, e entender ambas é o que impede um modelo de gerar erros ou entrar em loop infinito. O IterateCount é o limite rígido de quantas vezes os membros do ciclo são reavaliados; o padrão é 100, correspondendo ao Excel. O IterateDelta é o limite de convergência: após cada passagem, o resolvedor mede a maior alteração numérica em todas as células do ciclo e, uma vez que a alteração máxima cai abaixo de IterateDelta — padrão 0.001 —, o loop de passagem é interrompido antecipadamente. A condição que for atingida primeiro encerra a iteração
Book.Iterate := True;
Book.IterateCount := 1000; // limite rígido: no máximo 1000 passagens sobre o ciclo
Book.IterateDelta := 0.0001; // convergência: para quando cada célula mover < 0.0001
case Book.Recalculate of
lxOk:
// o ciclo convergiu, OU atingiu o limite de 1000 passagens e manteve
// seus valores da última iteração -- ambos os caminhos retornam lxOk quando Iterate é True
SaveWorkbook(Book);
lxErrorRef:
// alcançável apenas com Iterate = False: o ciclo foi relatado, não resolvido
LogWarning('Referência circular com iteração desativada');
end;
Uma consequência merece ser dita claramente, porque é o limite real do recurso. Quando o limite máximo é atingido sem que a alteração caia abaixo de IterateDelta, o SolveCycleIteratively não gera um erro — ele retorna lxOk e deixa as células mantendo seus valores da última iteração, exatamente como o Excel grava os últimos números calculados quando seu próprio limite de iteração é atingido sem convergência. Portanto, um código de retorno bem-sucedido de Recalculate sob o modo iterativo significa "o resolvedor executou", não "o resolvedor convergiu". Um modelo cujo feedback diverge ou oscila consumirá silenciosamente todas as passagens de IterateCount e entregará de volta números que não são um ponto fixo, e nenhuma exceção marcará a diferença
Como a configuração é salva em arquivos XLSX e XLS?
As configurações de cálculo iterativo persistem em ambos os formatos de planilha, de modo que uma pasta de trabalho aberta no Excel se comporta da maneira que seu código Delphi configurou. Do lado do XLSX, o gravador emite o elemento OOXML <calcPr> apenas quando Iterate é True, e omite qualquer atributo que permaneça em seu padrão para manter a saída mínima: uma pasta de trabalho que usa os padrões grava apenas <calcPr iterate="1"/>, enquanto iterateCount aparece apenas quando difere de 100 e iterateDelta apenas quando difere de 0.001. Ao abrir, o TXLSXWorkbook lê os mesmos três atributos de volta, de modo que o processo de ida e volta é simétrico
O motor legado BIFF8 (.xls), o TXLSWorkbook, carrega o estado equivalente por meio de três registros separados sob um trio de propriedades diferente. O EnableIteration é mapeado para o registro CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations para o registro CalcCount ($000C, [MS-XLS] §2.4.31) e MaxIterationChange para o registro CalcDelta ($0010, [MS-XLS] §2.4.32). Os setters impõem os intervalos da especificação — CalcCount deve estar entre 1 e 32767, portanto, MaxIterations é limitado, e um MaxIterationChange negativo retorna ao padrão de 0.001. Defina-os em uma pasta de trabalho .xls carregada e os três registros de cálculo serão gravados fielmente ao salvar
var
Book: TXLSWorkbook; // motor BIFF8 (.xls)
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // registro CalcIter $0011
Book.MaxIterations := 500; // registro CalcCount $000C (limitado a 1..32767)
Book.MaxIterationChange := 0.0001; // registro CalcDelta $0010
Book.SaveAs('model.xls'); // os três registros de cálculo fazem a ida e volta
finally
Book.Free;
end;
end;Observe a divisão deliberada de nomes: o motor XLSX usa Iterate / IterateCount / IterateDelta (o vocabulário OOXML), enquanto o motor BIFF8 usa EnableIteration / MaxIterations / MaxIterationChange (alinhado com os nomes de registros [MS-XLS]). Ambos os trios descrevem as mesmas três opções — uma chave liga/desliga, um limite de iteração e um delta de convergência — com os mesmos padrões de desligado, 100 e 0.001
Quando uma referência circular é um bug em vez de um modelo?
Habilitar a iteração não é uma forma de fazer com que os avisos de referência circular desapareçam, e tratar a questão dessa forma é uma armadilha. Definir o Iterate como True globalmente converte cada ciclo acidental — aqueles que o código de erro padrão deveria capturar — em um número silenciosamente convergido ou silenciosamente não convergido. A disciplina é o inverso: mantenha o Iterate como False como modo normal para que os erros genuínos de criação ainda surjam como lxErrorRef, e habilite a iteração apenas em pastas de trabalho cuja circularidade seja por design e compreendida
Quando um ciclo aparece e você não tem certeza de qual tipo ele é, o rastreador de avaliação de fórmula é a ferramenta que os diferencia: rastreie a fórmula suspeita e a cadeia de referências que se dobra sobre si mesma se tornará visível passo a passo, permitindo que você decida se ela codifica um ciclo de realimentação real ou uma autorreferência perdida. Também ajuda lembrar que uma célula de ciclo pode chamar qualquer função integrada ao longo do loop — o mesmo calculador que resolve uma fórmula de engenharia ou número complexo avalia os membros do ciclo em cada passagem —, portanto, um modelo divergente costuma ser um problema de fórmula dentro do loop, não um problema de configurações de iteração
A lista de verificação prática é curta. Confirme se o loop possui um ponto fixo real antes de confiar na iteração; mantenha o IterateDelta estrito o suficiente para que "convergido" signifique o que seu modelo precisa; e após uma recalculação que você espera que converja, verifique a sanidade de uma saída conhecida em vez de confiar apenas no lxOk, pois esse código não consegue distinguir a convergência de um limite de iteração atingido
O cálculo iterativo de referência circular faz parte do motor XLSX no Componente HotXLS para Delphi, ao lado da recalculação incremental de grafo de dependência sobre a qual se baseia e da persistência OOXML e BIFF8 que carrega a configuração em cada arquivo gravado. Para os modelos financeiros e de engenharia que são circulares de propósito, é a diferença entre um código de erro e uma resposta