VBA ArrayList – Mega-Guía Actualizada para 2022

Uso de un ArrayList en VBA

Un ArrayList es un objeto VBA que puede ser utilizado para almacenar valores. Es similar a un objeto Collection, pero tiene mucha más flexibilidad desde el punto de vista de la programación. Vamos a discutir algunas diferencias entre ArrayLists y Colecciones y Arrays.

• El objeto Collection sólo tiene dos métodos (Add, Remove) y dos propiedades (Count, Item) mientras que un ArrayList tiene muchos más.
• El objeto Collection es de sólo lectura. Una vez añadidos los valores, el valor indexado no puede ser modificado, mientras que en un ArrayList es posible la edición.
• El objeto ArrayList se expande y se contrae según el número de elementos que contenga. No necesita ser dimensionado antes de su uso como un Array.
• El ArrayList es unidimensional (igual que el objeto Collection) y el tipo de datos por defecto es Variant, lo que significa que aceptará cualquier tipo de datos, ya sean numéricos, de texto o de fecha.

En muchos sentidos, el ArrayList aborda una serie de deficiencias del objeto Collection. Es ciertamente mucho más flexible en lo que puede hacer.

El objeto ArrayList  no forma parte de la biblioteca estándar de VBA. Puede utilizarlo en su código VBA de Excel utilizando la vinculación tardía o temprana.

Sub Ejemplo_vinculacion_tardia()
    Dim MyList As Object
    Set MyList = CreateObject("System.Collections.ArrayList")
End Sub

Sub Ejemplo_vinculacion_temprana()
    Dim MyList As New ArrayList
End Sub

Para utilizar el ejemplo de vinculación temprana, primero debe introducir una referencia en VBA al archivo ‘mscorlib.dll’ Esto se hace seleccionando ‘Herramientas | Referencias ‘ en la ventana del Editor de Visual Basic (VBE). Aparecerá una ventana emergente con todas las referencias disponibles. Desplácese hacia abajo hasta ‘mscorlib.dll’ y marque la casilla junto a él. Haz clic en Aceptar y esa biblioteca formará parte de tu proyecto:

Uno de los grandes inconvenientes de un objeto ArrayList es que no tiene ‘Intellisense’. Normalmente, cuando usas un objeto en VBA como un rango, verás una lista emergente de todas las propiedades y métodos disponibles. Esto no se consigue con un objeto ArrayList, y a veces es necesario revisar cuidadosamente para asegurarse de que se ha escrito correctamente el método o la propiedad.

Además, si pulsas F2 en la ventana del VBE y buscas en ‘ArrayList ‘, no se mostrará nada, lo que no es muy útil para un desarrollador.

Su código se ejecutará considerablemente más rápido con la vinculación temprana, porque todo se compila por adelantado. Con la vinculación tardía, el objeto tiene que ser compilado a medida que el código se ejecuta

Cómo distribuir su aplicación de Excel que contiene un ArrayList

Como ya se ha señalado, el objeto ArrayList no forma parte de Excel VBA. Esto significa que cualquiera de tus compañeros a los que distribuyas la aplicación debe tener acceso al archivo ‘mscorlib.dll’

Este archivo se encuentra normalmente en:

C:\NWindows\NMicrosoft.NET\NFramework\Nv4.0.30319

Podría valer la pena escribir algún código (utilizando el método Dir) para comprobar que este archivo existe cuando un usuario carga la aplicación para que experimenten un «aterrizaje suave» si no se encuentra. Si no está presente, y el código se ejecuta entonces se producirán errores.

Además, el usuario debe tener instalada la versión correcta de .Net Framework. Incluso si el usuario tiene una versión posterior, la V3.5 debe estar instalada, de lo contrario su aplicación no funcionará

Alcance de un Objeto ArrayList

En términos de alcance, el objeto ArrayList 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, el objeto ArrayList debe volver a crearse mediante código VBA.

Si quieres que tu ArrayList esté disponible para todo el código de tu módulo de código, entonces necesitas declarar el objeto Lista de Matriz en la sección Declarar en la parte superior de la ventana del módulo

Esto asegurará que todo su código dentro de ese módulo pueda acceder al ArrayList. Si quieres que cualquier módulo de tu libro de trabajo pueda acceder al objeto ArrayList, entonces defínelo como un objeto global.

Global MyCollection As New ArrayList

Rellenando y Leyendo de su ArrayList

