Fórmulas desde VBA vs Ciclos en VBA ¿Cuál es más veloz en Excel?
<<CURSO COMPLETO DE MACROS EN YOUTUBE>>
En ocasiones, cuando tenemos que resolver una situación nos vemos en la difícil situación de elegir entre un procedimiento u otro. Es importante estar bien capacitados en Excel VBA y macros para tomar la mejor decisión.
En este video te mostraré dos maneras de correr macros cuando la situación implica aplicar fórmulas a un rango extenso de celdas. La función que debemos usar es BUSCARV, además de tener que aplicarla a un rango de 300 mil filas.
Ver Video Fórmulas vs Ciclos ¿Cuál es más veloz en Excel?
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
1er round. Aplicar la función BUSCARV con Ciclo For Each Next
Como les dije anteriormente, tenemos un rango de 300 mil filas, a los que deseamos aplicar la función BUSCARV para devolver el nombre de la Sucursal con base al ID de la Sucursal. Usamos el ciclo For Each para recorrer todas las celdas, aplicándoles la función VLOOKUP de la clase WorksheetFunction.
La ventaja de usar funciones de hoja de cálculo desde VBA, es que el valor resultante de usar esas funciones, se puede devolver directamente a celdas o guardar los valores en variables.
Seleccionamos el rango D2:D300000 y aplicamos la primera macro. En nuestras pruebas, la ejecución de la macro duró 27 segundos.
Figura 1. Aplicar función BUSCARV en Excel usando ciclo For Each Next.
Código VBA de la macro
La siguiente macro se llama BUSCARV1 y nos sirve para aplicar la función VLOOKUP con ciclos en Excel.
Option Explicit
'EXCELeINFO
'MVP Sergio Alejandro Campos
'http://www.exceleinfo.com
'https://www.youtube.com/user/sergioacamposh
'http://blogs.itpro.es/exceleinfo
Sub BUSCARV1()
Dim TiempoInicial As Double
Dim Segundos As Double
Dim Celda As Range
TiempoInicial = VBA.Timer
For Each Celda In Selection
Celda.Value = Application.WorksheetFunction.VLookup(Celda.Offset(0, -2), Sheets("Hoja2").Range("A2:B101"), 2)
Next Celda
Segundos = Round(VBA.Timer - TiempoInicial, 2)
MsgBox "Segundos: " & Segundos & vbInformation
End Sub
2do round. Aplicar la función BUSCARV usando FormulaLocal
Tenemos el mismo escenario, 300 mil filas para aplicarles la función BUSCARV. Solo que en esta ocasión utilizaremos la propiedad FormulaLocal para insertar la fórmula en las celdas. Con este procedimiento podemos insertar una fórmula directamente en un rango de celdas, tal como lo haríamos de manera manual, para luego copiar y pegar valores para tener solo el resultado sin las fórmulas. Todo con VBA y macros.
La ventaja de insertar fórmulas a un rango, es que se aplica todo de una vez, y por supuesto es más rápido que recorrer celda por celda.
En nuestras pruebas, la ejecución de la macro fue de 0.9 segundos.
Figura 2. Insertar la función BUSCARV en celdas y luego copiar y pegar valores.
Código VBA de la macro
Sub BUSCARV2()
Dim TiempoInicial As Double
Dim Segundos As Double
TiempoInicial = VBA.Timer
Selection.FormulaLocal = "=BUSCARV(B2,Hoja2!$A$2:$B$101,2,0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Segundos = Round(VBA.Timer - TiempoInicial, 2)
MsgBox "Segundos: " & Segundos & vbInformation
End Sub
¿Cuál macro es la ganadora?
Para este caso en particular, la macro ganadora es la segunda, donde insertamos la función BUSCARV en las 300 mil filas. Los ciclos son buenos, pero ahora tienes más herramientas para tomar la decisión correcta.
Descarga el archivo de ejemplo
Formulas en VBA VS ciclos – EXCELeINFO.xlsm
<<CURSO COMPLETO DE MACROS EN YOUTUBE>>
Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.
