VBA Arrays and Collections
Store and manage multiple values efficiently with arrays and collection objects
Quick Reference
Arrays: Store multiple values of same type in indexed list
Collections: Flexible objects that store any data type with keys
Dictionary: Key-value pairs (requires Scripting.Dictionary reference)
ArrayList: Dynamic list with built-in methods
What are Arrays?
Arrays are variables that can store multiple values in a single container, accessed by numeric index. Think of an array as a list of numbered boxes, each holding a value.
❌ Without Arrays:
Dim name1 As String
Dim name2 As String
Dim name3 As String
Dim name4 As String
Dim name5 As String
Messy and unmanageable!
✓ With Arrays:
Dim names(1 To 5) As String
names(1) = "Alice"
names(2) = "Bob"
names(3) = "Charlie"
Clean and scalable!
Static Arrays
Fixed size arrays declared with specific dimensions.
Declaration Syntax:
' Zero-based (default): 0 to 4 = 5 elements
Dim arr(4) As Integer
' One-based: 1 to 5 = 5 elements
Dim arr(1 To 5) As Integer
' Custom range
Dim arr(10 To 20) As String
Basic Example:
Sub StaticArrayExample()
Dim days(1 To 7) As String
' Assign values
days(1) = "Monday"
days(2) = "Tuesday"
days(3) = "Wednesday"
' Continue...
' Access values
MsgBox days(1) ' "Monday"
End Sub
Loop Through Array:
Sub LoopArray()
Dim numbers(1 To 5) As Integer
Dim i As Integer
' Fill array
For i = 1 To 5
numbers(i) = i * 10
Next i
' Print array
For i = LBound(numbers) To UBound(numbers)
Debug.Print numbers(i)
Next i
End Sub
Dynamic Arrays
Arrays that can be resized at runtime with ReDim.
Declaration and Resizing:
Sub DynamicArrayExample()
' Declare without size
Dim arr() As String
' Set initial size
ReDim arr(1 To 3)
arr(1) = "Apple"
arr(2) = "Banana"
' Resize - LOSES DATA!
ReDim arr(1 To 5)
' Resize but keep data
ReDim Preserve arr(1 To 10)
End Sub
ReDim Preserve (Keep Existing Data):
Sub AddToArray()
Dim arr() As String
Dim count As Integer
count = 0
' Add item 1
count = count + 1
ReDim Preserve arr(1 To count)
arr(count) = "First"
' Add item 2
count = count + 1
ReDim Preserve arr(1 To count)
arr(count) = "Second"
End Sub
Note: ReDim Preserve is slow for large arrays. Use Collections for dynamic lists instead!
Multi-Dimensional Arrays
Arrays with 2 or more dimensions (like Excel tables - rows and columns).
2D Array (Table Structure):
Sub TwoDimensionalArray()
' Declare 3 rows x 2 columns
Dim table(1 To 3, 1 To 2) As String
' Fill table
table(1, 1) = "John"
table(1, 2) = "25"
table(2, 1) = "Jane"
table(2, 2) = "30"
' Access: table(row, column)
MsgBox table(1, 1) ' "John"
End Sub
Load Excel Range to Array:
Sub RangeToArray()
' Load entire range into array at once
Dim data As Variant
data = Range("A1:C10").Value
' data is now 2D array (1 To 10, 1 To 3)
MsgBox data(1, 1) ' First row, first col
MsgBox data(5, 2) ' Fifth row, second col
End Sub
Loop Through 2D Array:
Sub Loop2DArray()
Dim data As Variant
Dim i As Long, j As Long
data = Range("A1:C10").Value
' Loop through rows
For i = 1 To UBound(data, 1)
' Loop through columns
For j = 1 To UBound(data, 2)
Debug.Print data(i, j)
Next j
Next i
End Sub
Useful Array Functions
| Function | Description | Example |
|---|---|---|
| LBound | Lowest index | LBound(arr) |
| UBound | Highest index | UBound(arr) |
| Split | String to array | Split("a,b,c", ",") |
| Join | Array to string | Join(arr, ", ") |
| Array | Quick array creation | Array(1, 2, 3) |
| Filter | Filter array | Filter(arr, "test") |
Split and Join Example:
Sub SplitJoinExample()
Dim text As String
Dim words() As String
text = "Apple,Banana,Cherry"
words = Split(text, ",") ' Create array from string
' words(0) = "Apple"
' words(1) = "Banana"
' words(2) = "Cherry"
text = Join(words, " | ") ' Join back with different delimiter
' Result: "Apple | Banana | Cherry"
End Sub
Collection Object
VBA's built-in Collection object - flexible, dynamic, and easy to use.
Basic Collection Operations:
Sub CollectionExample()
Dim myCol As Collection
Set myCol = New Collection
' Add items
myCol.Add "Apple"
myCol.Add "Banana"
myCol.Add 100 ' Can mix types!
' Access by index (1-based)
MsgBox myCol(1) ' "Apple"
' Count items
MsgBox myCol.Count ' 3
' Remove item
myCol.Remove 1 ' Remove first item
End Sub
Collections with Keys:
Sub CollectionWithKeys()
Dim employees As Collection
Set employees = New Collection
' Add with unique keys
employees.Add "John Smith", "EMP001"
employees.Add "Jane Doe", "EMP002"
' Access by key
MsgBox employees("EMP001") ' "John Smith"
' Access by index
MsgBox employees(1) ' "John Smith"
End Sub
Loop Through Collection:
Sub LoopCollection()
Dim col As Collection
Dim item As Variant
Set col = New Collection
col.Add "Red"
col.Add "Green"
col.Add "Blue"
' For Each loop (best way)
For Each item In col
Debug.Print item
Next item
End Sub
When to Use Collections:
- ✓ Don't know size in advance
- ✓ Frequently adding/removing items
- ✓ Need to mix data types
- ✓ Want simple syntax
- ✓ Don't need key-value lookups
Dictionary Object
Scripting.Dictionary provides key-value pairs with fast lookups and advanced features.
Setup Required: Tools → References → Check "Microsoft Scripting Runtime"
Basic Dictionary Usage:
Sub DictionaryExample()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
' Add key-value pairs
dict("name") = "John"
dict("age") = 25
dict("city") = "New York"
' Access by key
MsgBox dict("name") ' "John"
' Check if key exists
If dict.Exists("age") Then
MsgBox "Age is: " & dict("age")
End If
' Remove item
dict.Remove "city"
End Sub
Count Unique Values:
Sub CountUnique()
Dim dict As Object
Dim cell As Range
Set dict = CreateObject("Scripting.Dictionary")
' Count occurrences
For Each cell In Range("A1:A100")
If dict.Exists(cell.Value) Then
dict(cell.Value) = dict(cell.Value) + 1
Else
dict(cell.Value) = 1
End If
Next cell
MsgBox "Unique values: " & dict.Count
End Sub
Loop Through Dictionary:
Sub LoopDictionary()
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict("A") = 1
dict("B") = 2
dict("C") = 3
' Loop through keys
For Each key In dict.Keys
Debug.Print key & " = " & dict(key)
Next key
End Sub
Best Practices
✓
Use LBound and UBound: Never assume array bounds start at 0 or 1
✓
Prefer Collections over ReDim Preserve: Much faster for dynamic lists
✓
Use Dictionary for lookups: Faster than looping arrays for searches
✓
Load ranges to arrays: Read Excel data to array once, process in memory
✓
Declare correct data types: Variant for mixed data, specific types for performance
✓
Clean up objects: Set collections and dictionaries to Nothing when done
Choosing the Right Structure
| Feature | Array | Collection | Dictionary |
|---|---|---|---|
| Fixed Size | Yes (Static) | No | No |
| Mixed Types | Variant only | Yes | Yes |
| Key Access | No | Optional | Yes |
| Performance | Fastest | Medium | Fast lookups |
| Add/Remove | Hard | Easy | Easy |
| Use When | Fixed data, speed critical | Simple lists, dynamic | Key-value pairs, lookups |