La acción más básica que quieres realizar es crear una ArrayList, poner algunos datos en ella y luego probar que los datos pueden ser leídos. Todos los ejemplos de código de este artículo suponen que estás utilizando vinculación temprana, y que has añadido ‘mscorlib.dll’ a las referencias VBA, como se ha descrito anteriormente.

Sub Ejemplo_ArrayList()
    'Crear nuevo objeto tipo ArrayList
    Dim MyList As New ArrayList, N As Long
     
    'Agregar elementos a la lista
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
     
    'Iterar a través del ArrayList para mostrar valores
    For N = 0 To MyList.Count - 1
        MsgBox MyList(N)
    Next N
 
End Sub

Este ejemplo crea un nuevo objeto ArrayList, lo rellena con 3 elementos, e itera a través de la lista mostrando cada elemento. Tenga en cuenta que el índice de ArrayList comienza en 0, no en 1, por lo que debe restar 1 al valor de Count También puedes utilizar un bucle ‘For…Each ‘ para leer los valores:

Sub Ejemplo2_ArrayList()
    'Crear nuevo objeto tipo ArrayList
    Dim MyList As New ArrayList, I As Variant
     
    'Agregar elementos a la lista
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
     
    'Iterar a través del ArrayList para mostrar valores
    For Each I In MyList
        MsgBox I
    Next I
End Sub

Edición y Cambio de Elementos en un ArrayList

Una de las principales ventajas de un ArrayList sobre una Colección es que los elementos de la lista pueden ser editados y cambiados dentro de su código. El objeto Collection es sólo de lectura mientras que el objeto ArrayList es de lectura/escritura.

Sub Ejemplo3_ArrayList()
    'Crear nuevo objeto tipo ArrayList
    Dim MyList As New ArrayList, I As Variant
     
    'Agregar elementos a la lista
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
     
    'Cambiar el elemento "Item2" a "Cambiado"
    MyList(1) = "Cambiado"
     
    'Iterar a través del ArrayList para probar el cambio
    For Each I In MyList
        'Mostrar el elemento
        MsgBox I
    Next I
End Sub

En este ejemplo, el segundo ítem, ‘Item2’ es alterado al valor ‘Cambiado’ (recuerde que el índice comienza en 0). Cuando se ejecute la iteración al final del código, se mostrará el nuevo valor.

Adición de un Rango de Valores a una Lista de Arreglos

Puede introducir valores en su Lista de Arreglos utilizando un arreglo que contenga una lista de estos valores o referencias a valores de celdas en una hoja de cálculo

Sub Ejemplo_agregar_valores()
    'Crear nuevo objeto tipo ArrayList
    Dim MyList As New ArrayList, v As Variant, N As Long
     
    'Iterar a través de un array de valores agregándolos al ArrayList
    For Each v In Array("A1", "A2", "A3")
        'Add each array value to list
        MyList.Add v
    Next
     
    'Iterar a través de los valores del array con las referencias de la hoja añadiéndolos al ArrayList
    For Each v In Array(Range("A5").Value, Range("A6").Value)
        MyList.Add v
    Next
     
    'Iterar a través del ArrayList para mostrar los valores
    For N = 0 To MyList.Count - 1
       'Mostrar los elementos
        MsgBox MyList.Item(N)
    Next N
End Sub

Lectura / Recuperación de un Rango de Elementos de un ArrayList

Usando el método GetRange en un ArrayList, puedes especificar un rango de elementos consecutivos a recuperar. Los dos parámetros requeridos son la posición del índice inicial y el número de elementos a recuperar. El código rellena un segundo objeto Array List con el subconjunto de ítems que puede ser leído por separado.

Sub Ejemplo_Leer_Rango()
    'Definir Obtjetos
    Dim MyList As New ArrayList, MyList1 As Object, I As Variant
     
    'Agregar elementos al Objeto "MyList"
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    MyList.Add "Item6"
    MyList.Add "Item4"
    MyList.Add "Item7"
     
    'Capturar 4 elementos en "MyList" iniciando en la posición 2
    Set MyList1 = MyList.GetRange(2, 4)
     
    'Iterar a través del Objeto "MyList1" para mostrar el subconjunto de elementos
    For Each I In MyList1
       'Mostrar elementos
        MsgBox I
    Next I
End Sub

Búsqueda de Elementos en un ArrayList

