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

EnvisionCAD

Since 1996, EnvisionCAD has been a nationally recognized leader in the configuration, customization, implementation, training & support for CAD software solutions. Our individualized approach has benefited private engineering firms and government agencies alike. Basic or advanced, we can help you get the most from your CAD technology.

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *