VBA Collection en Excel — la lista ordenada que crece (y por qué no es un Dictionary)

TL;DR — Una Collection es una lista ordenada integrada que haces crecer con .Add y encoger con .Remove — sin ReDim, sin adivinar el tamaño por adelantado. Recurre a ella cuando estés acumulando un número desconocido de elementos en orden y no necesites buscarlos por clave. Cuatro reglas la mantienen honesta: es base 1 (coll(1) es el primer elemento), no puedes sobrescribir un elemento (solo Add / Remove), una clave duplicada lanza el error 457 y no hay .Exists — lo que la convertiría en un Dictionary.

Dim jobs As Collection
Set jobs = New Collection        ' Set + New: una Collection es un objeto

jobs.Add "Export report"         ' crece bajo demanda — sin tamaño declarado
jobs.Add "Email the team"
jobs.Add "Archive last month"

Debug.Print jobs.Count           ' -> 3
Debug.Print jobs(1)              ' -> Export report   (¡base 1!)

For Each task In jobs            ' recorre en orden de inserción
    Debug.Print task
Next task

Cuando no sabes de antemano cuántas cosas vas a recopilar —cada fila que no pasa la validación, cada cliente único que te encuentras, cada archivo de una carpeta— una matriz resulta incómoda. Tendrías que adivinar un tamaño e ir haciendo ReDim Preserve sobre la marcha. Una Collection es la respuesta de VBA: un objeto que empieza vacío y crece de un .Add en .Add, en el orden en que fuiste añadiendo. Es uno de los objetos más útiles del lenguaje, y también uno de los que más se usan mal sin ruido, porque parece una matriz y no se comporta ni como una matriz ni como un Dictionary.

Lo que aprenderás

El modelo mental — una Collection es una lista ordenada, no una matriz indexada
Por qué es base 1, y cómo se esconde ese desfase por uno
La trampa de la sobrescritura: coll(2) = "x" no edita — da error
Cómo funcionan las claves, por qué los duplicados lanzan el error 457 y el .Exists que falta
Una regla de decisión: Collection frente a Array frente a Dictionary

El modelo mental: una lista ordenada que crece, no una estantería numerada

Una matriz es una estantería numerada: declaras Dim a(1 To 100), cada casilla existe desde el principio y llegas a la casilla i directamente. Una Collection es un montón de fichas al que sigues añadiendo: empieza vacío, cada .Add deja una ficha nueva sobre el montón en orden, y .Count crece a medida que avanzas. Puedes leer la ficha n-ésima y puedes sacar una, pero no puedes «asignar a la casilla 5» — no hay una casilla 5 fija, solo la quinta ficha que hay ahora mismo en el montón.

Esa única distinción explica todas las reglas de abajo. Como es una lista que crece y no una estantería fija, las posiciones no son direcciones estables — son solo el lugar actual del elemento en la fila, y ese lugar cambia cuando añades o quitas cosas.

La regla que causa el primer error: una Collection es base 1

Las matrices en VBA son base 0 por defecto (a(0) es el primer elemento). Una Collection es base 1. El primer elemento es coll(1); coll(0) lanza «Llamada a procedimiento o argumento no válidos» (error 5).

Dim c As Collection
Set c = New Collection
c.Add "first"
c.Add "second"

Debug.Print c(1)        ' -> first
Debug.Print c(0)        ' -> error en tiempo de ejecución 5

Donde más muerde es al mezclar las dos en una misma macro — lees una matriz con arr(0) y una colección con coll(1) en el mismo bucle, y un desfase por uno está casi garantizado. La regla se memoriza sin más: las matrices empiezan en 0, las Collections empiezan en 1. Cuando recorres una Collection por índice, siempre es For i = 1 To coll.Count.

La regla que sorprende a quien viene de las matrices: no puedes sobrescribir un elemento

Con una matriz, a(2) = "new" reemplaza el valor de la casilla 2. Con una Collection, la misma idea es un error:

Dim c As Collection
Set c = New Collection
c.Add "old"

c(1) = "new"            ' error de compilación o en tiempo de ejecución — un elemento de Collection es de solo lectura por índice

Una Collection expone .Item como de solo lectura. Puedes recuperar c(1), pero no puedes asignarle. Para «cambiar» un elemento lo quitas y añades un reemplazo, usando los argumentos Before / After de .Add para controlar dónde cae el nuevo:

c.Remove 1              ' saca el elemento viejo de la posición 1
c.Add "new", Before:=1  ' vuelve a poner el reemplazo en el mismo sitio

Si te descubres haciendo esto a menudo, es señal de que la Collection es la herramienta equivocada — seguramente quieres una matriz (para ediciones por posición) o un Dictionary (para editar por clave). Una Collection da lo mejor de sí cuando los elementos entran y salen, pero no se reescriben en su sitio.

La regla sobre las claves: cadenas únicas, error 457 en los duplicados y ningún Exists

.Add acepta una clave opcional — una cadena que luego puedes usar en lugar de una posición numérica:

Dim prices As Collection
Set prices = New Collection
prices.Add 9.99, "apple"      ' primero el valor, luego la clave
prices.Add 4.5, "pear"

Debug.Print prices("apple")   ' -> 9.99   (búsqueda por clave)

Esto parece un Dictionary, y ese parecido es una trampa. Dos límites duros:

Las claves deben ser únicas. Añade un segundo elemento con una clave existente y obtendrás «Esta clave ya está asociada con un elemento de esta colección» (error 457). No hay «añadir o actualizar» — una clave repetida es un error sin remedio.
No existe .Exists. Una Collection no te da ninguna forma limpia de preguntar «¿ya existe esta clave?». Tus únicas opciones son feas: envolver la búsqueda en On Error Resume Next y comprobar Err.Number, o atrapar el error 457 en el .Add.

' El incómodo baile de «¿existe esta clave?» que te obliga a hacer una Collection
Dim v As Variant
On Error Resume Next
v = prices("banana")
Dim found As Boolean
found = (Err.Number = 0)
On Error GoTo 0

Ese .Exists que falta es la razón más clara para preferir un Dictionary siempre que las claves importen: un Scripting.Dictionary tiene .Exists(key), te deja sobrescribir por clave y no lanza error ante una repetición. Mira VBA Dictionary para la estructura centrada en claves que una Collection solo imita.

La regla para encoger una Collection: quita hacia atrás

Como las posiciones cambian, quitar elementos por índice dentro de un bucle hacia delante se salta elementos — el mismo fallo que tienes al eliminar filas o al usar For Each sobre una colección que cambia. Quita el elemento 2, y el antiguo elemento 3 se desliza a la posición 2, pero tu bucle ya ha pasado al índice 3:

' ROTO — los índices se desplazan según quitas
Dim i As Long
For i = 1 To c.Count
    If c(i) = "drop" Then c.Remove i   ' se salta el elemento que subió
Next i

' CORRECTO — cuenta hacia atrás para que las eliminaciones no alteren lo que queda
For i = c.Count To 1 Step -1
    If c(i) = "drop" Then c.Remove i
Next i

Es la misma regla de iteración inversa de VBA For Loop, y la razón por la que no puedes eliminar con seguridad dentro de un recorrido VBA For Each. Siempre que la pertenencia encoja durante un bucle, cuenta hacia atrás.

Cuándo usar una Collection, un Array o un Dictionary

Las tres guardan un grupo de cosas. Son buenas para trabajos distintos:

Array — un conjunto de tamaño fijo y numerado con el que harás trabajo posicional o numérico (cálculos, acceso tipo matriz, leer un Range entero de una vez). Lo más rápido para trabajo numérico masivo; incómodo cuando no se conoce la cantidad. Mira VBA Array.
Collection — una lista ordenada de longitud desconocida que construyes añadiendo y lees en orden. Perfecta cuando acumulas «todos los elementos que coinciden» y los recorrerás una vez. Sin búsqueda por clave en la que confiar, sin ediciones en el sitio.
Dictionary — un almacén clave → valor para cuando necesitas búsqueda rápida, eliminar duplicados o «¿he visto esto antes?». Su .Exists y la sobrescritura por clave son justo lo que le falta a una Collection. Mira VBA Dictionary.

La señal rápida: añadir elementos en orden → Collection; buscarlos por un nombre o eliminar duplicados → Dictionary; cálculo numérico de tamaño fijo → Array.

Cómo ayuda ExcelMaster

Elegir el contenedor correcto —y acordarte de que una Collection es base 1, no se puede sobrescribir y no tiene Exists— es la clase de decisión de bajo nivel que consume tiempo antes de que te acerques siquiera al resultado que de verdad querías.

ExcelMaster te deja saltártelo. Describe el resultado —«lista cada cliente de la columna C que aparezca más de una vez» o «reúne todas las facturas vencidas en una hoja de resumen»— y escribe y ejecuta la lógica, eligiendo la estructura correcta (un conjunto para eliminar duplicados, una lista ordenada para acumular) sin que tú sopeses Collection frente a Dictionary a mano. Cuando mantienes una macro programada seguirás eligiendo el contenedor tú mismo; para el caso puntual de cada día, enunciar el objetivo es más rápido que acertar a mano la estructura de datos.

Preguntas frecuentes

¿Cómo añado elementos a una VBA Collection?

Créala con Set c = New Collection y luego llama a c.Add value. La lista crece automáticamente — sin tamaño que declarar. Opcionalmente puedes pasar una clave (c.Add value, "mykey") para buscar después, y Before / After para controlar la posición de inserción. Recupera los elementos con c(1) (base 1) o c("mykey"), y cuéntalos con c.Count.

¿Una VBA Collection es base 0 o base 1?

Base 1. El primer elemento es c(1) y el último c(c.Count); c(0) lanza el error en tiempo de ejecución 5. Es lo contrario de las matrices de VBA, que son base 0 por defecto — una fuente habitual de errores de desfase por uno cuando usas ambas en una misma macro.

¿Cuál es la diferencia entre una Collection y un Dictionary en VBA?

Una Collection es una lista ordenada optimizada para añadir y leer en orden; un Scripting.Dictionary es un almacén clave → valor optimizado para la búsqueda. Las diferencias decisivas: un Dictionary tiene .Exists(key) y te deja sobrescribir un valor por clave, mientras que una Collection no tiene ninguna de las dos — añadir una clave duplicada lanza el error 457, y los elementos son de solo lectura por índice. Usa un Dictionary siempre que importen las claves o la eliminación de duplicados.

¿Por qué me sale el error 457 al añadir a una Collection?

El error 457 —«Esta clave ya está asociada con un elemento de esta colección»— significa que llamaste a .Add value, key con una clave que ya existe. Las claves de una Collection deben ser únicas, y no hay forma integrada de comprobarlo antes. O bien llevas las claves aparte, o atrapas el error, o usas un Scripting.Dictionary, cuya comprobación .Exists evita el problema por completo.

¿Cómo compruebo si una clave existe en una Collection?

No hay forma limpia — esa es la mayor debilidad de la Collection. Tienes que envolver la búsqueda en On Error Resume Next, intentar c(key) e inspeccionar Err.Number (0 significa que existe). Si necesitas esta comprobación a menudo, cámbiate a un Scripting.Dictionary y usa d.Exists(key).

Probado en

Probado en: Excel 365 (Windows 11), VBA 7.1 — última verificación el 01/08/2026.

Guías relacionadas: VBA Dictionary · VBA Array · VBA For Each · VBA For Loop · VBA With