Puede comprobar si un elemento nombrado está en su lista utilizando el método ‘Contains’. Esto devolverá True o False

MsgBox MyList.Contains("Item2")

También puedes encontrar la posición real del índice utilizando el método ‘IndexOf’. Es necesario especificar el índice de inicio de la búsqueda (normalmente 0). El valor de retorno es el índice de la primera instancia del elemento encontrado. A continuación, puede utilizar un bucle para cambiar el punto de partida al siguiente valor del índice para encontrar más instancias si hay varios valores duplicados.

Si no se encuentra el valor, se devuelve un valor de -1

Este ejemplo demuestra el uso de ‘Contains’, el elemento no encontrado, y el bucle a través del ArrayList para encontrar la posición de todos los elementos duplicados:

Sub Ejemplo_Busqueda_Lista()
    'Definir ArrayList y variables
    Dim MyList As New ArrayList, Sp As Integer, Pos As Integer
     
    'Agregar nuevos elementos incluso un duplicado
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    MyList.Add "Item1"
     
    'Probar si "Item2" está en la Lista - devolverá Verdadero
    MsgBox MyList.Contains("Item2")
     
    'Obtener la posición de un valor inexistente - devolverá -1
    MsgBox MyList.IndexOf("Item", 0)
     
    'Definir la posición inicial para la búsqueda en cero (0)
    Sp = 0
     
    'Iterar a través de la lista para obtener todas las posiciones de "Item1"
    Do
         'Obtener la posición del índice del siguiente 'Item1' en base a la posición en la variable 'Sp'
        Pos = MyList.IndexOf("Item1", Sp)
       'Si no se encuentran más instancias de 'Item1', entonces se sale del bucle
        If Pos = -1 Then Exit Do
       'Muestra la siguiente instancia encontrada y la posición del índice
        MsgBox MyList(Pos) & " at index " & Pos
       'Añade 1 al último valor de índice encontrado - éste se convierte en la nueva posición de inicio para la siguiente búsqueda
        Sp = Pos + 1
    Loop
 
End Sub

Tenga en cuenta que el texto de búsqueda utilizado distingue entre mayúsculas y minúsculas y que no se aceptan comodines.

Inserción y Eliminación de Elementos

Si no desea añadir sus elementos al final de la lista, puede insertarlos en una posición de índice determinada para que el nuevo elemento se encuentre en el centro de la lista. Los números de índice se ajustarán automáticamente para los elementos posteriores.

Sub Ejemplo_Insertar_Elemento()
    'Definir el objeto ArrayList
    Dim MyList As New ArrayList, N As Long
     
    'Agregar elementos al ArrayList
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    MyList.Add "Item1"
     
    'Insertar "Item6" en la posición 2
    MyList.Insert 2, "Item6"
     
    'Iterar a través de los elementos en el ArrayList para mostrar el nuevo orden y sus posiciones
    For N = 0 To MyList.Count - 1
        MsgBox MyList(N) & " posición " & N
    Next N
 
End Sub

En este ejemplo, ‘Item6’ se añade a la lista en la posición de índice 2, por lo que el ‘item3’ que estaba en la posición de índice 2 ahora se mueve a la posición de índice 3. Un elemento individual puede ser eliminado utilizando el método ‘Remove’.

MyList.Remove "Item"

Observe que no se produce ningún error si no se encuentra el nombre del elemento. Todos los números de índice posteriores se cambiarán para adaptarse a la eliminación. Si conoce la posición del índice del elemento puede utilizar el método ‘RemoveAt’, por ejemplo

MyList.RemoveAt 2

Tenga en cuenta que si la posición del índice dada es mayor que el número de elementos del ArrayList, se devolverá un error. Puedes eliminar un rango de valores de la lista utilizando el método ‘RemoveRange’. Los parámetros son el índice inicial y el número de elementos a eliminar, por ejemplo

MyList.RemoveRange 3, 2

Tenga en cuenta que obtendrá un error en su código si el número de elementos desplazados desde el valor inicial es mayor que el número de elementos del ArrayList. Tanto en el método ‘RemoveAt’ como en el método ‘RemoveRange’, sería recomendable que algún código comprobara si los números de índice especificados son mayores que el número total de elementos del ArrayList para atrapar cualquier posible error. La propiedad ‘Count’ dará el número total de elementos del ArrayList.

