miércoles, 5 de diciembre de 2012

VBA: Maneras de acelerar nuestras macros de Excel.

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:

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 booSiNo

podrí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 If

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

37 comentarios:

  1. Hola Excelforo
    Muy buena explicación de las distintas variantes para acelerar una Macro, muy Agradecido una vez mas por tus Exposiciones.
    Un Abrazo
    Lázaro.

    ResponderEliminar
  2. Buenas,

    Muy 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

    ResponderEliminar
    Respuestas
    1. Hola Luís,
      realmente 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

      Eliminar
  3. Hola, como puedo detectar el evento cuando cambian a pagina completa en excel?. Saludos cordiales

    ResponderEliminar
    Respuestas
    1. Hola, me imagino quieres saber si se puede detectar si maximizamos o no.

      Que 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

      Eliminar
    2. Muchas gracias por tu tiempo brindado.

      Saludos cordiales,

      Eliminar
    3. Si te sirvió, entonces tiempo bien empleado.
      Slds

      Eliminar
  4. Muchas gracias por esos muy buenos aportes que me sirven en las actividades que hago, saludos.

    ResponderEliminar
  5. Muy interesante. Gracias por el aporte

    ResponderEliminar
  6. Hola, 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?

    ResponderEliminar
    Respuestas
    1. Hola,
      a 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

      Eliminar
  7. 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.
    Por 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

    ResponderEliminar
    Respuestas
    1. Hola,
      imagina 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

      Eliminar
    2. Gracias por tu rpta Ismael. Ya lo hice y funciona muy bien. Pero requiero que eso se realice a partir de K8.
      Disculpando 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.

      Eliminar
    3. Cuando cambio el rango ("A1:A250") por otra columna, me sale error 9 "subindice fuera de intervalo" y resaltado:

      Worksheets(Target.Value).Visible = True

      Eliminar
    4. DA igual donde esté el rango.. pero se requiere que los valores de ese rango respondan exactamente a los distintos nombres de las hojas
      Saludos

      Eliminar
  8. Tienes razón Ismael, ya me di cuenta de mi error, la columna tenia una descripción diferente a los nombres de las hojas.

    Muchas gracias! Me has ayudado un montón. Que tengas un buen fin de semana.

    ResponderEliminar
  9. 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.
    El 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?

    ResponderEliminar
    Respuestas
    1. Hola,
      no 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

      Eliminar
    2. 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
  10. 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

    ResponderEliminar
    Respuestas
    1. Hola Valen,
      habrí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

      Eliminar
  11. hola
    hice 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

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá 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

      Eliminar
    2. te copio el codigo porque no sé como hacer para que se ejecute cada linea con limite de tiempo

      Sub 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

      Eliminar
    3. No creo sea posible... yo al menos desconozco el cómo
      Lo siento

      Un saludo y suerte

      Eliminar
  12. Hola tengo una macro para elimina registros duplicados!! y uso el siguiente código:

    Sub EliminarRepetidos()

    Dim Row As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    Row = 1
    While ws.Cells(Row, 2).Value <> ""
    If ws.Cells(Row, 2).Value = ws.Cells(Row + 1, 2).Value Then
    Range("B" & (Row + 1)).EntireRow.Delete
    Else
    Row = Row + 1
    End If
    Wend

    End Sub


    Hice pruebas con 1000 registros y ejecutó bien la macro pero ahora lo ejecuto con 434 104 registros y ya lleva 40 min y no acaba, es normal que se tarde mucho? o alguna forma de optimizar la ejecución del macro?

    ResponderEliminar
    Respuestas
    1. Hola Silvestre,
      yo 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

      Eliminar
    2. 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.
      Saludos.

      Eliminar
  13. Veo que este tema ya tiene años, pero mi problema es vigente… Gracias Ismael por compartir grandes conocimientos…

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

    ResponderEliminar
    Respuestas
    1. Hola!
      Es 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

      Eliminar
  14. hola Ismael, tengo una consulta si me pudieras ayudar por favor:
    Resulta 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.

    ResponderEliminar
    Respuestas
    1. Funcionó Ismael, al dar espacio en blanco entre cada ampersand y entre cada comilla doble.
      Disculpa, pero lo pude descubrir después de haberte planteado la consulta. Un abrazo!!!

      Eliminar
    2. Gracias a tí,
      a veces un sencillo espacio en blanco tiene esta importancia en programación
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.