Collections 101-Data Storage

A Collection is what some computer science books refer to as a dictionary, that is, a table of items in which you can look up an entry by using a key. Collections start out simple, but they can easily become complicated. Let’s briefly run through the features of Collections by looking at a simple one:

Dim animals As New Collection

Here’s the code to load a Collection with strings—which are actually converted to Variants because that’s the only data type Collections recognize:

‘ Create collection
animals.Add “Lion”
animals.Add “Tiger”
animals.Add “Bear”
animals.Add “Shrew”, , 1
animals.Add “Weasel”, , , 1

The first three Add methods put Lion, Tiger, and Bear in the Collection in that order, as items 1, 2, and 3. The Collection class is one-based, and new items go at the end unless you specify otherwise. The fourth Add method uses the third parameter to specify that the new item will precede existing item 1. The last Add method uses the fourth parameter to specify that the new item will follow existing item 1.

Once you have a Collection, you’ll want to access its members. You can use indexes, just as you do for an array:

Debug.Print animals(3) & “ “ & animals.Item(3)

Notice that animals(3) is a shortcut for animals.Item(3). Item is the default member and can be omitted.

You might also want to replace one item of a Collection with another.

animals(2) = “Wolverine”

If you think this should work with a simple assignment, think again. Visual Basic could have made this work. (It was requested.) Look back at the implementation of Item in the CVector or CList class to see how easy it would have been to add assignment to the Collection class. All you have to do is implement the Item property with Get, Let, and Set procedures. Instead, they implemented Item as a function, and of course you can’t Let or Set a function. As a result, the only way to replace an item is to delete the old item and insert a new one in its place:

animals.Add “Wolverine”, , 2
animals.Remove 3

Here’s the standard way to iterate through a Collection:

Dim vAnimal As Variant
For Each vAnimal In animals
    s = s & Space$(4) & vAnimal & sCrLf
    Debug.Print vAnimal
Next

In the previous edition of this book, I suggested replacing items with the following code:

animals.Remove 2
animals.Add “Wolverine”, , 2

Hardcore reader Ralf Kretzschmar pointed out that this syntax fails if you’re trying to replace the last item. Here’s a procedure that always gets the syntax right regardless of whether you specify the item to be replaced by index or by key:

Sub CollectionReplace(n As Collection, vIndex As Variant, _
                      vVal As Variant)
    If VarType(vIndex) = vbString Then
        n.Remove vIndex
        n.Add vVal, vIndex
    Else
        n.Add vVal, , vIndex
        n.Remove vIndex + 1
    End If
End Sub

Of course, you can still iterate through a Collection by index:

Dim i As Integer
For i = 1 To animals.Count
    s = s & Space$(4) & animals(i) & sCrLf
    Debug.Print animals(i)
Next

This technique is risky, however. It’s easy to confuse the indexing system because the Collection class is one-based, while many of the predefined Visual Basic collections (Forms and Controls) are zero-based for compatibility with previous versions. Furthermore, as you might have discovered with ListBox controls, you can’t delete members from or add members to a collection while iterating because this changes the index and the count, which often causes the loop to fail. Yet this is one of the most frequent reasons to iterate. You can sometimes get around the limitation by iterating backward, but For Each is a more reliable looping technique.

If you want to remove everything from a Collection, you have to do it in a loop:

Do While animals.Count
    animals.Remove 1
Loop

It would be handy if the Collection class had a Clear method like the CList class does, but no such luck. You can also clear a Collection by setting it to Nothing, but then you’ll have to Set it to a New Collection before using it again.