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

Contiuamos con nuestra serie de artículos dedicados a la optimización de código Visual Basic for Applications para Excel. En este caso, serán también cinco formas que hacer que nuestras macros funcionen de forma mas fluida y óptima.

1) Utilizar bloques With para acortar el código

Si partimos del primer artículo de esta serie, en él podemos ver que al final habíamos reunido una buena serie de líneas de código que optimizaban la velocidad de ejecución de una macro. Esas líneas eran:

Sub MiMacro ()

   Application.ScreenUpdating = False

   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False 

   Application.DisplayStatusBar = False

   ....mi código

   Application.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   ActiveSheet.DsiplayPageBreaks = False
End sub ()

Podemos apreciar que de las siete líneas de código (siete, sin contar los protocolos de entrada y salida), seis comienzan con Application . Esto quiere decir que si utilizamos una forma de resumir todas esas palabras que se repiten, Visual Basic no tendrá que leerlas todas y se ejecutará más rápido. Esta forma es la siguiente:

Sub MiMacro ()

  
   With Application
      .ScreenUpdating = False

      .Calculation = xlCalculationManual
      .EnableEvents = False 

      .DisplayStatusBar = False

   ....mi código

      .Calculation = xlCalculationAutomatic
      .EnableEvents = True
      ActiveSheet.DsiplayPageBreaks = False
   En With

End sub ()

Donde podemos ver la estructura With.... End With que simplifica el código en buena medida. Imaginémonos un código donde lo que se repite es una cadena tan larga como...

Worksheets("Sheet1").Rectangles(1).Interior.Color = RGB(255, 255, 255)

Worksheets("Sheet1").Rectangles(1).Interior.Pattern = xlChecker

Worksheets("Sheet1").Rectangles(1).Interior.PatternColorIndex = 5

En este último caso, la optimización podría ser aún más espectacular:

With Worksheets("Sheet1").Rectangles(1).Interior

   .Color = RGB(255, 255, 255)

   .Pattern = xlChecker

   .PatternColorIndex = 5

End With

2) Evitar utilizar la propiedad Select (típica de la grabadora de macros)

La grabadora de macros es una herramienta estupenda para iniciarnos con el código VBA, pero hay que entender que es imposible que genere un código excelente, sobre todo si pensamos en que se limita a traducir acciones de la interfaz de usuario a código.

Una de las señas de identidad de la grabadora de macros es la utilización de la propiedad Select aplicada normalmente a rangos de celdas y otros objetos. Esta utilización repetida de Select es debidio a que la grabadora reproduce nuestras acciones.... y como usuarios, no tenemos más remedio que seleccionar todo para poder hacer algo con la celda, gráfico, imagen, etc. que queramos. Lo que ocurre es que, seleccionar todo aquello sobre lo que queremos actuar no es algo obligatorio en VBA. Al contrario, por medio de código podemos aplicar métodos y propiedades sobre los objetos sin la necesidad de seleccionarlos previamente.

El siguiente es un trozo de código típico de una macro grabada:

    Range("B2").Select
    Selection.ClearContents

...que podría optimizarse así:

    Range("B2").ClearContent

Hemos ahorrado una línea de código evitando decir a VBA que seleccione la celda y que después, con la selección efectuada, limpie (borre) el contenido. Esta situación la encontraremos en muchos casos. Siempre que veamos una combinación Select y después Selection, podemos hacer esta "reducción".

3) Copiar-pegar adecuadamente y cuando sea estrictamente necesario

Algunas de las acciones más utilizadas en macros son, precisamente, "Copiar" y "Pegar". Sobre ellas tenemos que hacer una distinción:

1) SI NO NECESITAMOS COPIAR Y PEGAR LOS FORMATOS Y FÓRMULAS DE LAS CELDAS:

En este caso, en vez de copiar y pegar valores, es más óptimo utilizar simplemente el signo igual. Por ejemplo, en vez de esta macro grabada...

Range("B2").Selec

Selection.Copy

Range("D2").Select

ActiveSheet.Paste

...utilizaremos este código escrito...

Range("D2") = Range("B2")

