February 1999

Create VBA Object Collections

by Michael C. Amundsen

Visual Basic for Applications offers a special construct called the Object collection. A collection is a set of related objects that you can access as if they were in an array. VBA includes several built-in collections that let you access application objects. For example, Excel has a Worksheets collection; Access has a Tables collection; and Microsoft Project has a Tasks collection. Collection objects also provide a great way to manage related items in your code. It's much easier to write code that deals with a collection than it is to write code that must address each individual object. Even better, members of the collection need not be of the same data type. The only real relation they must share is that they all belong to the same collection.

Although each VBA host platform has a set of built-in collections, you can create your own collection objects as well. In this article, we'll show you how to use Access VBA to create a collection and manipulate its members.

Collections versus arrays

If you think VBA collections are equivalent to the old BASIC arrays, and you're correct--but not entirely. Although collections and arrays have a great deal in common, collections offer additional features that make them much more valuable than simple data arrays. First, it's difficult to build data arrays that contain different data types. While you can use VBA to create an array that holds either Variant data types or objects, you can't create a single array that holds both Variant data and objects. Collections, on the other hand, let you combine data types with ease. Second, creating BASIC arrays usually means you have to pre-declare the array size.

In contrast, using collections frees you from the task of managing the item count. When you create a collection, you don't need to indicate a pre-defined size--the collection construct automatically keeps track of the current count and updates the counter each time you add or remove an item. Add to this freedom the ability to collect objects and Variant data in the same group, and you have a flexible and powerful programming tool to include in your arsenal.

Collection basics

Creating and managing collections is easy. Each collection has one property (Count) and three methods (Add, Item, and Remove). To create a collection, simply declare a new program variable as a type collection, as follows:


Dim CollNames as New Collection

Note the New keyword, which creates an instance of the generic VBA collection type. Once you've created the collection, you can easily add items using the Add method:


CollNames.Add Item:="Mike", Key:="Mike"

At the time you set the Item value, you can also add an optional Key value, as we've done. This value will serve as a lookup string when you search the collection.

You access collection items (also called members) using either the familiar numeric index pointer or the optional Key value, as follows:


MsgBox CollNames.Item(1)
MsgBox CollNames.Item("Mike")
MsgBox CollNames(2)
MsgBox CollNames("Fred")

Notice that the last two lines omit the Item keyword. The Item keyword is the default property of a collection object. If you omit it, VBA assumes you're asking for an item from the collection. Finally, removing an item from the collection is also a simple task:


CollNames.Remove("Bertha")
CollNames.Remove(13)

When you remove an item, VBA automatically reindexes all members of the collection. For example, if you have a collection with three members (Shannon, Dana, and Jesse), the following line of code will return the Dana member:

MsgBox CollNames.Item(2) `returns "Dana"

However, suppose you execute a Remove method on the first member, as follows:


CollNames.Remove("Shannon")

The original code now gives different results:


MsgBox CollNames.Item(2) `returns "Jesse"

For this reason, it's a good idea to set the optional Key values when you create a collection and to use those values when accessing members. Now, let's use Microsoft Access to create, list, search, and empty a collection.

Creating a collection in Access

To begin, start Microsoft Access, create a new database (VBA9901.MDB), and insert a new form. (Click New, then choose Design View to open a blank form.) To this form, add four command buttons labeled Build Collection, Show Collection, Clear Collection, and Search Collection. Figure A shows the four buttons' placement on the form.

Figure A: Create this form in your Access database. Download Access data into Excel without MS Query

Before you add code in the event procedures behind the buttons, you need to add a variable declaration to the form's general declarations. This form-level variable will hold the new collection that the event procedures will manipulate. Select View/Code from the menu and add the following code to the general declarations:


Option Compare Database
Option Explicit

Dim CollNames As New Collection

Now, select the Build Collection button and locate its On Click event in the properties box. Click the down arrow to activate the event procedure, and click the É button to open the event code window; then add the code shown in Listing A.

Listing A


Private Sub Command0_Click()
` build collection
On Error GoTo Command0Err
Dim strName As String
Dim strMsg As String

strMsg = "Enter a Name or <RETURN> to end."
Do
  strName = InputBox(strMsg, "Build Collection")
  ` add to collection, if not empty
  If Trim(strName) <> "" Then
     CollNames.Add Item:=strName, KEY:=strName
  End If

Loop Until Trim(strName) = "" ` back for more?
Exit Sub

Command0Err:
MsgBox Error$
End Sub

This code fills the collection with a list of names you enter. Notice that there's no way for the program to know in advance how many members you'll add to the collection. In fact, you could add a few members, exit the routine by leaving the name blank, and then click the button and add more members. The only restriction to adding members is that the Key value must be unique for each item in the list. If you try to add an item with a duplicate Key value, you'll get an error from VBA. For this reason, the routine includes an error trap.

Next, open the code window for the Show Collection button's On Click event and enter the code from Listing B. This code displays the collection members in a message box.

Listing B


Private Sub Command1_Click()
Dim strMsg As String
Dim vntName As Variant

strMsg = "" ` initial value

For Each vntName In CollNames
  strMsg = strMsg & vntName & Chr(13)
Next

MsgBox strMsg ` show results
End Sub

Note the use of the For Each...Next construct to walk through the collection. (For more information on For Each...Next and other loop structures, see this month's article Unravel Loop Structures In VBA.)

At this point, you add the code from Listing C, to the Clear Collection button's On Click event, in order to clear the collection.

Listing C


Private Sub Command2_Click()
Dim intLoop As Integer

For intLoop = 1 To CollNames.Count
  collNames.Remove intLoop
Next

MsgBox "Collection has been Cleared."
End Sub

This code contains a standard For...Next loop, which shows that you can also use the collection's Count property to determine the loop's topmost value.

You'll notice that the collection counter starts at 1--not 0, as in most other built-in VBA collections. This indexing inconsistency can cause real headaches when you're coding!

Now you're ready to add the code behind the Search Collection button. This code will ask the user to enter a Key value or index number to use in locating the collection member. Add the code from Listing D to the button's On Click event procedure.

Listing D


Private Sub Command3_Click()
On Error GoTo Command3Err
Dim vntSearch As Variant ` input return
Dim strFound As String ` search return
Dim strMsg As String ` input prompt

strMsg = "Enter a Key Value or Index:"
vntSearch = InputBox(strMsg, "Search Collection")

If IsNumeric(vntSearch) <> 0 Then
  strFound = CollNames(Int(vntSearch))
Else
  strFound = CollNames(vntSearch)
  If Trim(strFound) = "" Then
     strFound = "Unable to find Key value."
  End If
End If

MsgBox strFound
Exit Sub

Command3Err:
MsgBox Error$
End Sub

This code provides a good example of how to write procedures that accept either numeric or string data as input and return the same result.

You need to add the error-trapping to this routine because VBA will return an error condition if your search entry doesn't exist in the collection. The error message--Invalid procedure call--is rather unhelpful. In this example, we simply echo the VBA error to the user, but in a production application you'll want to replace this message with one that's more useful.

Go ahead and compile the code (Debug/Compile All Modules) and save the form as frmCollections. You can now try your collection routines.

Testing the collection routines

Run the form by double-clicking on the frmCollection item in the forms list. Then click the Build Collection button and add members to your collection using the resulting dialog box, as shown in Figure B.

Figure B: Use this dialog box to add members to the collection.

After you've added several members, click Show Collection to display your list, as shown in Figure C.

Figure C: The Show Collection button displays your collection members.

You can search the list using the Key values or by simply entering an index number. When you're done with the collection, click Clear Collection to remove all the members.