Excel – VBA – Fórmulas – O guia definitivo

Este tutorial ensinará você a criar fórmulas de células usando o VBA.

Fórmulas em VBA

Usando o VBA, você pode escrever fórmulas diretamente em intervalos ou células no Excel. A aparência é a seguinte:

Sub Formula_Examplo()

    'Atribuir uma fórmula codificada a uma única célula
    Range("b3").Formula = "=b1+b2"
    
    'Atribuir uma fórmula flexível a um intervalo de células
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

Há duas propriedades de Range que você precisará conhecer:

• .Formula – Cria uma fórmula exata (referências de célula codificadas). Ideal para adicionar uma fórmula a uma única célula.
• .FormulaR1C1 – Cria uma fórmula flexível. Ideal para adicionar fórmulas a um intervalo de células em que as referências de células devem mudar.

Para fórmulas simples, não há problema em usar a propriedade .Formula. Entretanto, para todo o resto, recomendamos o uso do Gravador de Macro…

Gravador de Macros e Fórmulas de Células

O Gravador de Macros é a nossa ferramenta preferida para escrever fórmulas de células com VBA. Você pode simplesmente:

• Iniciar a gravação
• Digitar a fórmula (com referências relativas/absolutas, conforme necessário) na célula e pressionar Enter
• Parar a gravação
• Abrir o VBA e revisar a fórmula, adaptando-a conforme necessário e copiando e colando o código onde for preciso.

Acho que é muito mais fácil inserir uma fórmula em uma célula do que digitar a fórmula correspondente no VBA.

 

Observe alguns aspectos:

• O Gravador de Macro sempre usará a propriedade .FormulaR1C1
• O gravador de macros reconhece referências de células absolutas e relativas

Propriedade FormulaR1C1 do VBA

A propriedade FormulaR1C1 usa referência de célula no estilo R1C1 (em oposição ao estilo A1 padrão que você está acostumado a ver no Excel).

Aqui estão alguns exemplos:

Sub FormulaR1C1_Examplos()

    'Referência D5 (Absoluta)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'Referência D5 (Relativa) da célula A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'Referência D5 (Linha Absoluta, Coluna Relativa) da célula A1
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'Referência D5 (linha relativa, coluna absoluta) da célula A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

Observe que a referência de célula no estilo R1C1 permite que você defina referências absolutas ou relativas.

Referências Absolutas

Na notação padrão A1, uma referência absoluta tem a seguinte aparência: “=$C$2”. Na notação R1C1, ela tem a seguinte aparência: “=R2C3”.

Para criar uma referência absoluta de célula usando o estilo R1C1, digite:

• R + Número da linha
• C + Número da coluna

Exemplo: R2C3 representaria a célula $C$2 (C é a 3ª coluna).

    'Referência D5 (Absoluta)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

Referências Relativas

As referências de células relativas são referências de células que “se movem” quando a fórmula é movida.

Na notação padrão A1, elas têm a seguinte aparência: “=C2”. Na notação R1C1, você usa colchetes [] para deslocar a referência de célula da célula atual.

Exemplo: Inserção da fórmula “=R[1]C[1]” na célula B3 faria referência à célula D4 (a célula 1 linha abaixo e 1 coluna à direita da célula da fórmula).

Use números negativos para fazer referência a células acima ou à esquerda da célula atual.

    'Referência D5 (Relativa) da célula A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

Referências Mistas

As referências de células podem ser parcialmente relativas e parcialmente absolutas. Exemplo:

    'Referência D5 (linha relativa, coluna absoluta) da célula A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

Propriedade de Fórmula VBA

Ao definir fórmulas com a propriedade .Formula, você sempre usará a notação de estilo A1. A fórmula é inserida da mesma forma que em uma célula do Excel, exceto entre aspas:

    'Atribuir uma fórmula codificada a uma única célula
    Range("b3").Formula = "=b1+b2"

Dicas de Fórmulas VBA

Fórmula com Variável

Ao trabalhar com fórmulas em VBA, é muito comum querer usar variáveis dentro das fórmulas das células. Para usar variáveis, você usa & para combinar as variáveis com o restante da string da fórmula. Exemplo:

Sub Formula_Variavel()
    Dim colNum As Long
    colNum = 4

    Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNum

End Sub

Fórmulas com Aspas

Se precisar adicionar aspas (“) em uma fórmula, digite as aspas duas vezes (“”):

Sub Macro2()
    Range("B3").FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/aaaa"")"
End Sub

Uma aspa simples (“) significa para o VBA o fim de uma cadeia de texto. Já uma aspa dupla (“”) é tratada como uma aspa dentro da cadeia de texto.

Da mesma forma, use 3 aspas (“””) para envolver uma cadeia de caracteres com uma aspa (“)

MsgBox """Use 3 para colocar aspas em uma string"""
' Isso imprimirá <"Use 3 para colocar aspas uma string"> na janela verificação imediata

Atribuir Fórmula de Célula a uma Variável Tipo String

Podemos ler a fórmula em uma determinada célula ou intervalo e atribuí-la a uma variável tipo String:

'Atribuir fórmula de célula a uma variável
Dim strFormula as String
strFormula = Range("B1").Formula

Diferentes Maneiras de Adicionar Fórmulas a uma Célula

Aqui estão mais alguns exemplos de como atribuir uma fórmula a uma célula:

1. Atribuir fórmula diretamente
2. Definir uma variável tipo String que contenha a fórmula
3. Usar variáveis para criar fórmulas

Sub MaisExemplosFormulas ()
' Maneiras alternativas de adicionar a fórmula SOMA
' para a célula B1
'
  Dim strFormula as String
  Dim cell as Range
  dim fromRow as long, toRow as long

  Set cell = Range("B1")

  ' Atribuição direta de uma string
  cell.Formula = "=SUM(A1:A10)"

  ' Armazenamento de string em uma variável
  ' e atribuindo à propriedade "Formula"
  strFormula = "=SUM(A1:A10)"
  cell.Formula = strFormula

  ' Uso de variáveis para criar uma string
  ' e atribuindo-o à propriedade "Formula".
  fromRow = 1
  toRow   = 10
  strFormula = "=SUM(A" & fromValue & ":A" & toValue & ")
  cell.Formula = strFormula
End Sub

Atualizar Fórmulas

Como lembrete, para atualizar fórmulas, você pode usar o comando Calculate:

Calculate

Para atualizar uma única fórmula, intervalo ou planilha inteira, use o comando .Calculate:

Sheets("Planilha1").Range("a1:a10").Calculate