Este tutorial irá ensinar-lhe como criar fórmulas de células usando VBA.
fórmulas em Vba
usando VBA, você pode escrever fórmulas diretamente para gamas ou células no Excel. It looks like this:
Sub Formula_Example() 'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2" 'Assign a flexible formula to a range of cells Range("d1:d100").FormulaR1C1 = "=RC2+RC3"End Sub
There are two Range properties you will need to know:
- .Formula-cria uma fórmula exacta (referências de células codificadas em código rígido). Bom para adicionar uma fórmula a uma única célula.
- .FormulaR1C1-cria uma fórmula flexível. Bom para adicionar fórmulas a uma gama de células onde as referências das células devem mudar.
para fórmulas simples, é bom usar o .Propriedade Da Fórmula. No entanto, para tudo o resto, recomendamos usar o gravador de macros …
Gravador de macros e fórmulas de células
o gravador de Macros é a nossa ferramenta de go-to para escrever fórmulas de células com VBA. Você pode simplesmente:
- Iniciar gravação
- Digite a fórmula (com referências relativa / absoluta, conforme necessário) para a célula & pressione enter
- Parar gravação
- Abra o VBA e rever a fórmula, adaptando-se, conforme necessário, e copiar+colar o código, quando necessário.
acho que é muito mais fácil introduzir uma fórmula numa célula do que digitar a fórmula correspondente em VBA.
Notice a couple of things:
- The Macro Recorder will always use the .Propriedade FormulaR1C1
- O Gravador de Macro reconhece Absoluto e Relativo Referências de Célula
VBA Propriedade FormulaR1C1
A propriedade FormulaR1C1 usa estilo L1C1 referência de célula (como oposição ao padrão A1-estilo que você está acostumado a ver no Excel).
Aqui estão alguns exemplos:
Sub FormulaR1C1_Examples() 'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4" 'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC" 'Reference D5 (Absolute Row, Relative Column) from cell A1 '=D$5 Range("a1").FormulaR1C1 = "=R5C" 'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"End Sub
Notice that the R1C1-style cell referencing allows you to set absolute or relative references.
referências absolutas
na notação A1 padrão uma referência absoluta se parece com esta: “= $C $ 2″. In R1C1 notation it looks like this:”=R2C3”.
Para criar uma referência Absoluta de célula usando o estilo L1C1 tipo:
- R + número da Linha
- C + número de Colunas
Exemplo: R2C3 representaria célula $C$2 (C é a 3ª coluna).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
referências relativas
referências relativas de células 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 este: “= C2”. Na notação R1C1, você usa parêntesis para compensar a referência da célula da célula actual.exemplo: Introduzir a fórmula “=RC ” na célula B3 referiria a célula D4 (a linha da célula 1 abaixo e a coluna 1 à direita da célula da fórmula).
utilize números negativos para as células de referência acima ou à esquerda da célula actual.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
referências mistas
as referências das células podem ser parcialmente relativas e parcialmente absolutas. Exemplo:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
VBA Formula Property
Ao definir fórmulas com o .Propriedade da fórmula você irá usar sempre a notação A1-style. Introduza a fórmula tal como faria numa célula do Excel, excepto rodeada por citações:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
VBA Formula Tips
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 resto da cadeia de fórmulas. Exemplo:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
cotações de fórmulas
Se você precisar adicionar uma cotação ( ” ) dentro de uma fórmula, digite a cotação duas vezes (“”):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
uma única citação (“) significa VBA o fim de uma cadeia de texto. Enquanto uma citação dupla ( “” ) é tratada como uma citação dentro da cadeia de texto.
Similarly, use 3 quotation marks (“””) to surround a string with a quotation mark ( “)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Assign Cell Formula to String Variable
podemos ler a fórmula numa determinada célula ou Gama e atribuí – la a uma variável:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Diferentes Maneiras para Adicionar Fórmulas de uma Célula
Aqui estão mais alguns exemplos de como atribuir uma fórmula para uma célula:
- Atribuir Diretamente Fórmula
- Define uma Variável de Seqüência de caracteres que Contém a Fórmula
- Usar Variáveis para Criar Fórmulas
Sub MoreFormulaExamples ()' Alternate ways to add SUM formula' to cell B1' Dim strFormula as String Dim cell as Range dim fromRow as Range, toRow as Range Set cell = Range("B1") ' Directly assigning a String cell.Formula = "=SUM(A1:A10)" ' Storing string to a variable ' and assigning to "Formula" property strFormula = "=SUM(A1:A10)" cell.Formula = strFormula ' Using variables to build a string ' and assigning it to "Formula" property fromRow = 1 toRow = 10 strFormula = "=SUM(A" & fromValue & ":A" & toValue & ") cell.Formula = strFormulaEnd Sub
Actualizar Fórmulas
Como um lembrete, para atualização de fórmulas, você pode usar o comando de cálculo:
Calculate
Para atualizar única fórmula, o intervalo, ou toda a folha de cálculo utilizar .Calcular em alternativa:
Sheets("Sheet1").Range("a1:a10").Calculate