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

Finalizamos con este 5º post nuestra serie de artículos "Cómo optimizar la velocidad de nuestras macros en Excel". Lo dedicaremos a 3 trucos que optimizarán nuestro código hacia otras formas más óptimas de VBA, ya sea por la velocidad o por la claridad en el código.

1) Seleccionar la última celda que contiene datos en un rango

El título de este primer punto hace referencia a una necesidad con la que nos solemos encontrar cuando escribimos código VBA. Esta necesidad la podemos cubrir de varias formas... pero unas son más óptimas que otras en cuanto a velocidad.

Por ejemplo, el siguiente código cumple con la función de encontrar la última celda que contiene datos en una lista que comienza en A1:

Sub UltimaCeldaConDatosConBucleFor()
    Range("A1").Select
    For i = 1 To 10
        If Range("A1").Offset(i, 0) <> vbNullString Then
            ActiveCell.Offset(1, 0).Select
        End If
    Next i
End Sub
Eso sí, tenemos que tener en cuenta que la celda que quedará seleccionada será la primera cuya celda siguiente esté vacía. Por lo tanto, si la lista tiene huecos, debemos contar con ellos:

También es posible seleccionar la última celda con datos de una lista mediante el código:

Sub UltimaCeldaConDatosConBucleDoUntil()
    Range("A1").Select
    Do Until ActiveCell.Offset(1, 0) = vbNullString
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Pero el código más óptimo en cuanto a velocidad y longitud es el siguiente:

Sub UltimaCeldaConDatosDesdeArriba()
    Range("A1:A10").End(xlDown).Select
End Sub

Como vemos, utilizamos en este caso la propiedad End con el argumento xlDown (equivalente a utilizar con el teclado CTRL + MAYUS + Flecha abajo).

Con un código similar podemos salvar el inconveniente de los "huecos" en la lista. El truco está en empezar a "buscar" desde abajo (desde la última celda de Excel de una columna determinada):

Sub PrimeraCeldaConDatosDesdeAbajo()
'AVERIGUAMOS CUANTAS FILAS TIENE EXCEL (SEGÚN VERSIÓN, TENDRÁ MÁS O MENOS)
    UltimaFila = Range("A:A").Count'DESDE LA ÚLTIMA CELDA, BUSCAMOS LA PRIMERA CELDA QUE TIENE DATOS EN LA LISTA
     Range("A" & UltimaFila).End(xlUp).Select
End Sub

2) Utilizar la versión "solo texto" de algunas funciones 

Cuando queremos utilizar alguna de las funciones nativas que tiene VBA, si lo vamos a hacer exclusivamente con textos, podemos utilizar la versión óptima de la función.

Las siguientes funciones...

Left(), Mid(), Right(), Chr(), ChrW(), UCase(), LCase(), LTrim(), RTrim(), Trim(), Space(), String(), Format(), Hex(), Oct(),Str(), Error

... son más lentas que sus correspondientes versiones que sirven para utilizar exclusivamente con textos....

Left$(), Mid$(), Right$(), Chr$(), ChrW$(), UCase$(), LCase$(), LTrim$(), RTrim$(), Trim$(), Space$(), String$(), Format$(), Hex$(), Oct$(),Str$(), Error$

3) Definir constantes cuando hay textos o valores que se repiten mucho

Si hay textos o valores en nuestro código que se repiten mucho a lo largo de nuetro poryecto, podemos incluirlas en una constante. Las constantes almacenan valores que, como su nombre indica, permanecen iguales durante la ejecución de una aplicación. A diferencia de las variables, cuando se declara una constante se le debe asignar su valor en ese momento:

Const NombreDeConstante As TipoDato = ValorConstante

Por ejemplo, si sabemos que en nuestro código se repite constantemente dentro de los cuadros de mensaje (MsgBox) la frase: "¿Está seguro de continuar con el proceso?. Si no lo está, haga clic en el botón 'Cancelar'. Si desea continuar, haga clic en 'Aceptar'." , podemos incluirla en una variable mucho más corta que será gestionada mucho mejor por el usuario y por VBA:

Sub FuncionesDeTextoOptimas()
    Const PreguntaAceptar As String = "¿Está seguro de continuar con el proceso?. Si no lo está, haga clic en el botón 'Cancelar'. Si desea continuar, haga clic en 'Aceptar'."""
    MsgBox PreguntaAceptar
End Sub
Si sabemos que vamos a necesitar la constante en varios procedimientos del módulo, debemos declararla al principio del módulo, en la zona de declaraciones:

 

Si sabemos que vamos a necesitar la constante en cualquier módulo de la aplicación, debemos declararla al principio de algún módulo, en la zona de declaraciones y con la palabra Public delante:

José Manuel Pomares Medrano