Fórmulas Excel VBA

Este tutorial le enseñará cómo crear fórmulas de celdas utilizando VBA.

Fórmulas en VBA

Usando VBA, puedes escribir fórmulas directamente en Rangos o Celdas en Excel. Se ve así

Sub Formula_Ejemplo()

    'Asigna una fórmula codificada a una sola celda
    Range("b3").Formula = "=b1+b2"
    
    'Asignar una fórmula flexible a un rango de celdas
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

Hay dos propiedades de Range que necesitarás conocer:

• .Formula – Crea una fórmula exacta (referencias de celdas codificadas). Es bueno para añadir una fórmula a una sola celda.
• .FormulaR1C1 – Crea una fórmula flexible. Buena para añadir fórmulas a un rango de celdas donde las referencias de celdas deben cambiar.

Para fórmulas simples, está bien usar la propiedad .Formula. Sin embargo, para todo lo demás, recomendamos usar el Grabador de Macros…

Grabador de macros y fórmulas de celdas

La grabadora de macros  es nuestra herramienta preferida para escribir fórmulas de celdas con VBA. Usted puede simplemente:

• Empezar a grabar
• Escriba la fórmula (con referencias relativas / absolutas según sea necesario) en la celda y pulse Intro
• Detener la grabación
• Abra VBA y revise la fórmula, adaptándola si es necesario y copiando+pegando el código donde sea necesario.

Me parece que es mucho más fácil introducir una fórmula en una celda que escribir la fórmula correspondiente en VBA.

Fíjate en un par de cosas:

• El grabador de macros siempre utilizará la propiedad .FormulaR1C1
• El grabador de macros reconoce las referencias de celda absolutas y relativas

Propiedad VBA FormulaR1C1

La propiedad FormulaR1C1 utiliza el estilo de referencia de celdas R1C1 (a diferencia del estilo A1 estándar que está acostumbrado a ver en Excel). Aquí hay algunos ejemplos:

Sub FormulaR1C1_Ejemplos()

    'Referencia D5 (Absoluta)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'Referencia D5 (Relativa) desde la celda A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'Referencia D5 (Fila Absoluta, Columna Relativa) desde la celda A1
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'Referencia D5 (Fila Relativa, Columna Absoluta) de la celda A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

Fíjate que el estilo R1C1 de referenciación de celdas te permite establecer referencias absolutas o relativas.

Referencias absolutas

En la notación estándar A1, una referencia absoluta tiene el siguiente aspecto «=$C$2». En notación R1C1 se ve así: «=R2C3». Para crear una referencia absoluta a una celda utilizando el estilo R1C1, escriba:

• R + Número de fila
• C + Número de columna

Ejemplo: R2C3 representaría la celda $C$2 (C es la 3ª columna).

  'Referencia D5 (Absoluta)
    '=$D$5
    Range("a1").FormulaR1C1 = "=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 de A1 se ven así: «=C2». En notación R1C1, se utilizan corchetes [] para desplazar la referencia de celda desde la celda actual. Ejemplo: Si se introduce la fórmula «=R[1]C[1]» en la celda B3, se hará referencia a la celda D4 (la celda que está una fila por debajo y una columna a la derecha de la celda de la fórmula). Utilice números negativos para referenciar celdas por encima o a la izquierda de la celda actual.

   'Referencia D5 (relativa) desde la celda A1
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

Referencias Mixtas

Las referencias de celdas pueden ser parcialmente relativas y parcialmente absolutas. Ejemplo:

   'Referencia D5 (fila relativa, columna absoluta) de la celda A1
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

Propiedad de la fórmula VBA

Cuando se establecen fórmulas con la propiedad de fórmula VBA, siempre se utiliza la notación de estilo A1. Se introduce la fórmula igual que se haría en una celda de Excel, excepto que se rodea de comillas:

   'Asigne una fórmula codificada a una sola celda
    Range("b3").Formula = "=b1+b2"

Consejos sobre fórmulas VBA

Fórmulas con variables

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, se usa & para combinar las variables con el resto de la cadena de la fórmula. Ejemplo:

Sub Formula_Variable()
    Dim columna As Long
    columna = 4

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

End Sub

Citas en fórmulas

Si necesita añadir una cita («) dentro de una fórmula, introduzca la cita dos veces («»):

Sub Macro1()
'
' Macro1 Macro
'
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""dd-mm-yyyy"")"
    Range("D10").Select
End Sub

Una comilla simple («) significa para VBA el final de una cadena de texto. Mientras que una comilla doble («») se trata como una cita dentro de la cadena de texto. Del mismo modo, utilice 3 comillas («») para rodear una cadena con una comilla («)

MsgBox """Use 3 para rodear una cadena con comillas""
' Esto imprimirá <"Use 3 para rodear una cadena con comillas"> ventana inmediata

Asignar la fórmula de la celda a una variable de cadena

Podemos leer la fórmula de una celda o rango determinado y asignarla a una variable de cadena:

'Asignar la fórmula de la celda a una variable
Dim formula as String
formula = Range("B1").Formula

Diferentes formas de añadir fórmulas a una celda

Aquí hay algunos ejemplos más de cómo asignar una fórmula a una celda:

1. Asignar directamente la fórmula
2. Definir una variable de cadena que contenga la fórmula
3. Usar Variables para Crear Fórmulas

Sub MasEjemplosDeFormula()
' Formas alternativas de añadir la fórmula SUM
' a la celda B1
'
  Dim formula As String
  Dim celda As Range
  Dim desdeFila As Long, hastaFila As Long

  Set celda = Range("B1")

  ' Asignación directa de una cadena
  celda.formula = "=SUM(A1:A10)"

  ' Almacenando la cadena a una variable
  ' y asignando a la propiedad "Fórmula"
  formula = "=SUMA(A1:A10)"
  celda.formula = formula

  ' Utilizando las variables para construir una cadena
  ' y asignándola a la propiedad "Fórmula"
  desdeFila = 1
  hastaFila = 10
  formula = "=SUM(A" & desdeFila & ":A" & hastaFila & ")"
  celda.formula = formula
End Sub

Actualizar Fórmulas

Como recordatorio, para refrescar las fórmulas, puedes usar el comando Calcular:

Calculate

Para refrescar una fórmula individual, un rango o una hoja de cálculo completa, utilice .Calculate:

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