Al programar en VBA constantemente interactuamos con los datos de las celdas en nuestras hojas y accedemos fácilmente a ellos a través de la propiedad Value sin embargo, cuando llega el momento de insertar una fórmula desde VBA dicha propiedad deja de ser funcional. En esta ocasión revisaremos las alternativas que tenemos para insertar una fórmula de Excel desde una macro.
La propiedad Formula de una celda
Además de la propiedad Value que tienen todas las celdas en Excel, cada una de ellas también tendrá la propiedad Formula la cual almacenará la fórmula asociada. Para mostrar el uso de esta propiedad supondremos un escenario en el que la celda A1 de nuestra hoja tiene el valor 5 y la celda B1 tiene el valor 3. Para insertar una fórmula en la celda C1 que haga la suma de las dos celdas anteriores, insertaré un botón de comando con el siguiente código:
Private Sub CommandButton1_Click()
Range("C1").Formula = "=A1+B1"
End Sub
Al pulsar el botón de comando obtendremos el resultado deseado en la celda C1:
Para dejar en claro la diferencia entre las propiedades Value y Formula de una celda, crearé un nuevo botón de comando que mostrará un cuadro de diálogo con ambas propiedades. El código será el siguiente:
Private Sub CommandButton2_Click()
MsgBox "Value: " & Range("C1").Value & vbCrLf & _
"Formula: " & Range("C1").Formula
End Sub
Al pulsar el botón obtendré un mensaje como el siguiente:
El valor de la celda mostrado en pantalla está almacenado en la propiedad Value mientras que la fórmula asociada a la misma celda se encuentra en la propiedad Formula. Con esto queda clara la diferencia entre ambas propiedades.
Insertar funciones de Excel desde VBA
Como bien sabemos, las fórmulas de Excel pueden contener funciones que facilitarán nuestros cálculos. Si queremos insertar una fórmula desde VBA en Excel que utilice alguna función debemos utilizar su nombre en inglés. Por ejemplo, si en lugar de hacer la suma de las celdas A1+B1 del ejemplo anterior queremos utilizar la función SUMA debemos utilizar el siguiente código:
Private Sub CommandButton1_Click()
Range("C1").Formula = "=SUM(A1, B1)"
End Sub
Al pulsar el botón obtendremos el resultado esperado en la celda C1:
Aun cuando la celda C1 en la hoja muestra la función SUMA, la propiedad Formula en VBA espera recibir el nombre de cualquier función en inglés. Si hacemos caso omiso de esta advertencia nuestra celda desplegará el error #¿NOMBRE? Para comprobar esta aseveración agregaré un nuevo botón, con un código similar al anterior pero solamente cambiaré el nombre de la función SUM (en inglés) por el nombre SUMA (en español). Observa lo que sucede en la celda C1 al pulsar este nuevo botón:
Esto podría convertirse en un problema si no conoces el nombre de las funciones en inglés. De manera personal, siempre recomiendo a cualquier usuario avanzado de Excel que conozca el nombre de las funciones tanto en español como en inglés, pero si eso se dificulta, Excel nos ofrece una solución alterna con la propiedad FormulaLocal.
La propiedad FormulaLocal de una celda
Si al momento de insertar una fórmula desde VBA deseas utilizar una función de Excel pero no recuerdas su nombre en inglés, ni tampoco lo puedes buscar en Internet, entonces tienes la opción de utilizar la propiedad FormulaLocal que te permitirá utilizar el nombre de la función en el idioma que corresponde a la instalación de Excel.
Para demostrar el comportamiento de esta propiedad crearé el mismo ejemplo de la sección anterior que utiliza la función SUMA, pero ahora asignaré la fórmula a la propiedad FormulaLocal de la siguiente manera:
Private Sub CommandButton1_Click()
Range("C1").FormulaLocal = "=SUMA(A1, B1)"
End Sub
Al pulsar el botón de comando se insertará correctamente la fórmula requerida y no veremos el error #¿NOMBRE? como sucedió con la propiedad Formula.
Como puedes ver en la imagen anterior, la función SUMA hace uso de la coma (,) como el separador de argumentos. Esto se debe a la configuración regional de mi equipo y por tal motivo en el código VBA he colocado también la coma para separar los argumentos de la función. Sin embargo, si te encuentras en una región donde el separador de argumentos es el punto y coma (;) entonces deberás modificar el código para incluir dicho carácter o de lo contrario se desplegará un error al momento de pulsar el botón de comando.
El separador de argumentos de una función
Cada vez es más común el compartir información entre diferentes países y el hecho de que tengamos diferencias en el separador de argumentos en diferentes regiones puede llegar a causar un problema ya que aquellos usuarios que utilicen un separador de argumentos diferente al nuestro recibirán un mensaje de error.
Este problema lo podemos resolver si dejamos que Excel descubra el separador de argumentos para posteriormente utilizarlo en la construcción de nuestra fórmula. En el siguiente código utilizo la variable Separador para obtener dicho carácter:
Private Sub CommandButton1_Click()
Separador = Application.International(xlListSeparator)
Range("C1").FormulaLocal = "=SUMA(A1" & Separador & "B1)"
End Sub
De esta manera Excel insertará la fórmula utilizando el separador de argumentos utilizado en el equipo local y no habrá problema alguno en las diferentes regiones que utilicen nuestro archivo.
Es muy importante mencionar que el problema del separador de argumentos se presenta solamente si utilizas la propiedad FormulaLocal. Si utilizas la propiedad Formula siempre deberás utilizar la coma (,) y no tendrás que preocuparte de las posibles diferencias en el separador de argumentos ya que Excel se encargará de adecuar la fórmula para utilizar el carácter adecuado en base a la región donde se utilice el archivo.
La propiedad FormulaR1C1 y FormulaR1C1Local
Finalmente hablaré de las propiedades FormulaR1C1 y FormulaR1C1Local que son equivalentes a las propiedades Formula y FormulaLocal pero con la diferencia de que utilizan el estilo de referencia R1C1, que en español es conocido como el estilo de referencia F1C1.
Si queremos insertar una fórmula desde VBA en la celda C1 tal como en los ejemplos anteriores, pero utilizando un estilo de referencia F1C1, entonces debemos utilizar el siguiente código:
Private Sub CommandButton1_Click()
Range("C1").FormulaR1C1 = "=SUM(R1C1, R1C2)"
End Sub
Private Sub CommandButton2_Click()
Separador = Application.International(xlListSeparator)
Range("C1").FormulaR1C1Local = "=SUMA(F1C1" & Separador & "F1C2)"
End Sub
En el código del primer botón utilizo el nombre de la función en inglés ya que utilizo la propiedad FormulaR1C1 pero para el segundo botón, que hace uso de la propiedad FormulaR1C1Local, puedo indicar el nombre de la función en español. Esto no es extraño ya que lo hemos hecho de la misma manera en los ejemplos anteriores, pero lo que es diferente en esta ocasión es la manera en que se indican las referencias en los argumentos de la función.
En el caso de la propiedad FormulaR1C1 se espera la información de la fórmula en inglés y por lo tanto la referencia se crea utilizando la nomenclatura R1C1 (R para Row), pero al utilizar la propiedad FormulaR1C1Local se espera la información en español y por lo tanto es necesario utilizar la nomenclatura F1C1 (F para Fila) de manera que la fórmula sea creada correctamente.
Nuestra hoja de Excel no reflejará la nomenclatura F1C1 porque está configurada con el estilo de referencia A1, la única manera de ver el mismo estilo de referencia en la barra de fórmulas sería cambiar el estilo de referencia de las fórmulas de nuestro libro de Excel. Aun así es posible utilizar el estilo de referencia F1C1 (R1C1) desde nuestro código VBA para crear fórmulas que hagan referencia a las celdas por su número de columna y de fila.
Con las propiedades que hemos revisado en este artículo tendrás las herramientas necesarias para insertar una fórmula desde VBA sin mayor dificultad. Descarga el libro de trabajo que he utilizado y comprueba por ti mismo cada uno de los métodos presentados en esta publicación.