Insertar Fórmulas y funciones en celdas de Excel usando VBA y macros

Las fórmulas y funciones en Excel son usadas comúnmente en celdas de Excel o Nombres definidos, pero para insertarlas no es forzoso hacerlo de manera manual o copiando de otras celdas o archivos. Con macros en Excel y lenguaje VBA también podemos insertar fórmulas en celdas con la ventaja que podemos hacerlo en varias celdas en una sola ejecución.

En este tutorial y video te muestro 5 maneras de insertar fórmulas en Excel, las cuales puedes adaptar a lo que necesites, por ejemplo si estás programando un reporte, en muchas ocasiones será obligatorio usar fórmulas, y qué mejor que hacerlo dinámicamente.

Las 5 macros que veremos harán uso de la función BUSCARV y usaremos una sencilla tabla de ejemplo con el nombre de los meses del año.

Figura 1. Tabla de meses para usar la función BUSCARV.

Usar las funciones de Excel en VBA y poner en celda solo el resultado

Sabemos que toda fórmula de Excel nos devuelve un resultado, pero tal vez no sabías que en una macro puedes usar una función de Excel, combinarla con código VBA y al final sólo poner en las celdas el resultado.

El objeto WorksheetFunction puede considerarse un contenedor de funciones de Excel, pero para ser llamadas desde VBA.

La siguiente macro nos permite usar la función BUSCARV, pero si la usamos mediante WorksheetFunction la función será VLOOKUP. Al ser la misma función, no cambian nos parámetros. También usaremos la instrucción If… Then para validar el resultado devuelto por VLOOKUP, el cuál será usado para la tabla de Meses.

'EXCELeINFO
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh
'http://blogs.itpro.es/exceleinfo
'
Sub FormulaVLOOKUP()

On Error Resume Next
Valor = Application.WorksheetFunction.VLookup(Hoja1.Range("E9"), Hoja1.Range("A8:B20"), 2, 0)

If Valor = 0 Then
    Hoja1.Range("G9").Value = "ID no existe"
Else
    Hoja1.Range("G9").Value = Valor
End If

End Sub

Ver Video Insertar fórmulas con VBA

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Insertar fórmula compatible con cualquier idioma

En esta macro haremos uso de la propiedad Formula las cual nos servirá para insertar formulas en celdas. La ventaja de usar esta propiedad es que la macro podrá funcionar sin importar el idioma en el que tengamos nuestra versión de Office. Un punto a considerar es que las funciones, en la macro se deben escribir en Inglés Y al ser insertadas en las celdas, se insertarán en el idioma local.

Figura 2. Usamos la propiedad Range.Formula para insertar una fórmulas en celdas de Excel.

Sub FormulaIngles()

Hoja1.Range("G12").Formula = "=IFNA(VLOOKUP(E12,A8:B20,2,0),""ID no existe"")"

End Sub

Insertar fórmula en idioma local

Así como tenemos la propiedad Formula, la propiedad FormulaLocal también nos sirve para insertar fórmulas en celdas de Excel, con la diferente de que aquí sí podemos escribir la fórmula en tu idioma local sin tener que saber la conversión a Inglés. La desventaja con esta propiedad que si transportas la macro a otra PC que tenga configurado otro idioma, deberás reescribir las funciones al nuevo idioma.

Sub FormulaLocal()

Hoja1.Range("G15").FormulaLocal = "=SI.ND(BUSCARV(E15,A8:B20,2,0),""ID no existe"")"

End Sub

Insertar fórmula en un rango de celdas

Para insertar fórmulas en un rango de celdas, puedes usar la propiedad Formula o FormulaLocal y sólo definir el rango en el que será insertad la fórmula. Solo te recomiendo considerar el tema de las referencias relativas y absolutas. La desventaja de usar un rango fijo de celdas es que si tu rango aumenta, debes de modificar el rango manualmente.

Sub FormulaRango()

Hoja1.Range("G18:G22").Formula = "=IFNA(VLOOKUP(E18,$A$8:$B$20,2,0),""ID no existe"")"

End Sub

Insertar fórmulas en rango dinámico

En esta macro usamos la propiedad CurrentRegion o el Rango actual para determinar cuál es la última celda de nuestro rango que contiene datos, y así insertar fórmulas en ese rango y evitar cambiar el rango cada que aumentemos los datos hacia abajo. En el archivo de ejemplo obtendremos el Rango actual en base a la celda D24 y para ver el Rango actual puedes usar la combinación de teclas [Control] + [Shift] + [Espacio].

Sub FormulaRangoDinamico()

With Hoja1.Range("D24").CurrentRegion

    UltimaFila = .Rows(.Rows.Count).Row
    
    MsgBox UltimaFila

    Hoja1.Range("G25:G" & UltimaFila).Formula = "=IFNA(VLOOKUP(e25,$A$8:$B$20,2,0),""ID no existe"")"

End With

End Sub

Descarga el archivo de ejemplo

Descarga el archivo de ejemplo para ver las macros y hagas pruebas.

 Insertar Fórmulas y funciones en Excel usando VBA y macros.zip