Este tutorial irá ensiná-lo 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. Se parece com isso:
| 123456789 | Sub Formula_Example ()'Atribuir uma fórmula codificada a uma única célulaIntervalo ("b3"). Fórmula = "= b1 + b2"'Atribuir uma fórmula flexível a um intervalo de célulasIntervalo ("d1: d100"). FormulaR1C1 = "= RC2 + RC3"End Sub |
Existem duas propriedades de intervalo que você precisa saber:
- .Fórmula - Cria uma fórmula exata (referências de células embutidas em código). Bom para adicionar uma fórmula a uma única célula.
- .FormulaR1C1 - Cria uma fórmula flexível. Bom para adicionar fórmulas a um intervalo de células onde as referências de células devem ser alteradas.
Para fórmulas simples, não há problema em usar a propriedade .Formula. No entanto, para todo o resto, recomendamos o uso do Gravador de macro…
Gravador de macro e fórmulas celulares
O gravador de macro é nossa ferramenta para escrever fórmulas de células com VBA. Você pode simplesmente:
- Comece a gravar
- Digite a fórmula (com referências relativas / absolutas conforme necessário) na célula e pressione Enter
- Pare de gravar
- Abra o VBA e revise a fórmula, adaptando conforme necessário e copiando e colando o código onde necessário.
Eu acho que é muito facil inserir uma fórmula em uma célula do que digitar a fórmula correspondente em VBA.
Observe algumas coisas:
- O Gravador de Macro sempre usará a propriedade .FormulaR1C1
- O gravador de macro reconhece referências celulares absolutas vs. relativas
Propriedade VBA FormulaR1C1
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:
| 12345678910111213141516171819 | Sub FormulaR1C1_Examples ()'Referência D5 (Absoluto)'= $ D $ 5Intervalo ("a1"). FormulaR1C1 = "= R5C4"'Referência D5 (relativa) da célula A1'= D5Intervalo ("a1"). FormulaR1C1 = "= R [4] C [3]"'Referência D5 (linha absoluta, coluna relativa) da célula A1'= D $ 5Intervalo ("a1"). FormulaR1C1 = "= R5C [3]"'Referência D5 (linha relativa, coluna absoluta) da célula A1'= $ D5Intervalo ("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 A1 padrão, uma referência absoluta se parece com isto: “= $ C $ 2”. Na notação R1C1 é assim: “= R2C3”.
Para criar uma referência de célula Absoluta usando o tipo de estilo R1C1:
- R + número da linha
- C + número da coluna
Exemplo: R2C3 representaria a célula $ C $ 2 (C é a 3ª coluna).
| 123 | 'Referência D5 (Absoluto)'= $ D $ 5Intervalo ("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 A1 padrão, eles se parecem com isto: “= C2”. Na notação L1C1, você usa colchetes [] para deslocar a referência de célula da célula atual.
Exemplo: inserir a 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 às células acima ou à esquerda da célula atual.
| 123 | 'Referência D5 (relativa) da célula A1'= D5Intervalo ("a1"). FormulaR1C1 = "= R [4] C [3]" |
Referências Mistas
As referências de células podem ser parcialmente relativas e parcialmente absolutas. Exemplo:
| 123 | 'Referência D5 (linha relativa, coluna absoluta) da célula A1'= $ D5Intervalo ("a1"). FormulaR1C1 = "= R [4] C4" |
Propriedade da Fórmula VBA
Ao definir fórmulas com o.Propriedade da Fórmula você sempre usará a notação de estilo A1. Você insere a fórmula como faria em uma célula do Excel, exceto entre aspas:
| 12 | 'Atribuir uma fórmula codificada a uma única célulaIntervalo ("b3"). Fórmula = "= b1 + b2" |
Dicas de Fórmula VBA
Fórmula com variável
Ao trabalhar com fórmulas em VBA, é muito comum querer usar variáveis dentro das fórmulas de células. Para usar variáveis, você usa & para combinar as variáveis com o resto da string de fórmula. Exemplo:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Intervalo ("a1"). FormulaR1C1 = "= R1C" & colNum & "+ R2C" & colNumEnd Sub |
Citações de fórmula
Se você precisar adicionar uma cotação (“) dentro de uma fórmula, insira a cotação duas vezes (“ ”):
| 123 | Sub Macro2 ()Intervalo ("B3"). FormulaR1C1 = "= TEXTO (RC [-1]," "mm / dd / aaaa" ")"End Sub |
Uma aspa simples (“) significa para o VBA o final de uma sequência de texto. Já as aspas duplas (“”) são tratadas como aspas dentro da string de texto.
Da mesma forma, use 3 aspas (“” ”) para cercar uma string com uma aspa (“)
| 12 | MsgBox "" "Use 3 para colocar uma string entre aspas" ""'Isto irá imprimir a janela imediata |
Atribuir Fórmula de Célula a Variável de String
Podemos ler a fórmula em uma determinada célula ou intervalo e atribuí-la a uma variável de string:
| 123 | 'Atribuir Fórmula de Célula à VariávelDim strFormula as StringstrFórmula = Intervalo ("B1"). Fórmula |
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:
- Atribuir Fórmula Diretamente
- Defina uma variável de string contendo a fórmula
- Use Variáveis para Criar Fórmula
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Formas alternativas de adicionar fórmula SUM'para a célula B1'Dim strFormula as StringCélula escurecida como intervalodim fromRow as Range, toRow as RangeDefinir célula = intervalo ("B1")'Atribuindo Diretamente uma Stringcélula.Fórmula = "= SOMA (A1: A10)"'Armazenando string em uma variável'e atribuindo a propriedade "Fórmula"strFórmula = "= SOMA (A1: A10)"cell.Formula = strFormula'Usando variáveis para construir uma string'e atribuindo-o à propriedade "Fórmula"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaEnd Sub |
Atualizar Fórmulas
Como um lembrete, para atualizar as fórmulas, você pode usar o comando Calcular:
| 1 | Calcular |
Para atualizar uma única fórmula, intervalo ou planilha inteira, use .Calculate:
| 1 | Folhas ("Folha1"). Intervalo ("a1: a10"). Calcular |
