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

Es turno del 4º capítulo de esta serie de 5 que iniciamos hace unos meses. En este caso traemos otras 3 formas de optimizar nuestras macros, sobre todo en cuanto a eficiencia en velocidad.

1) Rellenar rangos de celdas con datos modificados de otros rangos (utilización de bucles, arrays y variables de valor)

Imaginemos que necesitamos rellenar las celdas de un rango determinado con los datos que hay en otro rango. Esto, en principio, se podría hacer simplemente copiando y pegando el rango original en el rango destino. Incluso se podría hacer (si no necesitamos que se copien los formatos y los rangos tienen el mismo tamaño) igualando los rangos con el simple código RangoDestino = RangoOrigen. Ya vimos estos ejemplos en el punto nº 3 del post Cómo optimizar la velocidad de nuestras macros en Excel (4 de 5).

Pero... ¿qué ocurre si los datos que deben ser rellenados en el rango destino tienen que sufrir alguna operación matemática o cualquier otra modificación, respecto de los datos originales?. En este caso, todo cambia, puesto que la modificación de cada dato dentro de un rango es un problema a solucionar y se puede hacer de varias formas. Algunas más rápidas que otras.

Veamos (con varias formas de resolución) un ejemplo de utilidad que copia los valores contenidos en un rango (desde H1 hasta H5000) y los traslada a otro rango (desde A1 hasta A5000) ... pero... cambiando cada valor multiplicándolo por si mismo

MEDIANTE UN BUCLE FOR... NEXT

Sub RellenarConBucle()
'SE DECLARA LA VARIABLE DEL CONTADOR
    Dim i As Integer
'SE COLOCA EN CADA CELDA-DESTINO EL VALOR DE CADA CELDA-ORIGEN, SEGÚN LA OPERACIÓN DESEADA
    For i = 1 To 5000
       CeldaActualOrigen = Cells(i, 8)
       Cells(i, 1) = CeldaActualOrigen * CeldaActualOrigen
    Next i
End Sub

Este método es el más lento de todos, puesto que el tiempo de ejecución (en mi ordenador) es de unos 0,300 sg. El motivo de este lapso de tiempo relativamente largo es que el código actúa directamente sobre celdas de Excel durante el bucle... y no sobre variables en memoria RAM. La ventaja es que hay menos código y más sencillo.

MEDIANTE UN ARRAY (arreglo o matriz)

Sub RellenarConArray()
'SE DECLARA LA VARIABLE PARA EL BUCLE, UN ARRAY Y LA VARIABLE OBJETO
    Dim i As Integer
    Dim miArray(5000, 1)
    Dim miRango As Range
    Set miRango = Range("H1").CurrentRegion
'SE COLOCAN, UNO A UNO Y SEGÚN LA OPERACIÓN DESEADA, LOS DATOS DEL RANGO EN UN ARRAY
    For i = 1 To 5000
        miArray(i, 1) = miRango(i, 1)
        miArray(i, 1) = miArray(i, 1) * miArray(i, 1)
    Next i
'SE TRASPASAN DE UNA VEZ LOS DATOS DESDE EL ARRAY HASTA EL RANGO
    Range("A1:A5000") = miArray
End Sub

Este método es el más formal y para algunos casos podría tener alguna ventaja, pero su ejecución es de unos 0,023 sg. (unas 13 veces más rápido que el método del bucle).

MEDIANTE UNA VARIABLE DE VALOR

Sub RellenarConVariable()
'SE DECLARAN LAS VARIABLES DEL CONTADOR Y RANGO ORIGEN (ESTA ÚLTIMA COMO VARIABLE DE VALOR, TIPO VARIANT)
    Dim i As Long
    Dim miRango As Variant
'SE COLOCAN DE UNA VEZ LOS DATOS DEL RANGO-ORIGEN EN UNA VARIABLE DE VALOR (NO DE OBJETO)
    miRango = Range("H1:H5000").Value
'SE MODIFICAN, SEGÚN LA OPERACIÓN DESEADA, LOS DATOS QUE ESTÁN EN LA VARIABLE DE VALOR (ESTARÁN EN FORMA MATRICIAL)
    For i = 1 To 5000
        miRango(i, 1) = miRango(i, 1) * miRango(i, 1)
    Next i 
'SE TRASPASAN DE UNA VEZ LOS DATOS DESDE LA VARIABLE HASTA EL RANGO-DESTINO
    Range("A1:A5000") = miRango
End Sub