Como podemos imaginar, este código es muuuuucho más rápido (lo notaremos especialmente si esta operación se repite muchas veces en nuestra macro) y también mucho más claro a la hora de entenderlo.

También es posible copiar y pegar rangos que tengan más de una celda. Por ejemplo:

Range("D2:E4") = Range("B2:C4")

Aunque... POR SUPUESTO... los rangos deben tener un mismo tamaño.

Tampoco debemos olvidar que si el copiado - pegado se debe hacer en hojas distintas a la que está activa en el momento de ejecutar el código debemos hacer una referencia más completa a ellas. Por ejemplo:

Sheets("Hoja2").Range("D2:E4") = Sheets("Hoja3").Range("B2:C4")

El código de arriba se podría ejecutar teniendo activa cualquier hoja en el momento de ejecución del código.

2) SI NECESITAMOS COPIAR Y PEGAR LOS FORMATOS Y FÓRMULAS DE LAS CELDAS:

En vez del código...

Range("B2").Selec

Selection.Copy

Range("D2").Select

ActiveSheet.Paste

...utilizaremos este código escrito...

Range("B2").Copy Destination:= Range("D2")

Como podemos ver, además de prescindir de la propiedad Select, también hemos aprovechado el parámetro Destination del método Copy. Con una sola línea tenemos un código equivalente a cuatro líneas. Eso sí, con esta forma de proceder no tenemos más remedio que pegar todo lo copiado y no podemos discriminar solo valores (por ejemplo).

También debemos recordar que si el rango de origen o el rango de destino no están en la hoja que VBA considera como activa en el momento de ejecutar el código, debemos hacer una referencia más completa a los rangos. Por ejemplo:

Sheets("Hoja2").Range("B2").Copy Destination:= Sheets("Hoja3").Range("D2")

4) Limpiar el portapapeles después de copiar

Si finalmente hemos tenido que utilizar el método Copy en nuestro código, sería conveniete que al final de la macro insertáramos la línea...

Application.CutCopyMode = False

... de esta forma limpiamos la memoria RAM ocupada por lo que se ha copiado. Con los métodos explicado en el apartado 3) de este artículo, Excel no utiliza el portapapeles como lugar de intercambio o "buffer", pero si hemos utilizado Copy, sí es conveniente vaciar el portapapeles, puesto que sí se ha utilizado.

5) Utilizar la función Lenb( ) o la constante vbNullString en vez de "" cuando nos referimos a celdas vacías

Cuando queremos provocar que una celda esté vacía. Lo podemos hacer mediante...

 Range("B2") = ""  (por ejemplo)

... pero hay una forma más rápida:

Range("B2") = vbNullString 

El motivo es que VBA procesa más rápido su constante vbNullString que la cadena vacía "". En otras palabras, con vbNullString, VBA considera que no existe ninguna cadena de texto, sin embargo, con "" considera que existe una cadena de texto vacía (no es lo mismo tener una caja vacía... que no tener ni siquiera la caja).

PARA COMPROBACIONES DE CADENAS VACÍAS:

Otro ejemplo de referencia a cadenas vacías es cuando queremos comparar. Por ejemplo, el siguiente código...

If Range("B2") <> ""

...es más lento que ....

If LenB(Range("B2")) <> 0

El motivo es que VBA procesa y compara más rápidamente solo el resultado de la función LenB (LARGOB) que toda la cadena de texto que pudiera haber en Range("B2").

La función de VBA LenB (LARGOB como equivalente en funciones Excel) es la "hermana" de la función de VBA Len (LARGO como equivalente en funciones Excel). Mientras que la primera devuelve el número de caracteres de una cadena de texto, la segunda devuelve el número de bytes usados para representarlos.

Por cierto, es más rápida la comparación <> (distinto que...) que la comparación > (mayor que...). El motivo es que en el primer caso, para VBA es más sencillo detectar si un valor es sencillamente distinto que otro, que calcular si un valor es mayor o menor que otro. Por lo tanto, si no es imprescindible comparar el valor, sino que es suficiente con saber si es o no igual, utilizaremos la comparación <>.

José Manuel Pomares Medrano