• Tips

Collections and Arrays

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

Tags: , , ,