Cómo optimizar la velocidad de nuestras macros en Excel (3 de 5)

Vamos a abordar en este tercer artículo de la serie, otras 5 formas de optimizar nuestras macros en VBA para Excel.

 

1) Declarar variables con tipos de datos concretos

Al declarar una variable, debemos declarar las variables asignándole el tipo de dato que contendrá. Es decir, si necesito una variable llamada i para un bucle, debo asignarle un tipo de dato a la declaración. En este caso sería una variable con números enteros:

Sub Variable_i ()

   Dim i As Integer

   For i = 1 To 5

   ... mi código...

   Next

End Sub

Si hubiéramos declarado la variable tan solo escribiendo  Dim i    o si no la hemos declarado, VBA asignará por defecto el tipo de variable Variant, para la que será necesario reservar y utilizar 16 bytes en la memoria de nuestro equipo. En su lugar, agregando   As Integer  a la declaración, se reservarán y utilizarán tan solo 2 bytes en memoria. Esta utilización, repetida muchas veces (como puede ser el caso de un bucle) hará que nuestra macro pueda ejecutarse más lenta o más rápida, dependiendo de los bytes reservados en memoria para la variable.

Cuidado con declarar varias variables a la vez de esta forma...

Dim i, f, t, x as Integer

... puesto que así, solo hemos asignado un tipo de datos a la última variable de la lista. Para aprender más sobre cómo declarar variables, haga clic en el siguiente enlace: Declaración de variables en VBA para Excel (I)

2) Utilizar "Select Case" en vez de "IF... Then..."

En vez del código:

Sub MiMacro ()
   IF Range("A1") = 100 Then
       Range("A2") = 200
   End IF
   IF Range("A1") = 200 Then
       Range("A3") = 400
   End IF 
   IF Range("A1") = 300 Then
       Range("A4") = 600
   End IF

End Sub

Es más óptimo utilizar el siguiente código:

Sub MiMacro ()
   Dim MiValor As Integer
   MiValor = Range("A1")

   Select Case MiValor
      Case 100
         Range("A2") = 200
      Case 200
         Range("A3") = 400
      Case 300
         Range("A4") = 600
   End Select

End Sub

El motivo es que la instrucción Select Case solo evalúa hasta el caso en el que es verdadero y después termina de evaluar. Sin embargo, con IF...THEN... , VBA debe entrar a valorar todas las expresiones. No debería hacer falta decir que es conveniente situar en primera posición la opción que tiene más posibilidades de cumplirse.

3) Limpiar las variables objeto al final de cada macro

Si utilizamos variables de tipo objeto es conveniente "limpiarlas" al final de la macro asignándoles un valor "Nothing". Esto se puede ver aquí:

Sub MiMacro ()
   Dim MiRango As Range
   Set MiRango = Range("A1")
    ..... mi código ....
   Set MiRango = Nothing

End Sub

4) Escribir macros que están relacionadas entre si, en el mismo módulo

Si tenemos una macro que nos remite a otra y esta a su vez remite a otra.... sería conveniente que todas las macros que van a ejecutarse de forma sucesiva estuvieran en el mismo módulo. De esta forma, VBA no tendría que cargar en memoria varios módulos durante la ejecución.

De hecho, también es conveniente que durante el trabajo cotidiano con nuestra aplicación en Excel no permanezcan abiertos módulos del editor de Visual Basic. Por supuesto, también es mejor que ni siquiera el editor de VBA esté abierto.

5) Precalcular con variables todo lo que esté dentro de un bucle

En vez del siguiente código...

Sub MiMacro ()

   Dim i As Integer

   For i = 1 To 5000

     Cells (i,3) = Range("B7")*459

   Next

End Sub

...utilizar el siguiente código...

Sub MiMacro ()

   Dim i As Integer

   Dim Operacion as Double

   Operacion = Range("B7")*459

   For i = 1 To 5000

     Cells (i,3) = Operacion

   Next

End Sub

... en el que podemos ver que la operación que hay dentro del bucle la calculamos con anterioridad y depositamos el valor en una variable (Operacion). De esta forma, VBA no tiene que realizar 5000 veces el cálculo, sino que solo tiene que transferir el resultado de la memoria a la celda Cells(i,3).

José Manuel Pomares Medrano