Muchas veces nos hemos encontrado programando nuestras macros en Excel, y después de un tiempo incluyendo líneas y líneas de código, al ejecutarla, observamos como el tiempo de ejecución es extremadamente elevado, o muy lento (en ocasiones, incluso bloqueamos la aplicación).
En esta entrada contaré alguna de las buenas prácticas que agilizan y optimizan los tiempos de ejecución de nuestras macros... aunque a veces, ni yo mismo utilizo ;-)
Mis 15 consejos, no necesariamente por orden de importancia, serían:
1. Usar la propiedad .ScreenUpdating al inicio y final de nuestra macro, para evitar la actualización de la pantalla cada vez que se realiza una acción sobre la hoja de cálculo:
2. Emplear la propiedad .Calculation para evitar el recálculo de las operaciones de nuestra hoja:
3. Cuando sea posible usar la instrucción WITH...END WITH:
4. Liberar memoria cuando definamos variables con objeto:
5. Este no siempre es posible, pero cuando lo sea, mejor no emplear la instrucción IF... THEN...ELSE. Por ejemplo, en lugar de:
6. Hacer uso de las funciones estándar de Excel en VBA en lugar de contruir procesos que hagan lo mismo. Por ejemplo utilizar el siguiente código:
7. Especificar/concretar lo máximo posible a la hora de definir variables, evitando las variables tipo Variant o Object. Por ejemplo:
8. No seleccionar un rango para trabajar sobre él, si no es estrictamente necesario:
9. Evitar Copiar y Pegar (Copy and Paste), empleando otras instrucciones similares, pero algo más eficientes. Por ejemplo, es mejor usar:
10. Emplear los índices para los elementos de las colecciones, en lugar de los nombres que definen estos elementos. Por ejemplo:
11. Otro caso muy frecuente, el uso de "" en lugar de la variable vbNullString, mucho más efectiva.
Mejor usar
12. Otra forma muy útil cuando trabajamos con eventos sobre nuestras hojas o libros (o controles), desactivar los Events al inicio de nuestra programación y activarlos de nuevo al final:
13. Intentar reducir el número de líneas de código, empleando para ello la posibilidad de escribir en una misma línea usando el separador ':' (dos puntos). Por ejemplo:
14. Declarar las variables OLE directamente. Un ejemplo:
15. Y uno más, el último pero no el menos importante (quizá sea el más utilizado). Cuando se haga necesario emplear bucles del tipo FOR, es más óptimo emplear FOR EACH...NEXT que FOR...NEXT cuando recorremos colecciones 'indexadas'. Por ejemplo, es más rápido el primero que el segundo:
Por supuesto existen otras acciones que mejoran la eficiencia de nuestras macros, pero en algún punto hay que parar, y es que la optimización del VBA no tiene fin...
En esta entrada contaré alguna de las buenas prácticas que agilizan y optimizan los tiempos de ejecución de nuestras macros... aunque a veces, ni yo mismo utilizo ;-)
Mis 15 consejos, no necesariamente por orden de importancia, serían:
1. Usar la propiedad .ScreenUpdating al inicio y final de nuestra macro, para evitar la actualización de la pantalla cada vez que se realiza una acción sobre la hoja de cálculo:
Application.ScreenUpdating=False '... nuestro código VBA... Application.ScreenUpdating=True
2. Emplear la propiedad .Calculation para evitar el recálculo de las operaciones de nuestra hoja:
Application.Calculation=xlCalculationManual '... nuestro código VBA... Application.Calculation=xlCalculationAutomatic 'o bien antes de cerrar el libro Application.CalculateBeforeSave=True
3. Cuando sea posible usar la instrucción WITH...END WITH:
With MyLabel .Height = 2000 .Width = 2000 .Caption = "Esta es mi etiqueta" End With
4. Liberar memoria cuando definamos variables con objeto:
Dim wsHoja as Worksheet 'Definimos la variable objeto Set wsHoja = Hoja1 '... nuestro código VBA... 'Liberamos la memoria Set wsHoja = Nothing
5. Este no siempre es posible, pero cuando lo sea, mejor no emplear la instrucción IF... THEN...ELSE. Por ejemplo, en lugar de:
Dim booSiNo As Boolean Dim x As Long If x = 13 Then booSiNo = True Else booSiNo = False End If MsgBox booSiNopodríamos usar:
Dim booSiNo As Boolean Dim x As Long booSiNo = (x = 13) MsgBox booSiNo
6. Hacer uso de las funciones estándar de Excel en VBA en lugar de contruir procesos que hagan lo mismo. Por ejemplo utilizar el siguiente código:
MiSuma=Application.WorksheetFunction.Sum(Range("B2:C10"))en lugar de:
For Each celda In Range("B2:C10") MiSuma = MiSuma + celda.Value Next celda
7. Especificar/concretar lo máximo posible a la hora de definir variables, evitando las variables tipo Variant o Object. Por ejemplo:
>Dim MiHoja As Worksheet 'mucho mejor que... Dim MiHoja As Object '''''' Dim filas As Long 'mucho mejor que... Dim filas As Variant
8. No seleccionar un rango para trabajar sobre él, si no es estrictamente necesario:
Range("C13").Font.Bold = True 'mucho mejor que... Range("C13").Select Selection.Font.Bold=True
9. Evitar Copiar y Pegar (Copy and Paste), empleando otras instrucciones similares, pero algo más eficientes. Por ejemplo, es mejor usar:
Range("A1:A13").Copy Destination:=Range("B1") 'O si únicamente necesitamos los valores: Range("B1:B13").Value= Range("A1:A13").Value
10. Emplear los índices para los elementos de las colecciones, en lugar de los nombres que definen estos elementos. Por ejemplo:
Worksheets("Hoja3") 'mejor usar Worksheets(3)Ojo, por que por contra, estos índices pueden cambiar, lo que generaría problemas de identificación futuros...no hay nada perfecto, verdad?.
11. Otro caso muy frecuente, el uso de "" en lugar de la variable vbNullString, mucho más efectiva.
Mejor usar
If Range("A1").Value = vbNullString Then '... Else '... End Ifen lugar de
If Range("A1").Value = "" Then '... Else '... End If
12. Otra forma muy útil cuando trabajamos con eventos sobre nuestras hojas o libros (o controles), desactivar los Events al inicio de nuestra programación y activarlos de nuevo al final:
Application.EnableEvents = False '... nuestro código VBA... Application.EnableEvents = True
13. Intentar reducir el número de líneas de código, empleando para ello la posibilidad de escribir en una misma línea usando el separador ':' (dos puntos). Por ejemplo:
With Selection .WrapText = True: .ShrinkToFit = False End With 'más rápido que... With Selection .WrapText = True .ShrinkToFit = False End With
14. Declarar las variables OLE directamente. Un ejemplo:
Dim xls As Excel.Application 'mejor que... Dim xls As Object Set xls = CreateObject("Excel.Application")
15. Y uno más, el último pero no el menos importante (quizá sea el más utilizado). Cuando se haga necesario emplear bucles del tipo FOR, es más óptimo emplear FOR EACH...NEXT que FOR...NEXT cuando recorremos colecciones 'indexadas'. Por ejemplo, es más rápido el primero que el segundo:
Dim wsHoja as Worksheet For Each wsHoja In Worksheets MsgBox wsHoja.Name Next wsHoja 'más rápido que el siguiente.. Dim i as Integer For i = 1 To Worksheets.Count MsgBox Worksheets(i).Name Next i
Por supuesto existen otras acciones que mejoran la eficiencia de nuestras macros, pero en algún punto hay que parar, y es que la optimización del VBA no tiene fin...
Hola Excelforo
ResponderEliminarMuy buena explicación de las distintas variantes para acelerar una Macro, muy Agradecido una vez mas por tus Exposiciones.
Un Abrazo
Lázaro.
Muchas gracias Lázaro!!
EliminarBuenas,
ResponderEliminarMuy completa explicación, yo utilizo bastantes de las 15 sin saberlo, pero además uso otra que es darle durante un cortisimo espacio de tiempo a la tecla escape mientras se está ejecutando, si la macro se para le doy a continuar y vuevo a intentarlo hasta que veo que se ha acelerado, haciendo esto he conseguido ejecutar una macro que tarda unos 40 segundos en menos de 5, ¿Sabes algo sobre esto, porqué ocurre, o como podría programarlo para no tener que estar intentandolo todas las veces?
Luis Miguel
Hola Luís,
Eliminarrealmente con la tecla Escape en algunos casos se detiene la ejecución de la macro (como indicas), pero no no entiendo como es posible que tal cosa acelere el procedimiento, ya que teóricamente debería estar detenido...
La única explicación que se me ocurre es que en los instantes de detención del proceso se libera memoria... la justa para dedicarlo a finalizar parte de la rutina ???!!!
No creo que tal cosa se pueda programar.
Slds cordiales
Hola, como puedo detectar el evento cuando cambian a pagina completa en excel?. Saludos cordiales
ResponderEliminarHola, me imagino quieres saber si se puede detectar si maximizamos o no.
EliminarQue yo sepa no existe un evento que actúe al maximizar, pero podemos detectar si está o no maximizada nuestra aplicación.
Si adjuntas este código en una hoja:
Private Sub Worksheet_Activate()
If Application.WindowState = xlMaximized Then
MsgBox "Maximizada"
'tu código
End If
End Sub
al entrar en ella (al activarla) se podrá ejecutar tu código.
Espero te pueda servir.
Slds
Muchas gracias por tu tiempo brindado.
EliminarSaludos cordiales,
Si te sirvió, entonces tiempo bien empleado.
EliminarSlds
Muchas gracias por esos muy buenos aportes que me sirven en las actividades que hago, saludos.
ResponderEliminar:-)
EliminarMil gracias!!
Muy interesante. Gracias por el aporte
ResponderEliminarHola, mi macro tiene funciones grabadas con excel que son relamente largas y que la verdad mi expertise en macro no me deja hacerlas en macros. Cuando la corro esta se demora mas de 10 minutos en correrse, usando la mayoría de estos codigos... alguna otra idea?
ResponderEliminarHola,
Eliminara veces no es un problema de las macros y sí del número de elementos a calcular... si las macros están optimizadas y el tiempo que necesitan es alto, el asunto será el número de cálculos que realiza.
Poco se puede hacer con esto... quizá Excel no sea la herramienta que necesitas
Saludos
Ismael, buenas tardes, agradecería tu ayuda con un pequeño problema. Soy algo nuevo empleando macros y estoy elaborando un kardex de productos en Excel -250 productos (hojas) hasta ahora-. Debido a la cantidad de hojas, las tengo ocultas y solo tengo visible la hoja resumen "Inventario". Probé con hipervínculos, pero no me sirve al requerir la hoja visible.
ResponderEliminarPor eso estaba configurando el poder visualizar cada hoja oculta mediante el código de hoja (selection_change). Así cuando doy clic a una celda específica dentro de la columna K (a partir de K8 hacia abajo) en “Inventario”, se muestra la hoja oculta correspondiente.
Este es el código que uso:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$K$8" Then
Worksheets("C-0001").Visible = True
Sheets("C-0001").Select
End If
If Target.Address = "$K$9" Then
Worksheets("C-0002").Visible = True
Sheets("C-0002").Select
End If
End Sub
El código me funciona a la perfección. Pero para aplicarlo a las 250 hojas, debería duplicarlo hasta llegar a la cantidad requerida y es un trabajo súper extenso…
¿Hay alguna macro o código que pueda aplicar para generar el enlace de cada celda a cada hoja sin tener que realizar los 250 códigos? Mi conocimiento no llega a más por ahora. Espero puedas ayudarme.
Gracias anteladas
Hola,
Eliminarimagina en el rango A1:A250 tienes un listado con los nombres de las 250 hojas:
C-0001
C-0002
...
en la ventana de código de esa hoja añade:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A250")) Is Nothing Then
Worksheets(Target.Value).Visible = True
Sheets(Target.Value).Select
End If
End Sub
Saludos
Gracias por tu rpta Ismael. Ya lo hice y funciona muy bien. Pero requiero que eso se realice a partir de K8.
EliminarDisculpando la insistencia ¿Hay alguna forma de modificar el código para realizar ese ajuste y que se ejecute de una columna difetente a "A"?.
Gracias por tu tiempo y apoyo.
Cuando cambio el rango ("A1:A250") por otra columna, me sale error 9 "subindice fuera de intervalo" y resaltado:
EliminarWorksheets(Target.Value).Visible = True
DA igual donde esté el rango.. pero se requiere que los valores de ese rango respondan exactamente a los distintos nombres de las hojas
EliminarSaludos
Tienes razón Ismael, ya me di cuenta de mi error, la columna tenia una descripción diferente a los nombres de las hojas.
ResponderEliminarMuchas gracias! Me has ayudado un montón. Que tengas un buen fin de semana.
Tengo una macro que es muy rápida y tiene muchas de las recomendaciones para agilizar ya implementadas. La macro abre otro archivo de excel para leer datos y los trae al archivo donde está la macro, el cual es binario (xlsb), además le tengo inhabilitado el cálculo automático durante la macro y también bloqueada la actualización de la pantalla. Ambos archivos son grandes y tienen mucha formulación avanzada en sus celdas, tablas dinámicas y gráficas.
ResponderEliminarEl problema es que por una razón que no comprendo aún, si pongo a correr la macro desde la hoja de cálculo usando un botón que la llama, el proceso se demora muchísimo más, que cuando voy al editor de visual basic y la pongo a correr directamente desde allí. También demora menos si la pongo a correr desde la hoja de cálculo pero colocando en algún lugar del código un punto de interrupción, de modo que al llegar a dicho punto la macro para, pero al presionar la tecla "F5" para que la macro continué normalmente, termina muy rápido en comparación con hacerlo sin el punto de interrupción.
Es como si la macro funcionara más rápido cuando se corre directamente desde el editor de visual basic que cuando se llama desde la hoja de calculo con un botón.
A alguien le ha pasado algo similar?
Hay alguna manera de hacer que la macro corra sobre el editor de visual para que sea más rápida, tal vez una función visual que haga eso?
Hola,
Eliminarno debería haber diferencias entre el lugar de inicio de la ejecución (hoja o editor de VB).
Quizá el problema esté donde esté la celda activa en el momento de ejecutar la macro¿¿??
Peor quedamos a la espera de otros comentarios...
Saludos
Hola, si tiene razón sobre la celda activa. La demora se dá cuando la hoja activa es la misma hoja donde la macro está escribiendo los datos. ejecuté la macro en otra hoja que la macro no toca y hay si no hay demoras
Eliminar;-)
EliminarSlds
Hola, ismael, estoy creando una plantilla para el trabajo y tarda mucho aunque depende de si relleno los textbox o no. Me explico, cree las celdas en la hoja de esta manera: A1 contiene la etiqueta y B1 contendra el texto del textbox1 cuando escriba en la plantilla y asi unas cuantas celdas luego las concatene en una sola es decir que yo entro al excel y directamente me muestra la plantilla en macro, despues relleno los campos y al pulsar el boton guardar se ejecuta de la siguiente manera: boton guardar>escribe el texto de los textbox en las celdas>la celda combinada con la funcion concatenar recoge la info de todas las celdas en el orden que le di>un textbox multi linea recoge la info de la celda combinada y me muestra en otro userform el texto completo> de ahi pulso el boton copiar y lo copia al portapapeles de manera que asi puedo pegarlo en el diario de trabajo de otr app. Sin necesidad de tocar el excel para nada. Mi problema es cuando relleno todos los textbox con datos y test tecnicos largos, al pulsar el boton guardar y ejecutar la cadena de acciones que cito arroba, pasa de tardar(sin texto) 5 segundos a tardar(con texto) entre 40 segundos y 1 minuto. Como cada textbox esvribe en una celda, las funciones estan una por una asi: Range("A1").Value = TextBox1 luego TextBox1.Value = "" y asi por unas 50 celdas.. se que se puede mejorar pero no se como. Agradeceria una mano. Grcias de antemano
ResponderEliminarHola Valen,
Eliminarhabría que ver el código en su contexto, pero no parece que se pueda hacer mucho..
Quizá evitar usar el Userform y rellenar directamente las celdas en la hoja de cálculo.. no parece que aporte mucho al proceso trabajar desde el formulario..
Saludos
hola
ResponderEliminarhice una macro con la grabadora que me actualiza varias tablas con datos de internet.
quisiera porner un codigo para que limite el tiempo de ejecucion de la actualizacion a 6 segundos por cadena de texto, y si pasa ese tiempo que vuelva a ejecutar ese mismo codigo y en caso de volver a pasar 6 segundos que siga con la siguiente linea de codigo.
gracias
Hola,
Eliminarquizá empleando el método OnTime puedas lograrlo:
http://excelforo.blogspot.com.es/2014/02/vba-repetir-una-macro-cada-x-tiempo.html
Aunque dudo tal cosas sea posible, ya que hasta donde yo se, las actualizaciones son complicadas de detener una vez comenzadas
Saludos
te copio el codigo porque no sé como hacer para que se ejecute cada linea con limite de tiempo
EliminarSub actualizar()
Application.Calculation = xlManual
ActiveWorkbook.Connections("tabla1").Refresh
ActiveWorkbook.Connections("tabla2").Refresh
ActiveWorkbook.Connections("tabla3").Refresh
ActiveWorkbook.Connections("tabla4").Refresh
Application.Calculation = xlautomatic
end sub
la idea es que cada actualizacion se ejecute 6 segundos y si pasa ese tiempo volver a ejecutar la misma linea que se tardó ese tiempo y si vuelve a demorarse pasar a la siguiente repetir lo mismo que la anterior pero que nunca vuelva al inicio del proceso si no que siga con las restantes tablas haciendo lo mismo, hasta que yo decida volver a ejecutar esa macro de nuevo.
Manualmente me sucede que se cuelga la actualizacion pero la suspendo con el boton ESC, la vuelvo a ejecutar y se actualiza bien, o sea que la segunda vez que actualizo termina resultando bien. Por eso queria automatizar el procedimiento de esa manera, no toma mas de 6 u 8 segundos la segunda actualizacion.
gracias
No creo sea posible... yo al menos desconozco el cómo
EliminarLo siento
Un saludo y suerte
Hola Silvestre,
ResponderEliminaryo emplearía la herramienta del sistema: Quitar duplicados
te hará lo mismo en décimas de segundo y de manera más segura,... y sin necesidad de que los datos estén ordenados por la columna B
Saludos
Así es estimado Ismael, toda la razón. Es mejor primero ordenar los datos (paso fundamental), y luego aplicar el Quitar duplicados propio de excel.
ResponderEliminarSaludos.
Veo que este tema ya tiene años, pero mi problema es vigente… Gracias Ismael por compartir grandes conocimientos…
ResponderEliminarResulta que tengo un código que hace un llamado de datos de Excel a Access, el problema es que en algunas computadoras funciona super bien y en otras no… y no comprendo porque todas son dell optiplex 990, core i5, con 4 GB de memoria, office 2010, Windows 10, etc.
En las que no funcionaba modifiqué el código y mejoró a medias, porque uno hace las primeras 7 consultas, y la respuesta es increíble, ya la consulta 8 se queda una eternidad, que puede estar pasando?
Antes de la modificación desde la primera consulta que quedaba trabado y no respondía… que será lo que pasa, porque en unas funciona de maravilla y en otras se queda trabada???
La hoja Excel esta en la estación de cada usuario, el archivo de Access se encuentra en un servidor y allí está direccionado la consulta.
Gracias por la atención… saludos cordiales.
Hola!
EliminarEs raro que se 'enganche' en la 8ª consulta y no en las anteriores...
quizá algo en las condiciones de esa consulta esté pasando algo raro con los datos en sí
Si vas liberando memoria según pasan las consultas, no se me ocurre otra opción...
Saludos y siento no poder decirte algo más
hola Ismael, tengo una consulta si me pudieras ayudar por favor:
ResponderEliminarResulta que quiero poner la variable i, que representa la última celda con datos, dentro de una fórmula, pero me da error de sintaxis:
Range("D1").FormulaLocal = "=SUMAR.SI(Base!D3:D" & i & ";Nómina_prev!A1;Base!O3:O19)"
Creo que estoy concatenando la i de mala manera, pero nosé donde se produce el problema.
Gracias de antemano, un abrazo.
Funcionó Ismael, al dar espacio en blanco entre cada ampersand y entre cada comilla doble.
EliminarDisculpa, pero lo pude descubrir después de haberte planteado la consulta. Un abrazo!!!
Gracias a tí,
Eliminara veces un sencillo espacio en blanco tiene esta importancia en programación
Saludos