VBA Arrays and Collections: Managing Lists of Data in Code

When you need to work with a list of values in VBA — a set of IDs to process, a lookup table cached in memory, a collection of objects — you have several options: arrays, Collections, and Dictionaries. Each has different strengths. Knowing which to use and when will make your VBA code significantly cleaner and faster.

Arrays

An array is the most basic data structure: a fixed-size list of values of the same type, accessed by index.

Declaring Arrays

' Fixed-size array (indices 0 to 9)
Dim names(9) As String

' Fixed-size array with explicit bounds
Dim scores(1 To 10) As Integer

' Multi-dimensional array (3 rows, 4 columns)
Dim grid(1 To 3, 1 To 4) As Double

Dynamic Arrays

When you do not know the size at declaration time, use a dynamic array and ReDim:

Dim ids() As Long
Dim count As Integer

' Later, once you know the size:
count = DCount("*", "Orders", "Status='Pending'")
ReDim ids(1 To count)

Use ReDim Preserve to resize while keeping existing values:

ReDim Preserve ids(1 To count + 1)
ids(count + 1) = newID

Iterating Arrays

Dim i As Integer
For i = LBound(names) To UBound(names)
    Debug.Print names(i)
Next i

LBound and UBound return the lower and upper bounds — always use these instead of hard-coding 0 or the array size.

Useful Array Functions

' Join array elements into a string
Dim csv As String
csv = Join(names, ", ")   ' "Alice, Bob, Carol"

' Split a string into an array
Dim parts() As String
parts = Split("Alice,Bob,Carol", ",")

' Check if a value is in an array (no built-in — use a loop or Filter)
Dim matches() As String
matches = Filter(names, "Alice")  ' Returns array of elements containing "Alice"
If UBound(matches) >= 0 Then MsgBox "Found!"

Collections

A Collection is a dynamic, ordered list that can hold any type of value (or object). Unlike arrays, you do not need to know the size in advance, and you can add and remove items freely.

Dim col As New Collection

' Add items
col.Add "Alice"
col.Add "Bob"
col.Add "Carol"

' Add with a key (for lookup by key)
col.Add "[email protected]", "Alice"

' Access by index (1-based)
Debug.Print col(1)   ' "Alice"

' Access by key
Debug.Print col("Alice")   ' "[email protected]"

' Remove by index or key
col.Remove 1
col.Remove "Alice"

' Count
Debug.Print col.Count

' Iterate
Dim item As Variant
For Each item In col
    Debug.Print item
Next item

Limitation: Collections do not support checking whether a key exists without error handling:

Function KeyExists(col As Collection, key As String) As Boolean
    On Error Resume Next
    Dim v As Variant
    v = col(key)
    KeyExists = (Err.Number = 0)
    Err.Clear
End Function

Scripting.Dictionary

The Dictionary object (from the Microsoft Scripting Runtime) is like a Collection but with better key management — you can check for key existence, get all keys or values as arrays, and overwrite values by key.

' Add reference: Tools → References → Microsoft Scripting Runtime
Dim dict As New Scripting.Dictionary

' Add key-value pairs
dict.Add "Alice", 42
dict.Add "Bob", 17
dict.Add "Carol", 99

' Check if key exists
If dict.Exists("Alice") Then
    Debug.Print dict("Alice")   ' 42
End If

' Update a value
dict("Alice") = 50

' Get all keys
Dim keys() As Variant
keys = dict.Keys

' Get all values
Dim vals() As Variant
vals = dict.Items

' Remove a key
dict.Remove "Bob"

' Count
Debug.Print dict.Count

' Iterate
Dim k As Variant
For Each k In dict.Keys
    Debug.Print k & " = " & dict(k)
Next k

Practical Example: Caching a Lookup Table

Instead of calling DLookup() hundreds of times in a loop (slow), load the lookup table into a Dictionary once:

Function LoadCategoryNames() As Scripting.Dictionary
    Dim dict As New Scripting.Dictionary
    Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT CategoryID, CategoryName FROM Categories")
    Do While Not rs.EOF
        dict.Add CStr(rs!CategoryID), rs!CategoryName
        rs.MoveNext
    Loop
    rs.Close
    
    Set LoadCategoryNames = dict
End Function

' Usage:
Dim cats As Scripting.Dictionary
Set cats = LoadCategoryNames()

' Now look up category names instantly, no database hit:
Debug.Print cats("5")   ' "Electronics"

This pattern can speed up loops that process thousands of records by orders of magnitude.

When to Use Each

Structure	Best for
Array	Fixed-size lists, numeric indexing, passing to functions
Collection	Dynamic lists, simple ordered sets, object collections
Dictionary	Key-value lookups, checking existence, caching data

Conclusion

Arrays, Collections, and Dictionaries are fundamental VBA tools that every Access developer should know. The Dictionary pattern for caching lookup data is particularly valuable — it is one of the most effective performance optimizations available in VBA, turning hundreds of database round-trips into a single query and instant in-memory lookups.