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!

153 Comments

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

Leave a Reply

Recibe avisos de nuevas descargas, trucos y tutoriales Excel