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

19 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