Collections in VBA are objects that can store groups of related items, much like an array. Unlike arrays, a single collection can store items of different types because each item in a collection is stored as a Variant. Many developers use collections instead of arrays for storing groups of related items to eliminate the hassles and extra code to set and verify array bounds. This can also result in code that is cleaner and easier to maintain. The following two example subroutines illustrate the differences.
'Example using an Array Private Const ARRAY_START = 5 Public Sub macroArray() Dim i As Long Dim myArray() As String 'Array indexes start at 0, unless explicitly redefined ReDim myArray(1 To ARRAY_START) i = 0 Do While True i = i + 1 If i > UBound(myArray) Then ReDim Preserve myArray(UBound(myArray) + ARRAY_START) End If myArray(i) = "String Item " & CStr(i) If i > 10 Then Exit Do End If Loop ReDim Preserve myArray(i) MsgBox "My Array contains " _ & CStr((UBound(myArray) - LBound(myArray)) + 1) & " items" MsgBox "The first item is: " & myArray(LBound(myArray)) For i = LBound(myArray) To UBound(myArray) Debug.Print myArray(i) Next End Sub 'Same Example using a Collection Public Sub macroCollection() Dim i As Long Dim myCollection As Collection Dim varString As Variant 'Collection indexes always start at 1 Set myCollection = New Collection i = 0 Do While True i = i + 1 myCollection.Add "String Item " & CStr(i) If i > 10 Then Exit Do End If Loop MsgBox "My Collection contains " _ & CStr(myCollection.Count) & " items" MsgBox "The first item is: " & myCollection.Item(1) For Each varString In myCollection Debug.Print varString Next End Sub