VBA Collection in Excel — The Ordered, Growable List (and Why It Isn't a Dictionary)

TL;DR — A Collection is a built-in, ordered list you grow with .Add and shrink with .Remove — no ReDim, no guessing the size in advance. Reach for it when you're accumulating an unknown number of items in order and you don't need to look them up by key. Four rules keep it honest: it's 1-based (coll(1) is the first item), you can't overwrite an item (only Add / Remove), a duplicate key throws error 457, and there's no .Exists — the thing that would make it a Dictionary.

Dim jobs As Collection
Set jobs = New Collection        ' Set + New: a Collection is an object

jobs.Add "Export report"         ' grows on demand — no size declared
jobs.Add "Email the team"
jobs.Add "Archive last month"

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

For Each task In jobs            ' walks in insertion order
    Debug.Print task
Next task

When you don't know up front how many things you'll collect — every row that fails validation, every unique customer you encounter, every file in a folder — an array is awkward. You'd have to guess a size and ReDim Preserve as you go. A Collection is VBA's answer: an object that starts empty and grows one .Add at a time, in the order you added things. It's one of the most useful objects in the language, and also one of the most quietly mis-used, because it looks like an array and behaves like neither an array nor a Dictionary.

What you'll learn

The mental model — a Collection is an ordered list, not an indexed array
Why it's 1-based, and how that fencepost bug hides
The overwrite trap: coll(2) = "x" doesn't edit — it errors
How keys work, why duplicates throw error 457, and the missing .Exists
A decision rule: Collection vs Array vs Dictionary

The mental model: an ordered list you grow, not a numbered shelf

An array is a numbered shelf: you declare Dim a(1 To 100), every slot exists from the start, and you reach slot i directly. A Collection is a stack of cards you keep adding to: it starts empty, each .Add drops a new card on the pile in order, and .Count grows as you go. You can read the n-th card and you can pull one out, but you can't "assign to slot 5" — there is no fixed slot 5, just the fifth card currently in the pile.

That single distinction explains every rule below. Because it's a growing list and not a fixed shelf, positions aren't stable addresses — they're just the item's current place in line, and that place shifts when you add or remove things.

The rule that causes the first bug: a Collection is 1-based

Arrays in VBA are 0-based by default (a(0) is the first element). A Collection is 1-based. The first item is coll(1); coll(0) throws "Invalid procedure call or argument" (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)        ' -> run-time error 5

This bites hardest when you mix the two in one macro — read an array with arr(0) and a collection with coll(1) in the same loop, and an off-by-one is almost guaranteed. The rule is simply memorised: arrays start at 0, Collections start at 1. When you loop a Collection by index, it's always For i = 1 To coll.Count.

The rule that surprises array users: you can't overwrite an item

With an array, a(2) = "new" replaces the value in slot 2. With a Collection, the same idea is an error:

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

c(1) = "new"            ' Compile/run-time error — a Collection item is read-only by index

A Collection exposes .Item as read-only. You can retrieve c(1), but you can't assign to it. To "change" an item you remove it and add a replacement, using .Add's Before / After arguments to control where the new one lands:

c.Remove 1              ' take out the old item at position 1
c.Add "new", Before:=1  ' put the replacement back in the same spot

If you find yourself doing this often, that's a signal the Collection is the wrong tool — you probably want an array (for positional edits) or a Dictionary (for edit-by-key). A Collection is at its best when items go in and come out but don't get rewritten in place.

The rule about keys: unique strings, error 457 on duplicates, and no Exists

.Add takes an optional key — a string you can later use instead of a numeric position:

Dim prices As Collection
Set prices = New Collection
prices.Add 9.99, "apple"      ' value first, then key
prices.Add 4.5, "pear"

Debug.Print prices("apple")   ' -> 9.99   (lookup by key)

This looks like a Dictionary, and that resemblance is a trap. Two hard limits:

Keys must be unique. Add a second item with an existing key and you get "This key is already associated with an element of this collection" (error 457). There is no "add or update" — a repeat key is a hard error.
There is no .Exists. A Collection gives you no clean way to ask "does this key already exist?" Your only options are ugly: wrap the lookup in On Error Resume Next and check Err.Number, or catch error 457 on the .Add.

' The awkward "does this key exist?" dance a Collection forces on you
Dim v As Variant
On Error Resume Next
v = prices("banana")
Dim found As Boolean
found = (Err.Number = 0)
On Error GoTo 0

That missing .Exists is the single clearest reason to prefer a Dictionary whenever keys matter: a Scripting.Dictionary has .Exists(key), lets you overwrite by key, and won't throw on a repeat. See VBA Dictionary for the key-first structure a Collection only imitates.

The rule for shrinking a Collection: remove backwards

Because positions shift, removing items by index inside a forward loop skips elements — the same failure you get deleting rows or using For Each on a changing collection. Remove item 2, and the old item 3 slides into position 2, but your loop has already moved on to index 3:

' BROKEN — indices shift as you remove
Dim i As Long
For i = 1 To c.Count
    If c(i) = "drop" Then c.Remove i   ' skips the item that slid up
Next i

' CORRECT — count down so removals don't disturb what's left
For i = c.Count To 1 Step -1
    If c(i) = "drop" Then c.Remove i
Next i

This is the same reverse-iteration rule from VBA For Loop, and the reason you can't safely delete inside a VBA For Each walk. Any time membership shrinks during a loop, count down.

When to use a Collection vs an Array vs a Dictionary

All three hold a group of things. They're good at different jobs:

Array — a fixed-size, numbered set you'll do positional or numeric work on (math, matrix-style access, reading a whole Range at once). Fastest for bulk numeric work; awkward when the count is unknown. See VBA Array.
Collection — an ordered list of unknown length you build by appending and read in order. Perfect when you're accumulating "all the items that match" and will loop them once. No key lookup worth relying on, no in-place edits.
Dictionary — a key → value store when you need fast lookup, de-duplication, or "have I seen this before?" Its .Exists and overwrite-by-key are exactly what a Collection lacks. See VBA Dictionary.

The quick tell: appending items in order → Collection; looking them up by a name or de-duplicating → Dictionary; fixed-size numeric crunching → Array.

How ExcelMaster helps

Picking the right container — and remembering that a Collection is 1-based, can't be overwritten, and has no Exists — is the kind of low-level decision that soaks up time before you get anywhere near the actual result you wanted.

ExcelMaster lets you skip it. Describe the outcome — "list every customer in column C that appears more than once" or "collect all the overdue invoices into a summary sheet" — and it writes and runs the logic, choosing the right structure (a set for de-duplication, an ordered list for accumulation) without you weighing Collection against Dictionary by hand. When you're maintaining a scheduled macro you'll still pick the container yourself; for the everyday one-off, stating the goal is faster than getting the data structure right by hand.

Frequently asked questions

How do I add items to a VBA Collection?

Create it with Set c = New Collection, then call c.Add value. The list grows automatically — no size to declare. You can optionally pass a key (c.Add value, "mykey") for later lookup, and Before / After to control the insert position. Read items back with c(1) (1-based) or c("mykey"), and count them with c.Count.

Is a VBA Collection 0-based or 1-based?

1-based. The first item is c(1) and the last is c(c.Count); c(0) throws run-time error 5. This is the opposite of VBA arrays, which are 0-based by default — a common source of off-by-one bugs when you use both in one macro.

What is the difference between a Collection and a Dictionary in VBA?

A Collection is an ordered list optimised for appending and reading in order; a Scripting.Dictionary is a key → value store optimised for lookup. The decisive differences: a Dictionary has .Exists(key) and lets you overwrite a value by key, while a Collection has neither — adding a duplicate key throws error 457, and items are read-only by index. Use a Dictionary whenever keys or de-duplication matter.

Why do I get error 457 adding to a Collection?

Error 457 — "This key is already associated with an element of this collection" — means you called .Add value, key with a key that already exists. Collection keys must be unique, and there's no built-in way to check first. Either track keys separately, catch the error, or use a Scripting.Dictionary, whose .Exists check avoids the problem entirely.

How do I check whether a key exists in a Collection?

There's no clean way — that's the Collection's biggest weakness. You have to wrap the lookup in On Error Resume Next, attempt c(key), and inspect Err.Number (0 means it exists). If you need this check regularly, switch to a Scripting.Dictionary and use d.Exists(key).

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-08-01.

Related guides: VBA Dictionary · VBA Array · VBA For Each · VBA For Loop · VBA With