VBA Excel – Colecciones

Este tutorial demostrará cómo utilizar las colecciones en VBA.

¿Qué es una colección en VBA?

Una colección es un objeto que contiene un número de elementos similares a los que se puede acceder y manipular fácilmente, incluso si hay un gran número de elementos dentro de la colección.

Usted puede crear sus propias colecciones, pero VBA también viene con colecciones incorporadas como la colección Sheets que almacena todas las hojas del libro de trabajo. Usando un bucle For Each, puede iterar a través de cada hoja de trabajo en la colección Sheets.

Sub Prueba_Worksheets()
    Dim Sh As Worksheet
    For Each Sh In Sheets
        MsgBox Sh.Name
        MsgBox Sh.Visible
    Next Sh
End Sub

También puede dirigirse a una hoja de trabajo específica en la colección utilizando el valor del índice (su posición en la colección), o el nombre real de la hoja de trabajo:

MsgBox Sheets(1).Name
MsgBox Sheets("Sheet1").Name

A medida que se añaden o eliminan hojas de trabajo, la colección de hojas crece o disminuye de tamaño. Tenga en cuenta que con las colecciones de VBA el número de índice comienza con 1 y no con 0.

Colecciones vs Arrays

Tanto los arrays como las colecciones pueden almacenar datos, pero tienen varias diferencias notables:

1. Los arrays son multidimensionales mientras que las colecciones son de una sola dimensión. Puedes dimensionar un array con varias dimensiones, por ejemplo

Dim MyArray(10, 2) As String

Esto crea un array de 10 filas con 2 columnas, casi como una hoja de cálculo. Una colección es efectivamente una sola columna.

2. Cuando rellenas tu array, necesitas una línea de código separada para poner un valor en cada elemento del array. Si tuvieras un array de dos dimensiones necesitarías en realidad 2 líneas de código – una línea para dirigir la primera columna y otra para dirigir la segunda columna. Con el objeto Collection, basta con utilizar el método Add para que el nuevo elemento se añada a la colección y el valor del índice se ajuste automáticamente.
3. Si necesitas eliminar un elemento de datos, entonces es más complicado en el array. Puedes establecer los valores de un elemento a un valor en blanco, pero el elemento en sí sigue existiendo dentro del array. Si estás usando un bucle For Next para iterar a través del array, el bucle devolverá un valor en blanco, lo que necesitará codificación para asegurarse de que el valor en blanco es ignorado. En una colección se utilizan los métodos Add o Remove, y toda la indexación y redimensionamiento se realiza automáticamente. El elemento que se ha eliminado desaparece por completo. Los arrays son útiles para un tamaño fijo de datos, pero las colecciones son mejores cuando la cantidad de datos puede cambiar.
4. Las colecciones son de sólo lectura, mientras que los valores de los arrays pueden modificarse mediante VBA. En el caso de una colección, hay que eliminar primero el valor que se va a modificar y luego añadir el nuevo valor modificado.
5. En un array, sólo se puede utilizar un único tipo de datos para los elementos que se establece cuando se dimensiona el array. Sin embargo, en el array puedes utilizar tipos de datos personalizados que hayas diseñado tú mismo. Podrías tener una estructura de array muy complicada utilizando un tipo de datos personalizado que a su vez tiene varios tipos de datos personalizados por debajo. En una colección, puedes añadir tipos de datos de uso para cada elemento. Usted podría tener un valor numérico, una fecha, o una cadena – el objeto de colección tomará cualquier tipo de datos. Si tratas de poner un valor de cadena en un array que está dimensionado como numérico, produciría un mensaje de error.
6. Las colecciones son generalmente más fáciles de usar que los arrays. En términos de codificación, cuando se crea un objeto colección, sólo tiene dos métodos (Add y Remove) y dos propiedades (Count y Item), por lo que el objeto no es en absoluto complicado de programar.
7. Las colecciones pueden utilizar claves para localizar los datos. Los arrays no tienen esta función y requieren un código de bucle para iterar a través del array para encontrar valores específicos.
8. El tamaño de un array necesita ser definido cuando se crea por primera vez. Es necesario tener una idea de la cantidad de datos que va a almacenar. Si necesitas aumentar el tamaño del array puedes usar ‘ReDim’ para redimensionarlo, pero necesitas usar la palabra clave ‘Preserve’ si no quieres perder los datos que ya están en el array. No es necesario definir el tamaño de la colección. Simplemente crece y se reduce automáticamente a medida que se añaden o eliminan elementos.

