14 formas de acelerar y optimizar tus macros excel

Esta información es muy útil para quienes manejen el tema de programación de macros excel. ¿Tus macros van lentas? ¿Problemas a la hora de ejecutarlas? ¿Cuáles son las técnicas recomendadas?

Cuando de programación de macros excel se trata, el tema de la eficiencia y la velocidad es clave. Hay 2 leyes fundamentales que hay que recordar:
.

codigo

a. Cuanto menos código tiene una macro mejor…¿por qué?
Ayuda a que la macro se ejecute mucho más rápido
Simplifica la tarea a la hora de modificar/ampliar/reparar la macro

.

rapidob. Cuanto más rápido se ejecuta una macro mejor!…¿por qué?
Mejora la experiencia del usuario
No mantiene la PC ocupada tanto tiempo

 

Respecto de usar menos código dependerá de las habilidades del programador excel en cuestión. Hemos visto infinidad de casos donde 30 o 40 líneas de código VBA se pueden resumir en 5 o 6 líneas (algo similar pasa con las fórmulas excel). Siempre hay macros o fórmulas que hacen la tarea de forma más directa y sin dar tantas vueltas!

Otra recomendación clave es invertir mucho tiempo inicial en planificar y analizar la lógica del trabajo. Esto nos va a ahorrar muchos problemas y dolores de cabeza posteriores!

Hay algunas instrucciones puntuales que siempre conviene usar y que van a acelerar y optimizar nuestras macros en todos los casos. Vamos a ver repasar algunas técnicas puntuales que podemos usar al comienzo, durante y al final de nuestras macros.

.

AL COMIENZO DE LAS MACROS


1. Apagar el parpadeo de pantalla

Lo hacemos con la instrucción: Application.screenupdating=False
E
vita los movimientos de pantalla que se producen al seleccionar celdas, hojas y libros


2. Apagar los cálculos automáticos

Lo hacemos con la instrucción: Application.calculation=xlCalculationManual
Evita que se recalcule todo cada vez que se pegan o modifican datos


3. Apagar los eventos automáticos

Lo hacemos con la instrucción: Application.EnableEvents=False
Evita que se disparen macros de evento si las hubiere


4. Apagar visualización de saltos de página
Lo hacemos con la instrucción: ActiveSheet.DisplayPageBreaks = False
Sirve para evitar algunos problemas de compatibilidad entre macros Excel 2003 vs. 2007/2010

En resumen, siempre debemos comenzar las macros así:

Application.screenupdating=False
Application.calculation=xlCalculationManual
Application.EnableEvents=False
ActiveSheet.DisplayPageBreaks = False
.

AL FINAL DE LAS MACROS


5. Borrar contenido de portapapeles
Lo hacemos con la instrucción: Application.CutCopyMode = False
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.

En resumen, siempre debemos finalizar las macros así:

Application.screenupdating=True
Application.calculation=xlCalculationAutomatic
Application.EnableEvents=True
ActiveSheet.DisplayPageBreaks = True
Application.CutCopyMode = False

.

OTRAS TECNICAS UTILES


6. Usar la instrucción WITH
Se usa para evitar tener que referenciar un mismo objeto muchas veces

Ejecución leeenta…
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


7. Evitar la instrucción SELECT

Se genera sobre todo en las macros grabadas
La mayoría de las veces no es necesario seleccionar para cumplir el objetivo

Ejecución leeenta…
Range(“E1”).Select
Selection.Copy
Range(“D10”).Select
ActiveSheet.Paste

 

Ejecución rápida!
Range(“E1”).Copy Range(“D10”)


8. Evitar loops FOR EACH

Tener que ir celda por celda consume mucho tiempo
Se puede resolver el problema de forma más directa!

Ejecución leeenta…
For Each cell In Range(“A1:A10000”)
If cell = Empty Then cell = 0
Next cell

* Los loops siempre son leeentos
* En este caso recorre 10.000 celdas!

Ejecución rápida!
Existen diversas formas de evitar los loops. La solución dependerá del caso concreto en cuestión. Generalmente se usan algunas de estas 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!

9. Usar las funciones nativas de Excel

No quieras reinventar la rueda. Quizás ya exista una función Excel que lo haga!

Las macros siempre ejecutan más rápido las funciones nativas de Excel

Ejecución leeenta…
mProducto = 1
For i = 1 to 100
mProducto = mProducto * Cells(3,i)
Next

Ejecución rápida!
mProducto = Application.WorkSheetFunction.Product(Range(“C1:C100”))


10. Forzar la declaración de variables

En el editor VBA, menú Herramientas > Opciones > pestaña Editor > marcar “Requerir declaración de variables”
Luego 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”)

11. Escribir las macros en módulos y no en hojas

Las hojas pueden ser borradas o copiadas y esto generaría problemas inesperados

12. Separar el proceso en varias macros (divide y conquistarás)

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 laaarga…
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()

13. Ser cuidadoso con la instrucción ON ERROR RESUME NEXT

Esta instrucción hace que la macro siga avanzando aunque encuentre un error
En algunos casos esto hará que se ignoren errores que no deberían ser ignorados
Podrías tener errores (bugs) y no enterarte!

