Collection Classes: Security Packaging for VBA Collection Objects (I)

You will sooner or later manage a set of custom objects created by VBA class modules. The VBA Collection object is the ideal tool for this purpose. We introduced Collections in the journal's Premiere journal and explained how to use the Collection object to store other objects. Although this technology is very useful, it does have some limitations. In this article, we discuss these limitations and show you how to overcome these limitations by using (you guess what) class modules.

Problems with Collections

What's wrong with the Collections object? It seems that the storage object is stored very well. In fact, it works really well. The biggest problem with the Collections object is that it can store references to any data type, such as Integer, String, Long, Variant, etc. (see Figure 1).

Of course, if you can guarantee that each object has the same type, and have the same properties and methods, then processing objects in the collection becomes very simple. For example, as part of this month's sample Excel VBA project, we create a custom File class to represent a disk file. This File class has several properties, including P ath, Size, and ShortName. If you create a Collection of File objects, you will want each object in this Collection to have these properties. You can use code similar to the following without any objections:

` Print the size of the first file in the Collection.
Debug.Print colFiles.Item(1).Size

However, if the first object in the colFiles Collection is not File, what will happen? What if it were a Form or Control object? If it is not an object at all? Of course, VBA will generate runtime errors when it tries to execute the code.

Figure 1 (Left) VBA Collection Object Can Hold Any Type of Object

Figure 2 (right) Create a Collection class to prevent unwanted objects from entering the collection

Enter the Collection class

The Collection class can act as a filter for the Collection object, limiting the types of objects you can store inside (see Figure 2). By interacting with the Collection class in your application (the second is not the Collection object itself), you can prevent the entry of unwanted objects. Creating and using the Collection class allows you to extend the properties and methods of the Collection object (more on this later).

To illustrate the Collection class and how to create a Collection class, we will use an Excel 97 project with a collection of files created by scanning the disk directory. Figure 3 shows a form that uses the file information stored in the custom Collection class after selecting the path to make the list box popular.

This form uses a Collection class called Files to popularize the list box. When creating a Collection, start by adding a new class module to your VBA project and then declare a Private Collection object in the declaration section of the new module. Here is the declaration from the Files class module:

` Collection of files.
Private pcolFiles As New Collection

To "insert" the Collection object from the outside world and prevent other parts of the program from filling it with useless stuff, you must declare the Collection object as Private.