En este post se va a intentar dar muy resumidamente pistas para quienes manejen el tema de programación de macros en VB dentro de excel para optimizar el rendimiento de estas . ¿Tus macros van lentas? ¿Problemas a la hora de ejecutarlas? ¿Cuáles son las técnicas recomendadas?
AL COMIENZO DE LAS MACROS
Podemos hacer una llamada a la funcion iniciomacro, la cual hara las siguintes cosas:
- Apagar el parpadeo de pantalla evitando los movimientos de pantalla que se producen al seleccionar celdas, hojas y libros .Lo hacemos con la instrucción Application.screenupdating=False
- Apagar los cálculos automáticos evitando que se recalcule todo cada vez que se pegan o modifican datos
Lo hacemos con la instrucción: Application.calculation=xlCalculationManual - Apagar los eventos automáticos evitando que se disparen macros de evento si las hubiere
Lo hacemos con la instrucción: Application.EnableEvents=False - Apagar visualización de saltos de página sirve para evitar algunos problemas de compatibilidad entre macros Excel 2003 vs. 2007/2010.Lo hacemos con la instrucción: ActiveSheet.DisplayPageBreaks = False
En resumen, siempre debemos comenzar una macros así: iniciamacro e incluiremos en el código la siguinte función:
Sub iniciamacro()
Application.screenupdating=False
Application.calculation=xlCalculationManual
Application.EnableEvents=False
ActiveSheet.DisplayPageBreaks = False
End Sub
EN LA FINALIZACION DE LA EJECUCION DE CADA MACRO
Para optimizar el rendimiento en la finlizacion de culquier macro deemos borrar contenido de portapapeles pues permite limpiar el portapapeles en caso de haber copiado datos . Además debemos volver a su estado original las instrucciones con las que comenzamos la macro qeu hacemos con la instrucción: Application.CutCopyMode = False
En resumen, siempre debemos finalizar la macros con una llamada a la funcion borracache cuyo código es el siguinte:
Sub borracache()
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
Application.CutCopyMode = False
End Sub
OTRAS TECNICAS UTILES
Usar la instrucción WITH.Se usa para evitar tener que referenciar un mismo objeto muchas veces mejorando asi el rendimiento
Ejecución lenta:
Sheets(1).Range(“A1:Z1″).Font.Italic = True
Sheets(1).Range(“A1:Z1″).Font.Interior.Color = vbRed
Sheets(1).Range(“A1:Z1″).MergeCells = True
Ejecución rápida:
With Sheets(1).Range(“A1:Z1″)
.Font.Italic = True
.Font.Interior.Color = vbRed
.MergeCells = True
End With
Evitar la instrucción SELECT que se genera sobre todo en las macros grabadas.La mayoría de las veces no es necesario seleccionar para cumplir el objetivo
Ejecución lenta:
Range(“E1″).Select
Selection.Copy
Range(“D10″).Select
ActiveSheet.Paste
Ejecución rápida:
Range(“E1″).Copy Range(“D10”)
Evitar blucles loops FOR EACH,dado que tener que ir celda por celda consume mucho tiempo y se puede resolver el problema de forma más directa mediante otars técnicas: agrupar, ir a especial, filtros, filtros avanzados (la idea es poder realizar la acción sobre todos los elementos al mismo tiempo, en lugar de tener que ir uno a uno)
Ejecución lenta:
For Each cell In Range(“A1:A10000″)
If cell = Empty Then cell = 0
Next cell
Usar las funciones nativas de Excel pues quizás ya exista una función Excel que lo haga dado que las macros siempre ejecutan más rápido las funciones nativas de Excel
Ejecución lenta…
mProducto = 1
For i = 1 to 100
mProducto = mProducto * Cells(3,i)
Next
Ejecución rápida!
mProducto = Application.WorkSheetFunction.Product(Range(“C1:C100″))
Optimizar el uso de Variables.Tenemos varios métodos para hacer uan mejor gestión de las variables:
- Forzar la declaración de variables:En el editor VBA, menú Herramientas > Opciones > pestaña Editor > marcar “Requerir declaración de variables”
- Usar la variable correcta: si es fecha usar Date, si es texto usar String, si es valor usar Long…
- Evitar el uso de la variable Variant ya que insume más recursos
- Usar nombres de variables que nos digan algo (por ej. “UltimaFila” o “FilaZ” en lugar de “f” o “uf”)
Escribir las macros en módulos y no en hojas,dado que las hojas pueden ser borradas o copiadas y esto generaría problemas inesperados
Separar el proceso en varias macros (divide y vencerás) ,pues si tu macro hace muchas cosas conviene separarla en muchas macros pequeñas y luego unirlas (es más fácil para controlar, auditar, etc…).Además te permite luego poder rehusar alguna parte del proceso en otras macros
Macro muy larga y lenta:
Sub MegaMacro()
‘Codigo limpia datos
’Codigo carga datos
’Código arregla datos
’Código arma reporte
End Sub()
Mejor dividir en diferentes macros para cada proceso:
Sub LimpiaDatos()
‘Codigo…
End Sub Sub
CargaDatos()
‘Codigo…
End Sub
Sub ArreglaDatos()
‘Codigo…
End Sub
Sub ArmaReporte()
‘Codigo…
End Sub
Finalmente podemos unir todos los procesos:
Sub ProcesoCompleto()
Call LimpiaDatos
Call CargaDatos
Call ArreglaDatos
Call ArmaReporte
End Sub()
Ser cuidadoso con la instrucción ON ERROR RESUME NEXT ya que esta instrucción hace que la macro siga avanzando aunque encuentre un error y en algunos casos esto hará que se ignoren errores que no deberían ser ignorados pues podrías tener errores (bugs) y tal vez no sea lo adecuado
Fuente aqui
Buenos datos. Gracias!