VBA Arrays and Collections: Managing Lists of Data in Code
Learn how to use arrays, Collections, and Dictionaries in Access VBA to manage lists of data efficiently without hitting the database for every operation.
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 "alice@example.com", "Alice"
' Access by index (1-based)
Debug.Print col(1) ' "Alice"
' Access by key
Debug.Print col("Alice") ' "alice@example.com"
' 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.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Working with complex data structures in Access VBA? Schedule a free call and get expert help optimizing your code.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
VBA File Operations: Read, Write, and Manage Files from Access
Access VBA can read and write text files, check if files exist, copy and move files, and automate file management tasks. Here is the complete guide.
Customizing the Access Ribbon with XML
The Access ribbon can be customized with XML to add your own buttons, groups, and tabs — creating a professional, branded interface for your application.
Automating Excel from Access VBA: Export, Format, and Send
Access VBA can create, populate, format, and save Excel workbooks automatically. Learn how to build powerful Excel automation workflows from your database.