Sub Ejemplo_Eliminar()
    'Definir objeto ArrayList
    Dim MyList As New ArrayList, N As Long
    'Agregar elementos al objeto ArrayList
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    MyList.Add "Item1"
    MyList.Add "Item4"
    MyList.Add "Item5"
    'Insertar ‘Item6’ en la posición 2
    MyList.Insert 2, "Item6"
    'Eliminar ‘Item2’
    MyList.Remove "Item2"
    'Eliminar ‘Item’ – esto no existe en la lista del array pero no da error
    MyList.Remove "Item"
    'Eliminar el item en la posición 2
    MyList.RemoveAt 2
    'Eliminar 2 elementos consecutivos iniciando en la posición 2
    MyList.RemoveRange 3, 2
    'Iterar a través del ArrayList para mostrar que quedó y en que posiciones están ahora.
    For N = 0 To MyList.Count - 1
        MsgBox MyList(N) & " Index " & N
    Next N
End Sub

Tenga en cuenta que si está utilizando el ‘RemoveAt’ para eliminar un elemento en una posición específica, entonces tan pronto como ese elemento es eliminado, todas las posiciones de índice posteriores son alteradas. Si tiene múltiples eliminaciones usando la posición del índice, entonces una buena idea es comenzar con el número de índice más alto y retroceder hasta la posición cero para que siempre elimine el elemento correcto. De esta manera no tendrá el problema

Ordenación de un ArrayList

Otra gran ventaja respecto a una colección es que puedes ordenar los elementos de forma ascendente o descendente. El objeto ArrayList es el único objeto en Excel VBA con un método de ordenación. El método de ordenación es muy rápido y esto puede ser una consideración importante para el uso de un ArrayList. En el objeto colección, era necesario pensar un poco para ordenar todos los ítems, pero con un ArrayList, es muy simple. El método ‘Sort’ ordena en orden ascendente, y el método ‘Reverse’ ordena en orden descendente.

Sub OrdenarArrayList()
    'Crear ArrayList
    Dim MyList As New ArrayList, I As Variant
    'Agregar elementos desordenados
    MyList.Add "Item1"
    MyList.Add "Item3"
    MyList.Add "Item2"
    'Ordenar elementos en forma ascendente
    MyList.Sort
    'Iterar a través de los elementos para mostrar el orden
    For Each I In MyList
        'Mostrar el elemento
        MsgBox I
    Next I
    'Ordenar los elementos en forma descendente
    MyList.Reverse
    'Iterar a través de los elementos para mostrar el orden
    For Each I In MyList
        'Mostrar el elemento
        MsgBox I
    Next I
End Sub

Clonación de un ArrayList

Un ArrayList tiene la facilidad de crear un clon o copia de sí misma. Esto es útil si un usuario realiza cambios en los elementos utilizando un front end y su código VBA, pero usted necesita mantener una copia de los elementos en su estado original como respaldo. Esto podría proporcionar al usuario una función de «Deshacer». Puede que hayan hecho los cambios y deseen volver a la lista original.

Sub Ejemplo_Clonar_ArrayList()
    'definir un ArrayList y un Objeto
    Dim MyList As New ArrayList, MyList1 As Object, I As Variant
    'Llenar el primer objeto con elementos
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    'Copiar MyList a MyList1
    Set MyList1 = MyList.Clone
    'Iterar a través del objeto MyList1 para probar la clonación
    For Each I In MyList1
        'Display item name
        MsgBox I
    Next I
End Sub

‘MyList1’ contiene ahora todos los elementos de ‘MyList’ en el mismo orden

Copia de un ArrayList en un Objeto de Matriz VBA Convencional

Puedes utilizar un método sencillo para copiar el ArrayList en un array normal de VBA:

Sub Ejemplo_copiar_ArrayList_a_Array()
    'Crear ArrayList y Objeto Array Normal
    Dim MyList As New ArrayList, NewArray As Variant, N As Long
    'Llenar el ArrayList con elementos
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    'Copiar el ArrayList al nuevo Array
    NewArray = MyList.ToArray
    'Iterar a través del nuevo Array – note que el ArrayList.Count proporciona el índice máximo
    For N = 0 To MyList.Count - 1
        'Mostrar el elemento
        MsgBox NewArray(N)
    Next N
End Sub

Cómo Copiar un ArrayList en un Rango de Hoja de Trabajo

