By Ken Getz and Mike Gilbert
Sooner or later you'll need to manage a group of custom objects created with VBA class modules, and the VBA Collection object is the perfect vehicle for doing this. We introduced Collections in the Premiere issue of this magazine, and demonstrated how to use the Collection object to store other objects. And while this technique is useful, it does have some limitations. In this article, we'll discuss those limitations, and show you how to overcome them by using - you guessed it - class modules.
So, what's the problem with the Collection object? It seems to store objects very well. In fact, it works a bit too well. The biggest problem with the Collection object is that it can store references to any data type, e.g. Integer, String, Long, Variant, etc. (see FIGURE 1).
Of course, it is much simpler to manipulate objects in a Collection if you can count on each object being of the same type and having the same properties and methods. For example, as part of this month's sample Excel VBA project, we created a custom File class to represent a disk file. The File class has several properties, including Path, Size, and ShortName. If you created a Collection of File objects, you would expect every object in the Collection to have those properties. And you could use code like this with no fear of adverse consequences:
' Print the size of the first file in the Collection.
Debug.Print colFiles.Item(1).SizeBut what happens if the first object in the colFiles Collection isn't a File? What if it's a Form or Control object? What if it isn't an object at all? What if it's an Integer? VBA will, of course, generate a run-time error when it attempts to execute the code.
FIGURE 1 (on the left): The VBA Collection object can accommodate any type of object.
FIGURE 2 (on the right): Creating a Collection class guards against unwanted objects in the collection.
A Collection class acts as a filter for the Collection object, restricting the type of object you can store in it (see FIGURE 2). By interacting with the Collection class in your application, rather than the Collection object itself, you guard against objects that don't fit your expectations. Creating and using Collection classes allows you to extend the properties and methods of the Collection object. (We'll demonstrate an example of this later.)
To illustrate a Collection class, as well as how to create one, we'll use an Excel 97 project with a collection of files created by scanning a disk directory. FIGURE 3 shows a form that populates the list box with file information stored in a custom Collection class after you select a path.
The form uses a Collection class called Files to populate the list box. When creating a Collection class, begin by adding a new class module to your VBA project, then declare a Private Collection object in the new module's declaration section. Here's the declaration from the Files class module:
' Collection of files.
Private pcolFiles As New CollectionYou must declare the Collection object as Private to "insulate" it from the outside world and prevent the rest of your program from filling it with garbage.
Of course, once you've declared the Collection object as Private, there's no way for any process to add or remove items from it. Therefore, the next step is to replicate the standard methods of the Collection object. While this might sound like a lot of extra work, it's also where the "filtering" we mentioned earlier comes in.
Remember the built-in Collection object has an Add method that accepts an object reference and a unique, alphanumeric identifier. If your application was using a Collection object directly, it would likely create a new instance of an object and add it to the Collection itself:
' Create a new instance of an object.
Dim objFile As New File
objFile.Path = "C:\AUTOEXEC.BAT"
' Add to a Collection object.
colFiles.Add objFile, objFile.ShortNameWith a Collection class, the application calls the Add method of the class, passing any required information. Contrast the previous code with the Add method of the Files class:
Public Function Add(Path As String) As File
Dim objFile As File
' Create the new File object.
Set objFile = New File
objFile.Path = Path
' Add it to the Private collection.
pcolFiles.Add objFile, objFile.ShortName
' Return a pointer to the new object.
Set Add = objFile
End FunctionIn this example, the object creation and addition to the Collection takes place inside the Add method; and the class retains complete control. Any required information, such as the file's path, is supplied as an argument to the method. The code called by the application to add a file to the Collection can then be simplified to:
' Add a file to the collection.
colFiles.Add "C:\AUTOEXEC.BAT"In addition to the Add method, the Collection class should also implement the Item and Remove methods, as well as a Count property:
Public Function Item(Key As Variant) As File
' Return an item in the collection.
Set Item = pcolFiles.Item(Key)
End Function
Public Sub Remove(Key As Variant)
' Remove an item from the collection.
pcolFiles.Remove Key
End Sub
Property Get Count() As Long
' Return the number of items.
Count = pcolFiles.Count
End PropertyNote that we've omitted error handling in these methods - something you should never do! Always include an error handler that, at a minimum, passes errors to the calling procedure by using the Raise method of the Err object.
FIGURE 3: This form illustrates a Collection class by displaying file information.
' Private variable to store path.
Private pstrPath As String
Property Get Path() As String
' Return stored path value.
Path = pstrPath
End Property
Property Let Path(strPath As String)
Dim strFile As String
' Clear the collection.
Set pcolFiles = New Collection
' Make sure there's a backslash.
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
' Get the first file.
strFile = Dir(strPath & "*.*", _
vbReadOnly Or vbHidden Or vbArchive Or vbSystem)
Do Until Len(strFile) = 0
' Add it to the collection.
Call Add(strPath & strFile)
' Get the next file.
strFile = Dir()
Loop
' Save the path.
pstrPath = strPath
End PropertyFIGURE 4: A Path property is added to the Collection class. Set this property and the class scans the directory and adds each file it finds to the private Collection object.
At this point, you could now use the Files class by declaring an instance of it and repeatedly calling its Add method, once for each file found by the Dir function. But that's not what our example does. Why not? One of the advantages of using a Collection class is that you can extend its functionality by adding more properties and methods; you're not limited to Add, Remove, Item, and Count.
In the case of our Files class, doesn't it make more sense to put the code that scans a directory inside the class itself, rather than in each application that uses the class? That's one of the guiding principles of object-oriented design: put the code closest to where it's needed.
To illustrate this concept, we added a Path property to the Collection class. When you set this property, the class scans the directory and adds each file it finds to the private Collection object (see FIGURE 4).
The Property Let procedure is triggered when a process changes the Path property of the class. In our example, this happens after you select a path from the browse dialog box. Here's the simple code that accomplishes the task of filling the Collection with a list of files:
' Reinitialize the collection.
Set mobjFiles = New Files
' Set the path property.
mobjFiles.Path = strPathThat's all there is to it! Once the Path property is set, the Files class populates its own Collection and makes it available to the application. This is yet another example where the "guts" of the process lies within the class itself. (You could argue that the class no longer needs its Add and Remove methods. In some applications this could well be true, but we've chosen to leave them as part of the class for illustrative purposes.)
Life with VBA Collection classes isn't all wine and roses. There are two things that you give up when using a Collection class instead of the Collection object. The first is the Collection object's default method, Item. A default method allows you to omit the word "Item" from your code. For example, the following two statements are identical, assuming colFiles refers to a Collection object:
Debug.Print colFiles.Item(1).Size
Debug.Print colFiles(1).SizeUnless you're using Visual Basic 5.0, there is no way to designate a method as the default method for a class. Therefore, you must always call the Item method explicitly.
The second major drawback to Collection classes is that you cannot create an enumeration function. An enumeration class is what makes For Each loops work. If you want to iterate through every item in the Collection you must do it the old fashioned way, using the Count property and a For Next loop. For example, this code populates the list box:
' Fill the list box with info.
lstFiles.Clear
For lngCount = 1 To mobjFiles.Count
With mobjFiles.Item(lngCount)
lstFiles.AddItem .ShortName & _
Space(12 - Len(.ShortName)) & _
vbTab & .AttributeString & _
vbTab & .Size
End With
NextNote how the procedure uses the counter variable, lngCount, to loop from 1 to the number of items in the Collection. The With statement uses the Item method to reference each object in the Collection.
Note that Visual Basic 5.0 users can overcome this limitation by creating an enumeration function. Search for the keyword "enumeration" in Visual Basic Books Online for more information.
Collection classes are extremely versatile. Not only do they protect your delicate Collections (especially important when they'll be used by others), but by adding additional properties and methods you can create extremely functional classes. In future issues, we'll show you more examples of using Collection classes. Stay tuned!
The files referenced in this article are available for download from the Informant Web site at http://www.informant.com/mod/modnewupl.htm. File name: MOD9711MG.ZIP.
Ken Getz and Mike Gilbert are Senior Consultants with MCW Technologies, a Microsoft Solution Provider focusing on Visual Basic and the Office and BackOffice suites. They've recently completed VBA Developer's Handbook and Access 97 Developer's Handbook (co-authored with Paul Litwin), both for SYBEX.