Este tutorial le enseñará cómo crear fórmulas de celda usando VBA.
Fórmulas en VBA
Con VBA, puede escribir fórmulas directamente en rangos o celdas en Excel. Se parece a esto:
| 123456789 | Sub Formula_Example ()'Asignar una fórmula codificada a una sola celdaRango ("b3"). Fórmula = "= b1 + b2"'Asignar una fórmula flexible a un rango de celdasRango ("d1: d100"). FórmulaR1C1 = "= RC2 + RC3"End Sub |
Hay dos propiedades de rango que necesitará conocer:
- .Fórmula - Crea una fórmula exacta (referencias de celda codificadas). Bueno para agregar una fórmula a una sola celda.
- .FórmulaR1C1 - Crea una fórmula flexible. Bueno para agregar fórmulas a un rango de celdas donde las referencias de celda deberían cambiar.
Para fórmulas simples, está bien usar la propiedad .Formula. Sin embargo, para todo lo demás, recomendamos utilizar el Grabador de macros…
Grabador de macros y fórmulas de celda
Macro Recorder es nuestra herramienta de referencia para escribir fórmulas de celda con VBA. Simplemente puede:
- Iniciar la grabación
- Escriba la fórmula (con referencias relativas / absolutas según sea necesario) en la celda y presione enter
- Para de grabar
- Abra VBA y revise la fórmula, adaptándola según sea necesario y copiando + pegando el código donde sea necesario.
Lo encuentro más fácil ingresar una fórmula en una celda que escribir la fórmula correspondiente en VBA.
Observe un par de cosas:
- La grabadora de macros siempre usará la propiedad .FormulaR1C1
- El registrador de macros reconoce las referencias de celda absolutas frente a las relativas
VBA FormulaR1C1 Propiedad
La propiedad FormulaR1C1 utiliza referencias de celda de estilo R1C1 (a diferencia del estilo A1 estándar que está acostumbrado a ver en Excel).
Aquí hay unos ejemplos:
| 12345678910111213141516171819 | Sub FórmulaR1C1_Examples ()'Referencia D5 (Absoluto)'= $ D $ 5Rango ("a1"). FórmulaR1C1 = "= R5C4"'Referencia D5 (relativo) de la celda A1'= D5Rango ("a1"). FórmulaR1C1 = "= R [4] C [3]"'Referencia D5 (Fila absoluta, Columna relativa) de la celda A1'= D $ 5Rango ("a1"). FórmulaR1C1 = "= R5C [3]"'Referencia D5 (Fila relativa, Columna absoluta) de la celda A1'= $ D5Rango ("a1"). FórmulaR1C1 = "= R [4] C4"End Sub |
Tenga en cuenta que la referencia de celda de estilo R1C1 le permite establecer referencias absolutas o relativas.
Referencias absolutas
En la notación estándar A1, una referencia absoluta se ve así: “= $ C $ 2”. En la 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 $ C $ 2 (C es la tercera columna).
| 123 | 'Referencia D5 (Absoluto)'= $ D $ 5Rango ("a1"). FórmulaR1C1 = "= R5C4" |
Referencias relativas
Las referencias de celda relativas son referencias de celda que se "mueven" cuando se mueve la fórmula.
En la notación estándar A1 se ven así: “= C2”. En la notación R1C1, usa corchetes [] para compensar la referencia de celda de la celda actual.
Ejemplo: Ingresar la fórmula “= R [1] C [1]” en la celda B3 haría referencia a la celda D4 (la celda 1 fila a continuación y 1 columna a la derecha de la celda de fórmula).
Utilice números negativos para hacer referencia a las celdas que se encuentran arriba o a la izquierda de la celda actual.
| 123 | 'Referencia D5 (relativo) de la celda A1'= D5Rango ("a1"). FórmulaR1C1 = "= R [4] C [3]" |
Referencias mixtas
Las referencias de celda pueden ser parcialmente relativas y parcialmente absolutas. Ejemplo:
| 123 | 'Referencia D5 (Fila relativa, Columna absoluta) de la celda A1'= $ D5Rango ("a1"). FórmulaR1C1 = "= R [4] C4" |
Propiedad de fórmula de VBA
Al configurar fórmulas con.Propiedad de la fórmula siempre utilizará la notación de estilo A1. Ingresa la fórmula como lo haría en una celda de Excel, excepto rodeada de citas:
| 12 | 'Asignar una fórmula codificada a una sola celdaRango ("b3"). Fórmula = "= b1 + b2" |
Consejos sobre 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 las celdas. Para usar variables, usa & para combinar las variables con el resto de la cadena de fórmulas. Ejemplo:
| 1234567 | Sub Formula_Variable ()Dim colNum As LongcolNum = 4Rango ("a1"). FormulaR1C1 = "= R1C" & colNum & "+ R2C" & colNumEnd Sub |
Cotizaciones de fórmulas
Si necesita agregar una cita (“) dentro de una fórmula, ingrese la cita dos veces (“ ”):
| 123 | Sub Macro2 ()Rango ("B3"). FórmulaR1C1 = "= TEXTO (RC [-1]," "mm / dd / aaaa" ")"End Sub |
Una sola cita (“) significa para VBA el final de una cadena de texto. Mientras que una cita doble (“”) se trata como una cita dentro de la cadena de texto.
Del mismo modo, use 3 comillas (“” ”) para rodear una cadena con una comilla (“)
| 12 | MsgBox "" "Use 3 para rodear una cadena con comillas" ""'Esto imprimirá una ventana inmediata |
Asignar fórmula de celda a variable de cadena
Podemos leer la fórmula en una celda o rango determinado y asignarla a una variable de cadena:
| 123 | 'Asignar fórmula de celda a variableDim strFormula como cadenastrFormula = Rango ("B1"). Fórmula |
Diferentes formas de agregar fórmulas a una celda
Aquí hay algunos ejemplos más sobre cómo asignar una fórmula a una celda:
- Asignar fórmula directamente
- Definir una variable de cadena que contenga la fórmula
- Utilice variables para crear fórmulas
| 12345678910111213141516171819202122232425 | Sub MoreFormulaExamples ()'Formas alternativas de agregar fórmula SUM'a la celda B1'Dim strFormula como cadenaAtenuar celda como rangodim fromRow como rango, toRow como rangoEstablecer celda = Rango ("B1")'Asignación directa de una cadenacell.Formula = "= SUM (A1: A10)"'Almacenar cadena en una variable'y asignando a la propiedad "Fórmula"strFormula = "= SUMA (A1: A10)"cell.Formula = strFormula'Usando variables para construir una cadena'y asignándola a la propiedad "Fórmula"fromRow = 1toRow = 10strFormula = "= SUM (A" & fromValue & ": A" & toValue & ")cell.Formula = strFormulaEnd Sub |
Actualizar fórmulas
Como recordatorio, para actualizar fórmulas, puede usar el comando Calcular:
| 1 | Calcular |
Para actualizar una fórmula, un rango o una hoja de cálculo completa, use .Calculate en su lugar:
| 1 | Hojas ("Hoja1"). Rango ("a1: a10"). Calcular |
