Este tutorial le enseñará cómo crear fórmulas de celda usando VBA.
Fórmulas en VBA
Usando VBA, puede escribir fórmulas directamente en rangos o celdas en Excel. Se ve así:
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
Hay dos propiedades de rango que deberá conocer:
- .Fórmula: Crea una fórmula exacta (referencias de celda codificadas). Bueno para agregar una fórmula a una sola celda.
- .FormulaR1C1-Crea una fórmula flexible. Es bueno para agregar fórmulas a un rango de celdas donde las referencias de celdas deben cambiar.
Para fórmulas simples, está bien usar el .Propiedad de Fórmula. Sin embargo, para todo lo demás, recomendamos usar el Grabador de Macro
Grabador de Macro y fórmulas de celda
El Grabador de macro es nuestra herramienta de referencia para escribir fórmulas de celda con VBA. Usted puede simplemente:
- Iniciar grabación
- Escriba la fórmula (con referencias relativas / absolutas según sea necesario) en la celda & presione enter
- Detener la grabación
- Abra VBA y revise la fórmula, adaptándola según sea necesario y copiando y pegando el código cuando sea necesario.
Encuentro que es mucho más fácil ingresar una fórmula en una celda que escribir la fórmula correspondiente en VBA.
Observe un par de cosas:
- El Grabador de Macros siempre utilizará el .Propiedad FormulaR1C1
- El Registrador de Macros reconoce Referencias de celda absolutas vs.Relativas
VBA Propiedad FormulaR1C1
La propiedad FormulaR1C1 utiliza referencias de celda de estilo R1C1 (a diferencia del estilo A1 estándar que está acostumbrado a ver en Excel).
Estos son algunos ejemplos:
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
Observe que la referencia de celda de estilo R1C1 le permite establecer referencias absolutas o relativas.
Referencias absolutas
En la notación A1 estándar, una referencia absoluta se ve así :»=C C 2 2″. En notación R1C1 se ve así: «= R2C3».
Para crear una referencia de celda absoluta utilizando el tipo de estilo R1C1:
- R + Número de fila
- C + Número de columna
Ejemplo: R2C3 representaría la celda cell C 2 2 (C es la 3a columna).
'Reference D5 (Absolute) '=$D$5 Range("a1").FormulaR1C1 = "=R5C4"
Referencias relativas
Las referencias relativas a celdas son referencias de celdas que se «mueven» cuando se mueve la fórmula.
En notación A1 estándar se ven así: «= C2». En la notación R1C1, se usan corchetes para desplazar la referencia de celda de la celda actual.
Ejemplo: Ingresar la fórmula «= RC » en la celda B3 haría referencia a la celda D4 (la celda 1 fila debajo y 1 columna a la derecha de la celda de fórmula).
Utilice números negativos para hacer referencia a las celdas de arriba o a la izquierda de la celda actual.
'Reference D5 (Relative) from cell A1 '=D5 Range("a1").FormulaR1C1 = "=RC"
Referencias mixtas
Las referencias de celda pueden ser parcialmente relativas y parcialmente absolutas. Ejemplo:
'Reference D5 (Relative Row, Absolute Column) from cell A1 '=$D5 Range("a1").FormulaR1C1 = "=RC4"
Propiedad de fórmula VBA
Al configurar fórmulas con el .Propiedad de fórmula siempre usará notación de estilo A1. Ingresa la fórmula tal como lo haría en una celda de Excel, excepto rodeado de comillas:
'Assign a hard-coded formula to a single cell Range("b3").Formula = "=b1+b2"
Consejos de fórmulas de VBA
Fórmula con variable
Cuando se trabaja con fórmulas en VBA, es muy común querer usar variables dentro de las fórmulas de celda. Para usar variables, use & para combinar las variables con el resto de la cadena de fórmula. Ejemplo:
Sub Formula_Variable() Dim colNum As Long colNum = 4 Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNumEnd Sub
Citas de fórmula
Si necesita agregar una cita («) dentro de una fórmula, ingrese la cita dos veces («»):
Sub Macro2() Range("B3").FormulaR1C1 = "=TEXT(RC,""mm/dd/yyyy"")"End Sub
Una de comillas simples («) significa VBA el final de una cadena de texto. Mientras que una comilla doble («») se trata como una cita dentro de la cadena de texto.
De manera similar, use 3 comillas ( » «») para rodear una cadena con una comilla («)
MsgBox """Use 3 to surround a string with quotes"""' This will print <"Use 3 to surround a string with quotes"> immediate window
Asignar Fórmula de celda a Variable de cadena
Podemos leer la fórmula en una celda o rango dado y asignarla a una variable:
'Assign Cell Formula to VariableDim strFormula as StringstrFormula = Range("B1").Formula
Diferentes formas de Agregar fórmulas a una Celda
Aquí hay algunos ejemplos más de cómo asignar una fórmula a una celda:
- Asignar directamente la Fórmula
- Definir una variable de cadena que Contenga la Fórmula
- Utilizar Variables para crear 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 recordatorio, para actualizar fórmulas, puede usar el comando Calcular:
Calculate
Para actualizar fórmulas individuales, rangos o hojas de cálculo completas .Calcule en su lugar:
Sheets("Sheet1").Range("a1:a10").Calculate