Alcance de un objeto de colección

En términos de alcance, el objeto colección sólo está disponible mientras el libro de trabajo está abierto. No se guarda cuando se guarda el libro. Si el libro de trabajo se vuelve a abrir, la colección debe volver a crearse mediante código VBA. Si quieres que tu colección esté disponible para todo el código de tu módulo de código, entonces necesitas declarar el objeto de colección en la sección Declarar en la parte superior de la ventana del módulo.

Esto asegurará que todo tu código dentro de ese módulo pueda acceder a la colección. Si quieres que cualquier módulo dentro de tu libro de trabajo pueda acceder a la colección, entonces defínela como un objeto global.

Global miColeccion As New Collection

Crear una colección, añadir elementos y acceder a ellos

Un simple objeto de colección puede ser creado en VBA usando el siguiente código:

Sub crearColeccion()
    'Crear Colección
    Dim miColeccion As New Collection
    'Agregar elementos a la coleccion
    miColeccion.Add "Item1"
    miColeccion.Add "Item2"
    miColeccion.Add "Item3"
End Sub

El código crea un nuevo objeto llamado ‘miColeccion’ y luego las siguientes líneas de código utilizan el método Add para añadir 3 nuevos valores.

Bucle a través de la colección

A continuación, puede utilizar el código para iterar a través de cada elemento en su colección:

For Each Item In miColeccion
    MsgBox Item
Next Item

O este código obtendrá el tamaño de la colección con .Count y hará un bucle a través de cada número de índice:

For n = 1 To miColeccion.Count
    MsgBox miColeccion(n)
Next n

El primer bucle For Each es más rápido que el segundo bucle For Next pero sólo funciona en una dirección (del índice bajo al alto). El bucle For Next tiene la ventaja de que puede utilizar una dirección diferente (índice alto a bajo) y también puede utilizar el método Step para cambiar el incremento. Esto es útil cuando quieres borrar varios elementos ya que tendrás que ejecutar el borrado desde el final de la colección hasta el principio ya que el índice cambiará a medida que se produzcan los borrados.

Añadir un elemento a la colección

El método Añadir en una colección tiene 3 parámetros opcionales – Clave, Antes y Después. Puede utilizar los parámetros «Antes» y «Después» para definir la posición de su nuevo elemento en relación con los otros que ya están en la colección. Esto se hace especificando el número de índice con el que quieres que tu nuevo elemento sea relativo.

Sub crearColeccion()
    'Crear Colección
    Dim miColeccion As New Collection
    'Agregar elementos a la coleccion
    miColeccion.Add "Item1"
    miColeccion.Add "Item2", , 1
    miColeccion.Add "Item3"
End Sub

En este ejemplo se ha especificado que ‘Item2’ se añada antes del primer elemento indexado de la colección (que es ‘Item1’). Al iterar por esta colección «Item2» aparecerá en primer lugar, seguido de «Item1» y «Item3».

Cuando se especifica un parámetro «Antes» o «Después», el valor del índice se ajusta automáticamente dentro de la colección, de modo que «Item2» se convierte en el valor del índice 1 y «Item1» se mueve a un valor del índice 2.

También puede utilizar el parámetro «Clave» para añadir un valor de referencia que pueda utilizar para identificar el elemento de la colección. Ten en cuenta que un valor clave debe ser una cadena y debe ser único dentro de la colección.

