لطفا منتظر باشید ...
Working with Custom Collections
In addition to the collections built into the Access and other object libraries, you can create custom collections. Custom collections are similar to arrays, but they offer several advantages:
- Collections are dynamically allocated. They take up memory based only on what's in them at a given time. This is different from arrays, whose size must be either predefined or re-dimensioned at runtime. When you re-dimension an array, Access actually makes a copy of the array in memory, taking up substantial resources. By using custom collections, you can avoid that.
- A collection always knows how many elements it has, and elements can easily be added and removed.
- Each element of a collection can contain a different type of data.
- Elements can be added into any element of a collection.
Although collections are very powerful and provide several advantages, it is important that you be aware of their disadvantages, which are as follows:
- Every item in a collection is stored as a variant.
- Although the capability to store a different type of data in each element of a collection can be an advantage, it can also be a disadvantage. If you attempt to treat each item in the collection the same (for example, by accessing the same property in each element of the collection), your code might render an error.
You might wonder why collections are covered in this section. A common use of collections is to house instances of custom objects. An example of such a use is covered in the section of this chapter titled "Using a Collection to Manipulate Multiple Instances of the FileInformation Class."
Creating a Custom Collection
Defining a custom collection is easysimply use the Dim keyword to create an object of the type Collection, as shown here:Dim colSports As New Collection
The Dim statement tells the compiler you want to declare a variable, and the As New keywords indicate that you're creating a new instance of something. Specifically, you're creating a new instance of a Collection object. Let's take a look at how you can add items to and remove items from a custom collection.
Adding Items to a Custom Collection
The Add method adds a new item to a custom collection. It looks like this:colSports.Add "Basketball"
This line of code adds the text "Basketball" to the colSports collection. The Add method has three optional arguments: Key, Before, and After. Key is a string name you can use to uniquely identify an element; the Before and After arguments enable you to specify where in the collection the new item will be placed. Here's an example:Sub NewCollection()
Dim colSports As New Collection
colSports.Add "Basketball"
colSports.Add "Skiing"
colSports.Add "Skating", Before:=1
colSports.Add "Hockey", After:=2
End Sub
This code creates a new collection called colSports and adds two consecutive elements to the collection: Basketball and Skiing. It then adds Skating before Basketball. Skating becomes Element 1 and Basketball becomes Element 2. Finally, it adds Hockey after Element 2 (Basketball).CAUTIONUnlike almost every other array or collection in VBA, custom collections are one-based, rather than zero-based. This means that the first element is numbered one (rather than zero), the second element is number two, and so on. This is a big change if you're used to thinking of arrays and collections as being only zero-based.
Looping Through the Elements of a Custom Collection
Just as you can loop through built-in collections, you can also loop through a custom collection. The code looks like this:Sub LoopThroughCollection()
Dim colSports As New Collection
Dim varSport As Variant
colSports.Add "Basketball"
colSports.Add "Skiing"
colSports.Add "Skating", Before:=1
colSports.Add "Hockey", After:=2
For Each varSport In colSports
Debug.Print varSport
Next varSport
End Sub
This code uses a For Each...Next loop to loop through each element of colSports. Notice that the routine declares a variant variable as the type of object in the collection. This is done so that different types of values can be stored in each object in the collection.
Referencing Items in a Custom Collection
When you add an item to a collection, you can specify a custom key for the object. This makes it easy to return to the item in the collection whenever necessary. The following code illustrates how to specify a custom key:Sub CustomKey()
Dim colSports As New Collection
colSports.Add "Basketball", "B"
colSports.Add "Skiing", "S1"
colSports.Add "Skating", "S2"
colSports.Add "Hockey", "H"
Debug.Print colSports.Item("S1")
End Sub
This code adds several items to the colSports collection. As the code adds each item, it assigns the item a unique key. You can easily access each item in the collection using its unique key. You will often use the Item method when adding several instances of a form, such as a Customer form to a collection. The customer ID of each customer is added as the unique key for each form in the collection. This unique identifier enables you to readily return to a specific instance of the Customer form.
Removing Items from a Custom Collection
Removing items from a custom collection is just as easy as adding them. You use the Remove method, which looks like this:Sub RemoveElements()
Dim colSports As New Collection
colSports.Add "Basketball"
colSports.Add "Skiing"
colSports.Add "Skating"
colSports.Add "Hockey"
colSports.Remove 2
End Sub
This routine removes Element 2 (Skiing) from the collection.