Este método es el más rápido (unos 0,012 sg. en mi equipo. Es decir, casi 30 veces más rápido que el sistema que utiliza solo un bucle For Next y prácticamente el doble de rápido que el sistema que aboga por utilizar un Array), puesto que no se manejan Arrays ni dimensiones y en su lugar lo que se hace es:

1. Colocar en una variable de valor declarada como tipo "Variant" (NO una variable de objeto) el rango de datos original. El resultado es que los datos se colocan en forma de matriz en la variable y el resultado es similar a la utilización de un Array (se puede hacer referencia a ellos mediante el índice de cada dimensión).
2.  Una vez que los datos están en una variable (en la RAM) su modificación es mucho más rápida, por lo tanto es en este momento cuando nos disponemos a multiplicar por si mismo cada valor.
3. Se traspasan, de una vez, todos los datos de la variable-matriz hasta el rango-destino.

2) Utilizar, cuando sea posible, el bucle For Each...Next para recorrer colecciones, en vez del bucle For...Next

El siguiente bucle (que rellena con un texto un rango de 500.000 celdas)...


Sub RecorrerConIndice()'SE DECLARAN LAS VARIABLES DEL ÍNDICE Y DEL RANGO A RELLENAR
    Dim i As Double
    Dim miRango As Range
    Set miRango = Range("A1:A500000")    'SE RECORRE EL RANGO MEDIANTE EL ÍNDICE DE CADA CELDA
     For i = 1 To miRango.Count
        miRango(i, 1) = "Prueba 1"
    Next i
End Sub

... es casi un 4% más lento que el siguiente bucle...


Sub RecorrerColeccion()'SE DECLARAN LA VARIABLES PARA CADA CELDA DEL RANGO Y LA VARIABLE DEL RANGO A RELLENAR
    Dim miCelda As Range
    Dim miRango As Range
    Set miRango = Range("A1:A500000")'SE RECORRE LA COLECCIÓN DE CELDA EN CELDA ASIGNANDO VALOR
     For Each miCelda In miRango
       miCelda = "Prueba 2"
    Next miCelda
End Sub

3) Utilizar el método SpecialCells para hacer referencia a celdas visibles o con una característica y/o valor concreto

Supongamos que tenemos un rango de filas filtradas (o con algunas de ellas ocultas) y necesitamos actuar sola y precisamente sobre las celdas que quedan visibles. Aunque este objetivo se puede conseguir con otros métodos distintos a la utilización del método SpecialCells (por ejemplo con Tablas de Excel), no siempre tendremos los datos en un formato así. Además, el método SpecialCells realiza otras acciones con otras constantes que presentaremos al final.

Veamos esta macro:

Sub SpecialCells()
'SE DECLARA UNA VARIABLE PARA EL RANGO A TRATAR
    Dim miRango As Range
    Set miRango = Range("A1:A10")
'SE COPIA AL PORTAPAPELES EL CONTENIDO DE LAS CELDAS QUE ESTÁN VISIBLES (EN UN RANGO FILTRADO, POR EJEMPLO)
    miRango.SpecialCells(xlCellTypeVisible).Copy
'SE PEGA EL CONTENIDO DE LAS CELDAS COPIADAS EN OTRO RANGO
    Range("C15").PasteSpecial xlPasteValues
End Sub

Como ya se ha comentado, lo que realiza este procedimiento es copiar el contenido de las celdas que quedan visibles en un rango que tiene aplicado un filtro (o que tiene algunas filas ocultas) y después pega el contenido en otro rango. Esto se hace gracias al método:

expresión.SpecialCells(Type, Value)

... que nos devuelve un rango de celdas que tienen unas características y un tipo valor determinado. Como podemos intuir, los argumentos Type y Valor (el segundo es opcional) sirven para determinar la característica de celda que queremos y el tipo de valor que contiene, respectivamente. Las constantes de las que disponemos para el primer argumento son:

Solo cuando el primer argumento es xlCellTypeConstants   o   xlCellTypeFormulas podemos utilizar el segundo argumento, que tiene las siguientes posibilidades:

Por ejemplo, si necesitamos seleccionar todas las celdas de un rango que contengan valores de texto, podemos utilizar la siguiente macro:

Sub SpecialCells()
'SE DECLARA UNA VARIABLE PARA EL RANGO A TRATAR
    Dim miRango As Range
    Set miRango = Range("A1:A10")
'SE SELECCIONAN LAS CELDAS QUE TIENEN CONSTANTES (NO FÓRMULAS) Y CUYO VALOR ES DE TIPO TEXTO (xlTextValues)
    miRango.SpecialCells(xlCellTypeConstants, xlTextValues).Select
End Sub



José Manuel Pomares Medrano