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