Puede copiar su ArrayList a una hoja de trabajo y una referencia de celda específicas sin necesidad de iterar por la lista de arrays. Sólo necesita especificar la primera referencia de celda

Sub Ejemplo_copiar_rango()
    'Crear objeto ArrayList
    Dim MyList As New ArrayList
    'Agregar elementos a la lista
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    'Borrar la hoja destino
    Sheets("Hoja1").UsedRange.Clear
    'Copiar elementos a través de una fila
    Sheets("Hoja1").Range("A1").Resize(1, MyList.Count).Value = MyList.toArray
    'Copiar elementos hacia abajo en la columna
    Sheets("Hoja1").Range("A5").Resize(MyList.Count, 1).Value = _
    WorksheetFunction.Transpose(MyList.toArray)
End Sub

Vaciar Todos los Elementos de un ArrayList

Hay una función sencilla (Clear) para borrar el ArrayList completamente

Sub Ejemplo_Borrar_ArrayList()
    'Crear ArrayList
    Dim MyList As New ArrayList
    'Agregar elementos
    MyList.Add "Item1"
    MyList.Add "Item2"
    MyList.Add "Item3"
    'Mostrar cuenta de elementos
    MsgBox MyList.Count
    'Borrar todos los elementos
    MyList.Clear
    'Mostrar cuenta de elementos para demostrar que el borrado ha funcionado
    MsgBox MyList.Count
End Sub

Este ejemplo crea elementos en un ArrayList y luego borra la lista de arrays. Los cuadros de mensaje demuestran antes y después el número de elementos en la lista del array.

Resumen de Métodos de ArrayList para Excel VBA

Tarea	Parámetros	Ejemplos
Añadir / Editar elemento	Valor	MyList.Add «Item1»
		MyList(4)= «Item2»
Clonar un ArrayList	Ninguno	Dim MyList As Object
		Set MyList2 = MyList.Clone
Copiar a un Array	Ninguno	Dim MyArray As Variant
		MyArray = MyList.ToArray
Copiar a un rango(fila) de la hoja de cálculo	Ninguno	Sheets(«Hoja1»).Range(«A1»).Resize(1, MyList.Count).Value = MyList.ToArray
Copiar a una hoja de trabajo rango(columna)	Ninguno	Sheets(«Hoja1»).Rango(«A3»).Resize(MyList.Count, 1).Value= WorksheetFunction.Transpose(MyList.ToArray)
Crear	«System.Collections.ArrayList»	Dim MyList As Object
		Set MyList = CreateObject(«System.Collections.ArrayList»)
Declare	N/A	Dim MyList As Object
Buscar / comprobar si el elemento existe	Elemento a encontrar	MyList.Contains(«Item2»)
Encontrar la posición de un elemento en el ArrayList	1. Elemento a encontrar.	Dim IndexNo As Long
	2. Posición a partir de la cual se inicia la búsqueda.	IndexNo = MyList.IndexOf(«Item3», 0)
		IndexNo = MyList.IndexOf(«Item5», 3)
Obtener el número de elementos	Ninguno	MsgBox MyList.Count
Insertar elemento	1. Índice – posición para insertar en.	MyList.Insert 0, «Item5»
	2 Valor – objeto o valor a insertar.	MyList.Insert 4, «Item7»
Leer ítem	Índice – número entero largo	MsgBox MyList.Item(0)
		MsgBox MyList.Item(4)
Leer el último elemento añadido	Índice – long integer	MsgBox MyList.Item(list.Count – 1)
Leer el primer elemento añadido	Índice – long integer	MsgBox MyList.Item(0)
Leer todos los ítems(For Each)	N/A	Dim element As Variant
		For Each element In MyList
		MsgBox element
		Next element
Leer todos los elementos(For)	Índice – entero largo	Dim i As Long
		For i = 0 To MyList.Count – 1
		MsgBox i
		Next i
Eliminar todos los elementos	Ninguno	MyList.Clear
Eliminar el elemento en la posición	Posición del índice donde está el ítem	MyList.RemoveAt 5
Eliminar elemento por nombre	El elemento a eliminar de la ArrayList	MyList.Remove «Item3»
Eliminar un rango de elementos	1. Índice – posición inicial.	MyList.RemoveRange 4,3
	2. Count – el número de elementos a eliminar.
Ordenar de forma descendente	Ninguno	MyList.Reverse
Ordenar en orden ascendente	No	MyList.Sort