14. Comentar bien las macros

¿Qué pasaría si tuvieras que volver a revisar/arreglar/ampliar tu código 8 meses después?
Añadir comentarios te ayudará a describir y recordar la lógica y te ahorrará mucho tiempo!

 

comentariosComparte tus propias técnicas para acelerar y optimizar macros!
Déjanos tus comentarios!

152 Comentarios

  1. herby branden Responder
  2. David Robles Responder
  3. nANCY Responder
  4. Cristián Andrés Contreras Responder
  5. Rosny Responder
    • Ing Luis Enrique Responder
  6. Andrés Responder
  7. Ramón Responder
    • TodoExcel Responder
  8. Montserrat Responder
    • TodoExcel Responder
  9. Martín Gonzalez Responder
    • todoexcel Responder
  10. Dalvin Soriano Responder
    • todoexcel Responder
  11. NORMAN CORDERO Responder
  12. Baltazar Responder
    • todoexcel Responder
    • Ricardo Vega Rico Responder
  13. Santi Quirante Responder
    • todoexcel Responder
    • Zepthcor Responder
      • todoexcel Responder
    • Ricardo Vega Rico Responder
  14. Johny Responder
    • todoexcel Responder
  15. luz maria Responder
    • Adolfo Balderas Responder
  16. CESAR MEDINA Responder
  17. Mónica A. Responder
    • todoexcel Responder
  18. hitler Responder
  19. Rafael Fernandez Responder
  20. Rodrigo Montero Responder
    • Jhon Jairo Mejia Responder
  21. juankmarulo Responder
    • todoexcel Responder
  22. francisco contreras Responder
  23. ING FELIX ENRQIUE Responder
  24. Gustavo Responder
  25. JOSUÉ HENAO ARIAS Responder
  26. R Escalera Responder
    • todoexcel Responder
    • todoexcel Responder
  27. 20-Skunk Responder
  28. Luis Cervantes Responder
    • todoexcel Responder
  29. Fernando H. Responder
  30. Fernando Responder
    • todoexcel Responder
  31. josefina Responder
  32. Melvin Zepeda Responder
    • todoexcel Responder
  33. Hernán Responder
  34. Ramón Responder
    • todoexcel Responder
  35. Ramón Responder
  36. José Manuel agundis Responder
    • todoexcel Responder
    • todoexcel Responder
  37. Eduardo Piña Responder
    • todoexcel Responder
  38. Mensajes Cristianos Responder
  39. Jacvar Responder
  40. Gerard Responder
  41. Gerard Responder
    • todoexcel Responder
  42. Juan Fernando Arroyave Responder
  43. Roberto Maradiaga Responder
    • todoexcel Responder
  44. Alfonso Pineda Responder
  45. CarlosR2 Responder
  46. fadia Responder
    • todoexcel Responder
  47. David Responder
  48. Norma Responder
  49. Laura Responder
    • Edgar L Responder
  50. CAMILA Responder
  51. ramon Responder
    • todoexcel Responder
    • Edgar L Responder
  52. losvangoh Responder
  53. Hans Mella Responder
    • todoexcel Responder
  54. adrian Responder
    • todoexcel Responder
  55. Edison Nieto A Responder
  56. Eliseo Diiaz Salgado Responder
  57. PAPIXTLI Responder
  58. Fernando Carmelo Mamani Blas Responder
  59. Wilson Responder
  60. Daniel Responder
  61. Rafael Rodriguez Responder
  62. JOSÉ LINARES Responder
    • Luis Alfonso Florez Responder
  63. Manus Responder
  64. Roger Zambrana Responder
  65. Alberto Responder
  66. Gabriel Responder
  67. Raul Pema Responder
  68. Julian Cardona Responder
  69. Gustavo Fierro Responder
  70. Orlando sanabria Responder
  71. Roberto Responder
  72. CARLOS L. GUTIERREZ Responder
  73. RICHAR ALEXANDER Responder
  74. Jose Redondo Responder
  75. Javier Pantpja Responder
  76. Martin Cruz Responder
  77. LUIS FERNANDO MONSALVE C Responder
  78. Pedro Diaz Responder
  79. Rocio Rodríguez Responder
  80. JOSE CASTILLO PEREZ Responder
  81. Manuel Cordero S Responder
  82. Sergio A Campos H Responder
  83. César Portales Responder
  84. Roberto Gómez Responder
  85. rolando Responder
  86. Jose Manuel Peralta Responder
  87. NORMAN CORDERO Responder
  88. deybis Responder
  89. Eleazar Enrique Lozano García Responder
  90. ivigras Responder
  91. Oscar Marín Responder
  92. jose riu Responder
  93. Alexcy Jimenez Responder
  94. Gonzalo Edgar Zambrana Rocha Responder
  95. Giovanni Rodriguez Responder
  96. Cristian Responder
  97. Oscar A. Mejía M. Responder
  98. Juan Carlos González Chavarría Responder
  99. Francisco Castro Pérez Responder
    • César Portales Alonso Responder

Dejar una respuesta

Recibe avisos de nuevas descargas, trucos y tutoriales Excel

* Podrás darte de baja cuando quieras!