VBA: Maneras de acelerar nuestras macros de Excel.

Publicado: 10 de julio de 2013 en Actualidad

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:

view plainprint?

  1. Application.ScreenUpdating=False
  2. ‘… nuestro código VBA…
  3. Application.ScreenUpdating=True

2. Emplear la propiedad .Calculation para evitar el recálculo de las operaciones de nuestra hoja:

view plainprint?

  1. Application.Calculation=xlCalculationManual
  2. ‘… nuestro código VBA…
  3. Application.Calculation=xlCalculationAutomatic
  4. ‘o bien antes de cerrar el libro
  5. Application.CalculateBeforeSave=True

3. Cuando sea posible usar la instrucción WITH…END WITH:

view plainprint?

  1. With MyLabel
  2. .Height = 2000
  3. .Width = 2000
  4. .Caption = "Esta es mi etiqueta"
  5. End With

4. Liberar memoria cuando definamos variables con objeto:

view plainprint?

  1. Dim wsHoja as Worksheet
  2. ‘Definimos la variable objeto
  3. Set wsHoja = Hoja1
  4. ‘… nuestro código VBA…
  5. ‘Liberamos la memoria
  6. 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:

view plainprint?

  1. Dim booSiNo As Boolean
  2. Dim x As Long
  3. If x = 13 Then
  4. booSiNo = True
  5. Else
  6. booSiNo = False
  7. End If
  8. MsgBox booSiNo

podríamos usar:

view plainprint?

  1. Dim booSiNo As Boolean
  2. Dim x As Long
  3. booSiNo = (x = 13)
  4. 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:

view plainprint?

  1. MiSuma=Application.WorksheetFunction.Sum(Range("B2:C10"))

en lugar de:

view plainprint?

  1. For Each celda In Range("B2:C10")
  2. MiSuma = MiSuma + celda.Value
  3. Next celda

7. Especificar/concretar lo máximo posible a la hora de definir variables, evitando las variables tipo Variant o Object. Por ejemplo:

view plainprint?

  1. Dim MiHoja As Worksheet
  2. ‘mucho mejor que…
  3. Dim MiHoja As Object
  4. ”””
  5. Dim filas As Long
  6. ‘mucho mejor que…
  7. Dim filas As Variant

8. No seleccionar un rango para trabajar sobre él, si no es estrictamente necesario:

view plainprint?

  1. Range("C13").Font.Bold = True
  2. ‘mucho mejor que…
  3. Range("C13").Select
  4. 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:

view plainprint?

  1. Range("A1:A13").Copy Destination:=Range("B1")
  2. ‘O si únicamente necesitamos los valores:
  3. 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:

view plainprint?

  1. Worksheets("Hoja3")
  2. ‘mejor usar
  3. 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

view plainprint?

  1. If Range("A1").Value = vbNullString Then
  2. ‘…
  3. Else
  4. ‘…
  5. End If

en lugar de

view plainprint?

  1. If Range("A1").Value = "" Then
  2. ‘…
  3. Else
  4. ‘…
  5. 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:

view plainprint?

  1. Application.EnableEvents = False
  2. ‘… nuestro código VBA…
  3. 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:

view plainprint?

  1. With Selection
  2. .WrapText = True: .ShrinkToFit = False
  3. End With
  4. ‘más rápido que…
  5. With Selection
  6. .WrapText = True
  7. .ShrinkToFit = False
  8. End With

14. Declarar las variables OLE directamente. Un ejemplo:

view plainprint?

  1. Dim xls As Excel.Application
  2. ‘mejor que…
  3. Dim xls As Object
  4. 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:

view plainprint?

  1. Dim wsHoja as Worksheet
  2. For Each wsHoja In Worksheets
  3. MsgBox wsHoja.Name
  4. Next wsHoja
  5. ‘más rápido que el siguiente..
  6. Dim i as Integer
  7. For i = 1 To Worksheets.Count
  8. MsgBox Worksheets(i).Name
  9. 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…

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s