VBA: Collection

Creating a collection

In order to use a collection, it needs to be created with the new operator.

dim coll as collection
set coll = new collection

Basic collection operations: Add, access and remove items

After creating a collection, items can be added to the end of the collection with its coll.add(elem) method.

The nth item in a collection can be accessed with coll(n). Note that unlike in C and C++ and many other programming languages, the element-index of a VBA-collection is not zero based.

The nth item of a collection is removed with coll.remove(n).

The number of elements in the collection is returned by coll.count.

These basic operations are summarized in the following simple VBA function:

option explicit

sub main () ' {

    dim coll as collection
    dim elem as variant

    set coll = new collection

    coll.add("one"  )
    coll.add("two"  )
    coll.add("three")
    coll.add("four" )
    coll.add("five" )
    coll.add("six"  )

    debug.print("number of items in coll: " & coll.count)
  '
  ' 6

    debug.print(coll(3))
  '
  ' "three"

    debug.print("going to remove element 2 and 4")

    coll.remove(2)
    coll.remove(4) ' removes element "five" because this is the 4th element after removing the 2nd element

    debug.print("number of items in coll: " & coll.count)
  '
  ' 4

    debug.print(coll(4))
  '
  ' "six"

end sub ' }

Github repository about-VBA, path: /objects/collection/add-and-remove-items.bas

for each … in

The for each … in statement allows to iterate over the items in a collection.

option explicit

sub main()

    dim c as new collection

    c.add "foo"
    c.add "bar"
    c.add "baz"

    dim i as variant ' With option explicit, i must be declared.

    for each i in c
        debug.print i
    next

end sub

Github repository about-VBA, path: /objects/collection/for-each-in.bas

Indexing items with a key

Items can be added with an associated key. The key is then used to find the item in the collection.

option explicit

dim c as new collection

sub main() ' {

    addItems
    printKeyVal "car"
    printKeyVal "city"
    printKeyVal "fruit"

end sub ' }

sub addItems() ' {

    c.add "Apple"    , "fruit"
    c.add "Mercedes" , "car"
    c.add "New York" , "city"

end sub ' }

sub printKeyVal(key_ as string) ' {
    debug.print key_ & " = " & c(key_)
end sub ' }

Github repository about-VBA, path: /objects/collection/key-value.bas

Misc

The IID probably is {A4C46780-499F-101B-BB78-00AA00383CBB}. The corresponding type lib (for version 2.1) is VEN2232.OLB.