Managing Collections in Visual Basic

Visual Basic collection is a data sets similar to Array designed to hold dynamically changing data. Unlike arrays collection doesn't need to be resized to add or remove values.

Collection is a reference type and it is required to use new keyword to initiate the collection

Dim coll As New Collection

Adding items

In order to add new item to the collection in Visual Basic it is required to use Add method of Collection object. This method has 4 parameters

item - element to add to collection. Item can be of any type.
(optional)key - String key value to associate the element with. Reference
(optional)before - 1-based index of the element in the collection where to insert this element before Reference
(optional)after - 1-based index of the element in the collection where to insert this element after Reference

Pushing item

Dim coll As New Collection
coll.Add "New Value"

Calling Add method of Collection object will push the element at the end of the collection, i.e. the new element will be inserted as the last element.

Inserting item at position

Calling Add method of Collection object and specifying the integer values in 3rd or 4th parameter will insert the element in the specified position.

Dim coll As New Collection
coll.Add "A",,<Insert Element Before This Index>
coll.Add "B",,,<Insert Element After This Index>

Accessing items

Similar to array, elements in the collection can be accessed by index

Unlike default behavior of arrays collections elements are 1-based indexed, i.e. first element's index is 1.

Element can be accessed using the () symbol either directly from the variable or via Item method

Debug.Print coll.Item(<Index Of Element>)
Debug.Print coll(<Index Of Element>)

Sub AddInsertItems()

    Dim coll As Collection
    Set coll = New Collection
    
    coll.Add "A"
    coll.Add "D"
    coll.Add "B", , , 1 'insert after first element
    coll.Add "C", , 3 'insert before 3rd element
    
    Dim i As Integer
    
    'A B C D
    For i = 1 To coll.Count() 'collection is 1-base indexed
        Debug.Print coll.Item(i)
    Next
    
End Sub

Indexing items by keys

Elements inserted to the collection can be associated with unique string key.

Dim coll As New Collection
coll.Add "A", "key1"
coll.Add "B", "key2"

Unlike elements keys must be unique in the collection otherwise the error will be displayed

Run-time error '457': The key is already associated with an element of this collection

Elements in the collection can be accessed by key (similar to the way they accessed by index)

Debug.Print coll.Item("<Key Name>")

Sub AddItemsWithKeys()

    Dim mathConstColl As Collection
    Set mathConstColl = New Collection
    
    mathConstColl.Add 3.14, "PI" 'number PI
    mathConstColl.Add 9.8, "G" 'gravitational constant
    mathConstColl.Add 2.71, "e" 'Euler's number
    
    Dim i As Integer
    
    'traverse all
    For i = 1 To mathConstColl.Count()
        Debug.Print mathConstColl(i) 'item can be accessed directly without the Item property
    Next
    
    'Access values by key
    Debug.Print mathConstColl("PI")
    Debug.Print mathConstColl("G")
    Debug.Print mathConstColl("e")
    
    mathConstColl.Remove 1
    mathConstColl.Remove "e"

End Sub

Removing items

Item can be dynamically removed from the collection using Remove method. It is possible to use either index or key to specify which item should be removed.

coll.Remove(<Index of Element>)
coll.Remove("<Key of Element>")

Sub RemoveItems()
    
    Dim coll As Collection
    Set coll = New Collection
    
    'items are stored as variant, so data types can be mixed
    coll.Add "A"
    coll.Add 1, "IntegerItem"
    coll.Add 10.5
    coll.Add True, "BoolItem"
    
    coll.Remove 1 'removing first item by index
    coll.Remove "BoolItem" 'removing item by key
    
    Dim i As Integer
    
    '1 10.5
    For i = 1 To coll.Count()
        Debug.Print coll(i)
    Next
    
End Sub