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:

1.  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
2.  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
3. Apagar los eventos automáticos evitando que se disparen macros de evento si las hubiere
   Lo hacemos con la instrucción: Application.EnableEvents=False
4.  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