Sub crearColeccion()
    'Crear Colección
    Dim miColeccion As New Collection
    'Agregar elementos a la coleccion
    miColeccion.Add "Item1"
    miColeccion.Add "Item2", "miClave"
    miColeccion.Add "Item3"
    MsgBox miColeccion("miClave")
End Sub

a ‘Item2’ se le ha dado un valor de ‘Clave’ de ‘miClave’ para poder referirse a ese elemento usando el valor de ‘miClave’ en lugar del número de índice (2).

Tenga en cuenta que el valor de la «Clave» tiene que ser un valor de cadena. No puede ser ningún otro tipo de datos. Tenga en cuenta que la colección es de Sólo Lectura, y no puede actualizar el valor de la clave una vez que se ha establecido. Además, no se puede comprobar si existe un valor clave para un elemento específico de la colección ni ver el valor clave, lo cual es un pequeño inconveniente.

El parámetro «Clave» tiene la ventaja añadida de hacer que su código sea más legible, especialmente si se lo entrega a un colega para que lo soporte, y no tiene que iterar por toda la colección para encontrar ese valor. Imagina que tienes una colección de 10.000 elementos, ¡qué difícil sería hacer referencia a un elemento concreto!

Eliminar un elemento de la colección

Puedes utilizar el método ‘Remove’ para eliminar elementos de tu colección.

miColeccion.Remove (2)

Desgraciadamente, no es fácil, si la colección tiene un gran número de elementos, averiguar el índice del elemento que se quiere eliminar. Aquí es donde el parámetro ‘Key’ resulta útil cuando se crea la colección.

miColeccion.Remove (“miClave”)

Cuando se elimina un elemento de una colección, los valores del índice se restablecen automáticamente en toda la colección. Aquí es donde el parámetro «Clave» es tan útil cuando se eliminan varios elementos a la vez. Por ejemplo, puedes eliminar el índice 105 del elemento, y al instante el índice 106 del elemento se convierte en el índice 105, y todo lo que está por encima de este elemento tiene su valor de índice desplazado hacia abajo. Si utilizas el parámetro «Clave», no tienes que preocuparte de qué valor de índice hay que eliminar.

Para eliminar todos los elementos de la colección y crear una nueva colección, se utiliza de nuevo la sentencia Dim que crea una colección vacía.

Dim miColeccion As New Collection

Para eliminar el objeto de la colección por completo, puede establecer el objeto a nada.

Set miColeccion = Nothing

Esto es útil si la colección ya no es necesaria para su código. Al establecer el objeto de la colección como nada, se elimina toda referencia a él y se libera la memoria que estaba utilizando. Esto puede tener importantes implicaciones en la velocidad de ejecución de su código, si un objeto grande está en la memoria que ya no es necesario.

Contar el número de elementos de una colección

Puedes averiguar fácilmente el número de elementos de tu colección utilizando la propiedad ‘Count’.

MsgBox miColeccion.Count

Utilizarías esta propiedad si estuvieras utilizando un bucle For Next para iterar a través de la colección, ya que te proporcionará el límite superior del número de índices.

Probar la colección para un valor específico

Puede iterar a través de una colección para buscar un valor específico para un elemento utilizando un bucle For Each.

Sub buscar_en_Coleccion()
    Dim miColeccion As New Collection, item As Variant
     
    miColeccion.Add "Item1"
    miColeccion.Add "Item2"
    miColeccion.Add "Item3"
     
    For Each item In miColeccion
        If item = "Item2" Then
            MsgBox item & " encontrado"
        End If
    Next
End Sub

El código crea una pequeña colección, y luego la recorre buscando un elemento llamado ‘item2″. Si lo encuentra, muestra un cuadro de mensaje que indica que ha encontrado el elemento específico.

Uno de los inconvenientes de esta metodología es que no se puede acceder al valor del índice ni al valor de la clave.

Si utiliza un bucle For Next en su lugar, puede utilizar el contador For Next para obtener el valor del índice, aunque sigue sin poder obtener el valor de la ‘Clave’.

