Acelerar tus macros de Excel

Sencillos consejos para optimizar las macros en Excel


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

Autor: soloelectronicos

Soy Ingeniero Técnico Electrónico e Ingeniero Técnico Informático de Sistemas : .He trabajado en Sistemas de Telecomunicaciones ,mas tarde como desarrollador , y después como Gestor de desarrollo y como formador en la EET . Mi gran pasion es la electronica (cuanto mas simple,sencilla y de bajo costo mejor: por eso me gusta cada vez mas la resuabilidad de los equipos en desuso) Mi segunda gran pasion son las artes marciales ( practico Tai-chi-chuan,Chi-Kung,Kobudo ,Goshin,karate-sotokan e Iaido)

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios .