One of the important data types available in VBA is ‘Collection’ which allows the storage and manipulation of a group of objects. In this blog post, we will discuss the syntax, storage, and range of a VBA ‘Collection’ data type. We will also provide five examples of VBA codes that utilize the ‘Collection’ data type.
Introduction to ‘Collection’ in VBA DataType
Syntax of VBA Collection
A collection in VBA is declared using the following syntax:
Dim collectionName As Collection
The keyword ‘Dim’ is used to declare a variable and ‘As Collection’ specifies the data type of the variable. The name of the collection can be anything the user desires, however, it is recommended to use a descriptive name.
Storage of VBA Collection
A collection in VBA can store a group of objects such as integers, strings, dates, or even other collections. Objects in a collection are stored in a sequential manner and can be accessed using an index number. The index of the first object in a collection is 1, the second object is 2, and so on. A collection can hold a maximum of approximately 16,000 objects.
Range of VBA Collection
Unlike an array, a collection in VBA can expand to accommodate new objects as needed. This means that the size of a collection is not fixed and can be dynamically changed during the execution of the code. Additionally, a collection can be both indexed and associative. This means that objects can be accessed using either an index number or a key value assigned to each object.
Example VBA Codes using ‘Collection’ data type
Example 1: Add Items to a Collection
Sub AddToCollection() Dim myCollection As Collection Set myCollection = New Collection 'Add items to the collection myCollection.Add "Apple", "Fruit" myCollection.Add "Orange", "Fruit" myCollection.Add "Banana", "Fruit" myCollection.Add "Carrot", "Vegetable" End Sub
In this example, we declare a collection called ‘myCollection’ and use the ‘Add’ method to add items to it. The first argument represents the object being added and the second argument is its associated key value. In this case, we are adding four items (fruits and vegetables) to the collection.
Example 2: Loop through a Collection using Index
Sub LoopThroughCollection() Dim myCollection As Collection Set myCollection = New Collection 'Add items to the collection myCollection.Add "John", "Name" myCollection.Add "Jane", "Name" myCollection.Add "Eric", "Name" 'Loop through the collection For i = 1 To myCollection.Count Debug.Print myCollection(i) Next i End Sub
This code snippet shows how to loop through a collection using its index numbers. The ‘Count’ property is used to determine the number of items in the collection and the ‘For’ loop is used to iterate through each item. In this example, we print the names of three people stored in the collection.
Example 3: Loop through a Collection using Key Value
Sub LoopThroughCollection() Dim myCollection As Collection Set myCollection = New Collection 'Add items to the collection myCollection.Add 10, "Number" myCollection.Add 20, "Number" myCollection.Add 30, "Number" 'Loop through the collection For Each key In myCollection Debug.Print myCollection(key) Next key End Sub
In this example, we use the ‘For Each’ loop to iterate through a collection using its key values. The ‘Debug.Print’ statement is used to print the items of the collection on the immediate window. In this code, we have added three numbers and used their associated key values to access them.
Example 4: Check if a Collection Contains a Specific Key Value
Sub CheckCollection() Dim myCollection As Collection Set myCollection = New Collection 'Add items to the collection myCollection.Add "Red", "Color" myCollection.Add "Blue", "Color" myCollection.Add "Green", "Color" 'Check if the collection contains a specific key value If myCollection.Contains("Color") Then MsgBox "Collection contains color values." Else MsgBox "Collection does not contain color values." End If End Sub
This code snippet shows how to use the ‘Contains’ method to check if a collection contains a specific key value. If the collection contains the specified key value, it will return ‘True’ and execute the first message box. Otherwise, it will return ‘False’ and execute the second message box.
Example 5: Remove an Item from a Collection
Sub RemoveFromCollection() Dim myCollection As Collection Set myCollection = New Collection 'Add items to the collection myCollection.Add "Michael", "Name" myCollection.Add "Lisa", "Name" myCollection.Add "Tom", "Name" 'Remove an item from the collection myCollection.Remove "Name" End Sub
In this final example, we use the ‘Remove’ method to, well, remove an item from a collection. The argument passed in the method is the key value of the object we want to remove. In this code, we remove the key value ‘Name’ and its associated object from the collection.
In Conclusion
VBA ‘Collection’ data type is a powerful tool that allows the storage and manipulation of a group of objects. With its dynamic size and ability to be both indexed and associative, collections can be a useful asset in your VBA coding. We have provided examples of VBA codes that demonstrate the usage of ‘Collection’ data type. Experiment with these codes and see how you can incorporate collections into your own VBA projects.