Sub buscar_en_Coleccion()
    Dim miColeccion As New Collection, N As Long
    miColeccion.Add "Item1"
    miColeccion.Add "Item2"
    miColeccion.Add "Item3"
     
    For N = 1 To miColeccion.Count
            If miColeccion.item(N) = "Item2" Then
                MsgBox miColeccion.item(N) & " encontrado en la posición del índice " & N
            End If
    Next N
End Sub

El contador For Next (n) proporcionará la posición del índice.

Ordenar la colección

No hay una funcionalidad incorporada para ordenar una colección, pero usando un poco de pensamiento menos cuadrático, se puede escribir un código para hacer una ordenación, utilizando la función de ordenación de la hoja de cálculo de Excel. Este código utiliza una hoja de cálculo en blanco llamada ‘SortSheet’ para hacer la ordenación real.

Sub ordenarColeccion()
    Dim miColeccion As New Collection, N As Long, item As Variant
    Dim Contador As Long
     
    'Construir la colección con elementos de orden aleatorio
    miColeccion.Add "Item5"
    miColeccion.Add "Item2"
    miColeccion.Add "Item4"
    miColeccion.Add "Item1"
    miColeccion.Add "Item3"
     
    'Capturar el número de elementos de la colección para su uso futuro
    Contador = miColeccion.Count
     
    'Recorrer la colección copiando cada elemento en una celda consecutiva de 'Hoja_Ordenada' (columna A)
    For N = 1 To miColeccion.Count
        Sheets("Hoja_Ordenada").Cells(N, 1) = miColeccion(N)
     
    Next N
    'Active la "Hoja_Ordenada" y utilice la rutina de ordenación de Excel para clasificar los datos en orden ascendente
    Sheets("Hoja_Ordenada").Activate
    Range("A1:A" & miColeccion.Count).Select
        ActiveWorkbook.Worksheets("Hoja_Ordenada").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Hoja_Ordenada").Sort.SortFields.Add2 Key:=Range( _
            "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Hoja_Ordenada").Sort
            .SetRange Range("A1:A5")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    'Borrar todos los elementos de la colección - tenga en cuenta que este bucle For Next se ejecuta en orden inverso
    For N = miColeccion.Count To 1 Step -1
         miColeccion.Remove (N)
    Next N
     
    'Copiar los valores de las celdas de nuevo en el objeto de colección vacío utilizando el valor almacenado (Contador) para el 'bucle
    For N = 1 To Contador
        miColeccion.Add Sheets("Hoja_Ordenada").Cells(N, 1).Value
     
    Next N
     
    'Iterar a través de la colección para probar el orden en que se encuentran los elementos
    For Each item In miColeccion
        MsgBox item
     
    Next item
     
    'Borrar la hoja de trabajo (Hoja_Ordenada) - si es necesario, borrarla también
    Sheets("Hoja_Ordenada").Range(Cells(1, 1), Cells(Contador, 1)).Clear
End Sub

Este código crea primero una colección con los elementos añadidos en un orden aleatorio. Luego los copia en la primera columna de una hoja de cálculo (Hoja_Ordenada).

El código utiliza la herramienta de ordenación de Excel para ordenar los datos de la columna en orden ascendente. El código también puede ser modificado para ordenar en orden descendente.

La colección se vacía de datos utilizando un bucle For Next. Observe que la opción de paso se utiliza para que se vacíe desde el final de la colección hasta el principio. Esto se debe a que, a medida que se vacía, los valores de los índices se restablecen, si se vacía desde el principio, no se vacía correctamente (el índice 2 se convertiría en el índice 1)

Finalmente, usando otro bucle For Next, los valores de los ítems se transfieren de nuevo a la colección vacía

Un nuevo bucle For Each comprueba que la colección está ahora en buen orden ascendente.

Desgraciadamente, esto no se ocupa de los valores clave que puedan haberse introducido originalmente, ya que los valores clave no pueden leerse

Pasar una colección a una subfunción/función

Una colección puede ser pasada a un Sub o a una Función de la misma manera que cualquier otro parámetro

