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:
.

14 formas de acelerar y optimizar tus macros excel

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

.

14 formas de acelerar y optimizar tus macros excelb. 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!

 

14 formas de acelerar y optimizar tus macros excelComparte tus propias técnicas para acelerar y optimizar macros!
Déjanos tus comentarios!

14 formas de acelerar y optimizar tus macros excel .

Información relacionada:

101 respuestas a 14 formas de acelerar y optimizar tus macros excel

  • ramon dice:

    Tengo una macro QUE en excel 2003 que corre muy rapida… basicamente lo que hace es copiar una formula en una columna.. copiar esa columna en la siguiente columna, copiar a solo valores la anterior (para que no queden formulas una vez hecho el calculo)… y asi sucesivamebte hasta 100 columnas. eso si . son 50.000 filas

    Eso en excel 2003 corre muy bien .. PERO EN EXCEL 2007 SE SATURA Y EMPIEZAN A SALIR “RECURSOS INSUFICIENTES…”

    HE PROBADO DE CONVERTIR LOS .XLS A XLSM.. PERO NO GANO NADA, SIGUE SALIENDO EL MENSAJE DE RECURSOS INSUFICIENTES…

    ¿porque se satura el excel 2007?

    ¿ ME VEO OBLIGADO A VOLVEER A EXCEL 2003?

  • TITO dice:

    quiza tengas que convertir el libro excel para que deje de estar en modo compatibilidad.

    Saludos

  • CAMILA dice:

    QUE ES INTERESANTE APRENDER SOBRES LAS FUNCIONES DE LOS MACR0S

  • Laura dice:

    Hoola a todos, a mí me pasa algo parecido a Ramón… después de convertir un montón de archivos del 2003 al 2007 con sus macros (he pasado de xls a xlsm o xlsx)…. al arrastrar las fórmulas de las casillas a la derecha del todo me dice recursos insuficientes…. voy poco a poco arrastrando.. grabando a la vez para así guardar los datos….y ahora cuando cierro el archivo ya no me abre, se puede quedar como 10 min. oooh más pensando.

    La sensación que tengo es que no soporta tantas fórmulas?? es correcto?? si es así, para que tenemos un excell con tantas celdas..???

    • Edgar L dice:

      Hola Laura,

      Porque no guardas tus archivos desde el 2007 como archivos binarios, esto te reduce considerablemente el tamaño del archivo que estas manejando, ademas de que al momento de ejecutar no consumira tantos recursos, y para cuando quieras guardar tu archivo solo tardara unos instantes, esto a servido porque no afecta al archivo.

      Saludos.

  • Norma dice:

    Excelente aporte! gracias!

  • David dice:

    excelente me ha salvado en cuanto ahorro de tiempo,gracias por tu ayuda

  • fadia dice:

    hola, sera que alguien me podria ayudar con esto? tengo un archivo de excel con macros. al abrirlo me sale un error de compilacion en modulo oculto: General TD y no me permite utilizar los complementos, alguien sabe que puedo hacer? gracias

  • CarlosR2 dice:

    Excelente aporte maestro, gracias por todo …!

  • Alfonso Pineda dice:

    Agradezco tu aportacion, la verdad es que venia trabajando diferente, lo voy a aplicar y espero corra mucho mas rápido.

     

     

  • Roberto Maradiaga dice:

    Hola. Tengo una macro que hice en excel 2010, pero por razones de equipo ahora utilizo el ofice 2007, la macro corre bien pero no me realiza el proceso completo, me genera datos basura. Pregunto, ¿ Acaso las macros hechas en office 2010, no son compatibles del todo con el office 2007? O a caso el Office 2007 no tiene las misma funciones del 2010 para ejecutar correctamente la macro? ¿ Como resuelvo esto?

  • Juan Fernando Arroyave dice:

    Amigo, ustedes son los verdaderos expertos.

    Los que comparten el conocimiento sin esperar nada a cambio. muchas gracias por esta información tan valiosa

  • Gerard dice:

    Hola.

    Excelente Post.Gracias por compartir.

    Solamente me queda una duda…En la macro que estoy programando deshabilito la actualizacion automatica…Despues Creo Una serie de formulas y las pego desde el segundo hasta el ultimo renglon…Sin embargo cuando la macro finaliza los valores no se actualizan. Y de esta forma al final me veo obligado a presionar f9 y pierdo todo el tiempo que gane… Es una archivo de 53mil renglones…Alguna sugerencia???

    saludos

  • Gerard dice:

    Mmmm ya agregue la instruccion pero no refresca los valores…Me imagino que tendra que ver con el Application.Calculation = xlCalculationManual que puse al inicio?

    saludos

  • Jacvar dice:

    Hola, quisiera que alguien pudiese ayudarme, he hecho una serie de macros que buscan datos en una BD SqlServer y los copias en hojas de excel, luego con los datos de las hojas aplico calculos entre ellos, pero tengo un problema, debo ejecutar dos o tres veces las macros porque al parecer no actualiza los datos de una vez si cambio los parametros de consulta (los cuales estan en una hoja).  Me sigue mostrando los adatos anteriores.  existe alguna forma de borrar los datos que quedan en memoria o algo asi por el estilo.

    Gracias

  • Mensajes Cristianos dice:

    Buenas Tardes,

    gracias por estos tips, me sirvieron de mucha ayuda.

    he mejorado la rapidez en un 300% o mas, se puso muy rapida mi macro,

    saludos y que Dios le bendiga

  • Eduardo Piña dice:

    hola, tengo una macro la cual me busca datos en mi base de datos, pero se detiene a cada rato si no encuentra un datos y necesito que la macro siga corriendo sin que se detenga y los datos que no estan que simplemente se salte al siguiente datoa a buscar

     

  • Any dice:

    Buenas tardes.

     

    Tengo un excel .xlsm con una macro que borra registros, este archivo lo lleno mediante un paquete de IS con información de una BD pero al momento de querer ejecutar las macro que estan en VB me marca ‘recursos insificientes’, como puedo resolver esto? El archivo tiene más de 60 mil registros

  • José Manuel agundis dice:

    Buenas tardes colegas de Excel:
    Un tip para que me ha dado resultado es usar solo archivos binarios (.xlsb), en verdad hagan la prueba abran un archivo con las extensiones que sean (.xls, .xlsx, .xlm, etc.); y guárdenlo como archivo binario y chequen sus propiedades después, se darán cuenta que bajará más del 50%, cargará más rápido y podrán hacer macros con esta extensión. Espero les sea de gran utilidad.
    Saludos desde México.

  • Ramón dice:

    ON ERROR RESUME NEXT
    Una solución práctica que he encontrado para ésto es la siguiente: Se comienzan comienzan todas las funciones y procedimientos con una instrucción que establece On Error Resume Next en función del valor de una variable globlal V_OmitirErrores que habremos establecido al abrir el libro. Esta variable puede hacerse depender también de si incluimos un caracter concreto en el nombre del libro, por ejemplo, si el nombre del libro contiene los caracteres VE (Versión Estable) se establece V_OmitirErrores a True, en caso contrario a False, con lo cual determinamos si se las macros tendrán o no On Error Resume Next

  • Ramón dice:

    Tenéis en algún sito información sobre cómo evitar los bucles For Each Next, o podéis explicarlo más detalladamente?
    Gracias.
    Un saludo.

    • todoexcel dice:

      Usualmente se pueden evitar ordenando y aplicando acciones sobre todo un bloque de datos a la vez.
      Igualmente cada caso requiere su análisis y su forma de abordarlo…
      Si tienes un caso puntual puedes plantearlo en nuestro foro

  • Hernán dice:

    Muy buenas recomendaciones, las pondré en práctica.

    Felicitaciones genial Post

  • Melvin Zepeda dice:

    Desde aquí comienza mi error, no hace lo que necesito, es esta parte que debería encontrarme el complemento de lo que ya encontré…..

  • josefina dice:

    Hola buen dia… soy novata completamente en esto… y he hecho pequeñitos proyectos que claro que para nada a los que he leido aqui.. gracias por su aceptación…espero no ser muy latosa me interesan las macros pero hay un sin fin de cosas que hacer…

  • Fernando dice:

    Felicitaciones por este compilado de trucos.. son muy efectivos. Sin embargo después de mucho utilizar macros, tengo un problema puntual en la hoja Excel en si. En las celdas al pasar con el tabulador o dar intro, se quedan pegadas en la anterior y toma un tiempo en poder pasar a la siguiente. Sera este un problema de la macro? o quizá un problema de configuración de la hoja Excel??

    de ante mano gracias

  • Fernando H. dice:

    Trabajo las macros pero me considero amateur, gracias por compartir estos trucos!! que libro de VBA me recomendarias o que pagina de VBA me recomendarias ademas de esta pagina y del foro. Gracias

  • Luis Cervantes dice:

    Muy buena aportacion… a excepcion de dos observaciones:

    1. “Cuanto menos código tiene una macro mejor” Esa aseveracion es relativa no siempre aquello que se hace con menos lineas de codigo resulta ser mas rapido, como por ejemplo sacar los valores unicos de un listado de datos de mas de un millon de datos. He visto como el gran filtro avanzado se ha echo añicos… tuve que crear una rutina especial que la cual se ejuta mas rapido y tiene mas lineas de codigo.

    2. Yo no recomendaria la instrucion “Application.screenupdating=False” ya que solo sirve para cubrir las deficiencias que tienen las personas al programar. Si relamente quieren acelerar el codigo, eviten la seleccion de objetos lo mas posible y aprendan buenas tecnicas de programacion. Yo no desactivo la actualizacion de pantalla, ya que como usuario uno piensa “relamente se esta ejecutando la macro? o sera que ya se pasmo”

    Saludos!

    • todoexcel dice:

      Gracias por tus comentarios Luis.
      Respecto al punto 1, lo decimos como “regla general” (luego habrá que ver cada caso en particular)
      Respecto del punto 2, realmente acelera mucho las macros, luego puedes jugar con el Status Bar para mostrar el avance
      ;-)

  • 20-Skunk dice:

    La 7 es bastante más rápido poner:
    Range(“D10″)=Range(“E1″) que copy, si no me crees, haz la prueba copiando 10000 celdas y comparas tiempos.

    La 9 es bastante desacertada, la mayoría de funciones de excel tienen alternativas mucho más livianas para VBA, solo es cuestión de saber encontrarlas o programarlas.
    Un ejemplo de ello son las funciones MAX y MIN.
    Te vuelvo a remitir a pruebas de tiempo para que veas las diferencias.

    Saludos!

  • mary dice:

    Hola una pregunta yo no se mucho de macros pero tenia una que corría muy bien pero derrepente ya no ejecuta y no puedo ver la base en visual basic por que me dice que se ha desabilitado, ya ejecute la acción de habilitar macros pero no puedo hacer correr nuevamente la macros que puedo hacer muchas gracias, espero su apoyo.

  • R Escalera dice:

    Hola. Desarrollé una hoja de cálculo con macros en versión Excel 97-2000, se lee y corre sin problemas en Excel 2007, pero cuando se trata de usar con Excel 2010, manda un error que lista error 9 en tiempo de ejecución. Se agradece de antemano comentarios.

  • JOSUÉ HENAO ARIAS dice:

    Buenísimas muchas gracias.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>