Fórmulas no Excel através de programação

Certamente o recurso mais utilizado do Excel são as fórmulas, isto é, cálculos envolvendo uma ou mais células. As fórmulas podem ser desde a simples referência ao conteúdo de uma única célula até a inclusão de funções avançadas, como tratamentos estatísticos.

Para falar desse assunto, vou assumir o cenário descrito nos posts sobre criação e formatação de planilhas Excel através de scripts.

Por exemplo, a primeira linha do código abaixo configura a célula B32 (linha 32 da coluna B) para que ela simplesmente exiba o conteúdo da célula B2 (linha 2 da coluna B). Depois, configuro a célula C32 (linha 32 da coluna C) para mostrar a somatória das linhas 2 e 3 da coluna C:

folha.Cells (32,"B").Formula = "=$B$2"
folha.Cells (32,"C").Formula = "=$C$2+$C$3"
folha.Cells (33,"C").Formula = "=$C$2+$C$3+Sheet2!A1"

Veja que o valor da fórmula sempre começa com o sinal de = e que não há espaços separando as referências envolvidas. O sinal $ indica que a referência é fixa, de forma que se esta fórmula for copiada para outra célula, a referência permanece a mesma. Portanto, o sinal $ não é obrigatório e, se for omitido, a referência é modificada automaticamente para refletir a nova localização sempre que a fórmula for copiada.

Também é permitido referenciar células de outras folhas de trabalho, bastando incluir o nome dessa folha seguido do sinal !, como na última linha do trecho acima.

A propriedade Formula usada nos exemplos vem do objeto Range mas até agora só a usamos para acessar a fórmula de células individuais. O que acontece se atribuirmos uma fórmula para um Range que englobe mais de uma célula, como no exemplo que segue?

folha.Range("B32:C32").Formula = "=SUM(B2:B31)"
folha.Range("B33:C33").Formula = "=SUM($B$2:$B$31)"

Se as referências contidas na fórmula possuirem o sinal $ (como na 2a linha do exemplo), todas as células do Range terão exatamente a mesma fórmula e, portanto, o mesmo valor. Se não tiverem esse sinal, o Excel modificará a fórmula para cada célula, substituindo a referência original por outra de acordo com a nova linha/coluna, do mesmo modo que acontece quando copiamos e colamos fórmulas pela interface gráfica do Excel.

Uma última coisa a observar é que a Microsoft tem versões traduzidas do Office para outras linguagens. Se seu Excel está em português, quando você monta fórmulas através da interface gráfica usa os nomes das funções traduzidos para português. Para montar o último exemplo pela interface, ao invés de SUM você usaria SOMA, por exemplo.

Então este script só funcionaria para o Excel em inglês? A resposta é não. A Microsoft mantem mais de uma propriedade com fórmulas. A propriedade Formula contem sempre funções com o nome original, em inglês. Se quiser configurar fórmulas com nome traduzido, use a propriedade FormulaLocal pois nela se usa funções cujos nomes estão na linguagem local. As linhas abaixo são equivalentes para quem tem o Excel em português:

folha.Range ("B32:C32").Formula = "=SUM(B2:B31)"
folha.Range("B33:C33").FormulaLocal = "=SOMA(B2:B31)"

Tenha em mente, no entanto, que atribuir uma fórmula usando a propriedade FormulaLocal limita o script a ser usado exclusivamente em ambientes onde o Excel usa a mesma lingua que você.

Mais Informações
Modelo de objetos do Excel, Leitura e Gravação de planilhas Excel através de scripts, Download do Exemplo.