Function miFuncion(ByRef miColeccion as Collection)

Es importante pasar la colección usando ‘ByRef’. Esto significa que se utiliza la colección original. Si la colección se pasa usando ‘ByVal’ entonces se crea una copia de la colección que puede tener repercusiones desafortunadas.

Si se crea una copia utilizando ‘ByVal’, cualquier cosa que cambie la colección dentro de la función sólo ocurre en la copia y no en el original. Por ejemplo, si dentro de la función se añade un nuevo elemento a la colección, éste no aparecerá en la colección original, lo que creará un error en tu código.

Devolver una colección desde una función

Puedes devolver una colección desde una función de la misma manera que devuelves cualquier objeto. Debe utilizar la palabra clave Set.

Sub DevolverDesdeFuncion()
    Dim miColeccion As Collection
    Set miColeccion = llenarColeccion
    MsgBox miColeccion.Count
End Sub

Este código crea una sub-rutina que crea un objeto llamado ‘miColeccion’ y luego utiliza la palabra clave ‘Set’ para llamar efectivamente a la función para llenar esa colección. Una vez hecho esto, muestra un cuadro de mensaje para mostrar el recuento de 2 elementos.

Function llenarColeccion() As Collection
    Dim miColeccion As New Collection
    miColeccion.Add "Item1"
    miColeccion.Add "Item2"
     
    Set llenarColeccion = miColeccion
End Function

La función llenarColeccion crea un nuevo objeto de colección y lo rellena con 2 elementos. A continuación, devuelve este objeto al objeto de colección creado en la sub-rutina original.

Convertir una colección en un Array

Es posible que desee convertir su colección en un array. Puede que quieras almacenar los datos donde puedan ser cambiados y manipulados. Este código crea una pequeña colección y luego la transfiere a un array

Observa que el índice de la colección comienza en 1 mientras que el índice del array comienza en 0. Mientras que la colección tiene 3 elementos, el array sólo necesita ser dimensionado a 2 porque hay un elemento 0

Sub convertirColeccionEnArray()
    Dim miColeccion As New Collection
    Dim MiArray(2) As String, N As Long
     
    miColeccion.Add "Item1"
    miColeccion.Add "Item2"
    miColeccion.Add "Item3"
     
    For N = 1 To miColeccion.Count
        MiArray(N - 1) = miColeccion(N)
    Next N
     
    For N = 0 To 2
        MsgBox MiArray(N)
    Next N
End Sub

Convertir un Array en una Colección

Puede querer convertir un array en una colección. Por ejemplo, puede querer acceder a los datos de una manera más rápida y elegante que usando código para obtener un elemento del array.

Ten en cuenta que esto sólo funcionará para una sola dimensión del array porque la colección sólo tiene una dimensión.

Sub ConvertirArrayEnColeccion()
    Dim miColeccion As New Collection
    Dim MiArray(2) As String, N As Long, item As Variant
    MiArray(0) = "item1"
    MiArray(1) = "Item2"
    MiArray(2) = "Item3"
     
    For N = 0 To 2
        miColeccion.Add MiArray(N)
     
    Next N
    For Each item In miColeccion
        MsgBox item
    Next item
End Sub

Si desea utilizar una matriz multidimensional, podría concatenar los valores de la matriz juntos para cada fila dentro de la matriz utilizando un carácter delimitador entre las dimensiones de la matriz, de modo que al leer el valor de la colección, podría utilizar programáticamente el carácter delimitador para separar los valores.

También podría mover los datos en la colección sobre la base de que el valor de la primera dimensión se añade (índice 1), y luego el valor de la siguiente dimensión se añade (índice 2) y así sucesivamente.

Si el array tuviera, digamos, 4 dimensiones, cada cuarto valor de la colección sería un nuevo conjunto de valores.

También se podrían añadir valores de la matriz para utilizarlos como claves (siempre que sean únicos), lo que añadiría una forma fácil de localizar datos específicos.