Para calcular referências circulares intencionais no Delphi, o HotXLS expõe o cálculo iterativo no seu motor XLSX: defina o TXLSXWorkbook.Iterate como True e o TXLSXWorkbook.Recalculate percorre cada ciclo de referência detetado em direção a um ponto fixo — até ao limite de passagens de IterateCount, ou até que cada célula se altere menos do que o IterateDelta —, em vez de devolver #REF! e desistir
Essa distinção importa mais do que o booleano único sugere. O mesmo motor que deteta um ciclo de referência e se recusa a entrar em loop sobre ele irá, com uma única propriedade alterada, avaliar esse ciclo deliberadamente até que estabilize. Manter a clareza sobre ambos os comportamentos — quando um ciclo é um defeito a reportar e quando é um modelo a resolver — é o tema 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 reporta-o em vez de o calcular. O TXLSXWorkbook.Recalculate constrói um gráfico de dependências de fórmulas, avalia cada célula com fórmula por ordem topológica e devolve lxErrorRef no momento em que encontra um ciclo — os nós que nunca podem ser libertados durante a ordenação topológica. Esses membros do ciclo mantêm os seus valores em cache anteriores; todas as fórmulas fora do ciclo continuam a ser avaliadas normalmente. A mecânica desse gráfico, e a razão pela qual os membros do ciclo são ignorados em vez de entrarem em loop, são abordadas no artigo complementar sobre recálculo incremental de fórmulas e o gráfico de dependências
O padrão é a opção segura porque a maioria dos ciclos são bugs: uma linha de resumo acidentalmente incluída no seu próprio intervalo de SUM, ou uma ação de copiar e colar que deslocou uma referência para si mesma. Um código de erro claro no momento do recálculo é exatamente o que deseja nesses casos. Mas 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 saldos descrevem todos um valor que legitimamente retroalimenta as suas próprias entradas — e o Excel calcula-os apenas quando o utilizador ativa Ficheiro → Opções → Fórmulas → Ativar cálculo iterativo
Como ativar o cálculo iterativo no HotXLS?
O HotXLS reflete essa opção do Excel com três propriedades em TXLSXWorkbook: o Iterate ativa o modo e, quando está definido como True, um ciclo detetado é entregue a um resolvedor (solver) iterativo em vez de produzir lxErrorRef. Considere o par de células canónico 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: capital inicial
Sheet.Cells[2, 2].Value := 0.05; // B2: taxa do período
Sheet.Cells[3, 2].Formula := 'B1+B4'; // B3: saldo = capital + juros
Sheet.Cells[4, 2].Formula := 'B3*B2'; // B4: juros = saldo * taxa
Book.Iterate := True; // ativa o 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;A célula B3 referencia a B4 e a B4 referencia a B3, pelo que o gráfico de dependências reporta um ciclo de dois nós. Deixando o Iterate no seu valor padrão de False, esse par retornaria como lxErrorRef e nenhuma das células estabilizaria. Com a propriedade como True, o Recalculate inicia o ciclo a partir dos valores atuais em cache e reavalia os seus membros passagem após passagem, alimentando as saídas de cada passagem de volta como entradas para a passagem seguinte, até que os números parem de mudar. Aqui, a fórmula fechada é capital / (1 - taxa), pelo que o saldo estabiliza em 1052.63 e os juros em 52.63 — os mesmos valores que o Excel produz com a iteração ativada
O que faz a iteração parar?
Duas condições de paragem independentes limitam o resolvedor (solver), e compreender ambas é o que impede um modelo de falhar ou de entrar num ciclo infinito. O IterateCount é o limite máximo estrito para o número de vezes que os membros do ciclo são reavaliados; o valor padrão é 100, correspondendo ao Excel. O IterateDelta é o limiar de convergência: após cada passagem, o resolvedor mede a maior alteração numérica em todas as células do ciclo e, assim que essa alteração máxima desce abaixo do IterateDelta — padrão 0.001 —, o loop de passagens é interrompido mais cedo. A iteração termina na condição que for cumprida primeiro
Book.Iterate := True;
Book.IterateCount := 1000; // limite máximo: no máximo 1000 passagens sobre o ciclo
Book.IterateDelta := 0.0001; // convergência: para quando cada célula mudar < 0.0001
case Book.Recalculate of
lxOk:
// o ciclo convergiu, OU atingiu o limite de 1000 passagens e manteve os seus
// valores da última iteração -- ambos os caminhos devolvem lxOk quando Iterate é True
SaveWorkbook(Book);
lxErrorRef:
// apenas alcançável com Iterate = False: o ciclo foi reportado, não resolvido
LogWarning('Circular reference with iteration disabled');
end;
Uma consequência deve ser exposta claramente, porque é o limite real desta funcionalidade. Quando o limite máximo é atingido sem que a alteração caia abaixo do IterateDelta, o SolveCycleIteratively não gera um erro — devolve lxOk e deixa as células com os seus valores da última iteração, exatamente como o Excel grava os últimos números calculados quando o seu próprio limite de iteração é atingido sem convergência. Assim, um código de retorno bem-sucedido do Recalculate no modo iterativo significa "O resolvedor executou", e não "o resolvedor convergiu". Um modelo cujo ciclo de feedback divirja ou oscile consumirá silenciosamente todas as passagens de IterateCount e devolverá números que não representam de todo um ponto fixo, e nenhuma exceção sinalizará a diferença
Como a configuração é gravada em ficheiros XLSX e XLS?
As configurações de cálculo iterativo persistem em ambos os formatos de folha de cálculo, pelo que um livro de trabalho aberto no Excel comporta-se da forma como o seu código Delphi o configurou. No lado do XLSX, o escritor emite o elemento OOXML <calcPr> apenas quando o Iterate é True, e omite qualquer atributo que permaneça no seu padrão para manter a saída de dados minimalista: um livro de trabalho que utiliza os padrões grava apenas <calcPr iterate="1"/>, enquanto o iterateCount só surge quando difere de 100 e o iterateDelta apenas quando difere de 0.001. Ao abrir, o TXLSXWorkbook lê de volta os mesmos três atributos, pelo que a viagem de ida e volta é simétrica
O motor BIFF8 (.xls) legado, TXLSWorkbook, carrega o estado equivalente através de três registos separados sob um trio de propriedades diferente. O EnableIteration mapeia para o registo CalcIter ($0011, [MS-XLS] §2.4.33), o MaxIterations para o registo CalcCount ($000C, [MS-XLS] §2.4.31), e o MaxIterationChange para o registo CalcDelta ($0010, [MS-XLS] §2.4.32). Os configuradores (setters) aplicam os intervalos da especificação — o CalcCount deve situar-se entre 1 e 32767, pelo que o MaxIterations é limitado, e um MaxIterationChange negativo reverte para o padrão de 0.001. Defina-os num livro de trabalho .xls carregado e os três registos de cálculo serão gravados fielmente ao guardar
var
Book: TXLSWorkbook; // motor BIFF8 (.xls)
begin
Book := TXLSWorkbook.Create;
try
Book.Open('model.xls');
Book.EnableIteration := True; // registo CalcIter $0011
Book.MaxIterations := 500; // registo CalcCount $000C (limitado a 1..32767)
Book.MaxIterationChange := 0.0001; // registo CalcDelta $0010
Book.SaveAs('model.xls'); // os três registos de cálculo fazem a viagem de ida e volta
finally
Book.Free;
end;
end;Note a divisão deliberada de nomes: o motor XLSX fala em Iterate / IterateCount / IterateDelta (o vocabulário OOXML), enquanto o motor BIFF8 fala em EnableIteration / MaxIterations / MaxIterationChange (alinhados com os nomes dos registos [MS-XLS]). Ambos os trios descrevem os mesmos três controlos — um interruptor para ligar/desligar, um limite máximo de iterações e uma variação 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?
Ativar a iteração não é uma forma de fazer desaparecer os avisos de referência circular, e tratar a questão dessa maneira é a armadilha. Ligar o Iterate globalmente converte cada ciclo acidental — aqueles que o código de erro padrão deveria capturar — num número silenciosamente convergido ou silenciosamente não convergido. A disciplina recomendada é a oposta: mantenha o Iterate como False como modo normal para que os erros genuínos de criação continuem a surgir como lxErrorRef, e ative a iteração apenas nos livros de trabalho cuja circularidade seja intencional e compreendida
Quando surge um ciclo e não tem a certeza de qual o seu tipo, o rastreador de avaliação de fórmulas é a ferramenta que os distingue: rastreie a fórmula suspeita e a cadeia de referência que se fecha sobre si mesma tornar-se-á visível passo a passo, permitindo-lhe decidir se esta codifica um loop de feedback real ou uma autorreferência perdida. Também ajuda lembrar que uma célula do ciclo pode chamar qualquer função integrada no decorrer do loop — o mesmo calculador que resolve uma fórmula de engenharia ou de números complexos avalia os membros do ciclo em cada passagem —, pelo que um modelo divergente é frequentemente um problema da fórmula dentro do loop e não um problema das configurações de iteração
A lista de verificação prática é curta. Confirme que o loop possui um ponto fixo real antes de confiar na iteração; mantenha o IterateDelta estreito o suficiente para que "convergido" signifique o que o seu modelo necessita; e após um Recalculate que espera que convirja, verifique a integridade 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 HotXLS Delphi Excel Component, juntamente com o recálculo incremental de gráfico de dependências no qual se baseia e a persistência de OOXML e BIFF8 que transporta a configuração para cada ficheiro que grava. 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