A Collection is a data type that holds stores objects just like arrays (but different). While writing basic VBA codes, you must have used Workbooks, Worksheets, Ranges etc. They all are collections. In this article, we will learn about VBA collections.
What is a Collection in VBA?
A collection is a data type that stores similar type of objects just like arrays.
But unlike arrays, it's size can be dynamically increased or decreased as per requirement.
You can add, find, fetch and delete items from collections.
Unlike array's you can't change values of items in collections. If you want so, use arrays as item in collection.
A collection has 4 properties or methods associated with it.
Collection.Add Collection.Item Collection.Count Collection.Remove
Through this article, will will explore the use of these properties.
How to Create a Collection in VBA?
A collection is an object hence it is necessary to declare and initialise it. Like any other object in VBA, there are two methods of creating a collection in VBA.
1. Instant Creation
Dim items as new Collection
In above method, the object is instantly created. You can start using this collection for your requirements immediately.
2. Delayed Creation
Dim items as Collection Set items = New Collection
In this method, first we create a reference of collection and when required we initialise with a collection object. Using this method you can choose to create the collection based on some criteria.
Adding Items to a Collection (Collection.Add)
We use Add method to add new items to a collection.
items.Add "Apples"
The above code will add string "Apples" in the collection.
items.Add 1.4
The above code will add double 1.4 to the end of collection.
We can also define keys to these items. These keys can be used to access the items in collection.
Inserting Items in Collection Before or After an Existing Item
The Add method adds new items in the end of the collection.
The add method has four parameters.
Add(item,[Key],[Before],[After])
The first argument is necessary. It is the object or that you want to add in the collection. Rest arguments are optional. We will see the uses of these arguments in this section.
If we want to add some item before or after some perticular index/key, then we use Before and After parameters of Add method.
items.Add True, "b", "cnst" 'Or items.Add True, Before:="cnst"
Both of the lines will insert a boolean value before key "cnst" in the VBA Collection items.
To insert an item after a perticular index/item/key we use After parameter of Add method.
'inserting "Mango" with key "m" after key "cnst" items.Add "Mango", Key:="m", after:="b"
Above line will insert "Mango" with key "m" after key "b".
Note: The keys of items should be unique. Any key that already exist in the collection will not be accepted. VBA will return an error that "the key is already associated with an element of this collection".
We can use this for our advantage get a unique list from collection.
Access Items of Collection (Collection.Item)
There are two methods of accessing the items of a collection.
For example, to access a member in a collection items at index 1 and key "apl", I can use any of below VBA codes.
Debug.Print items.Item(1) -- Debug.Print items(1) -- Debug.Print items.Item("apl") -- Debug.Print items("apl")
How to get count of items in a collection? (Collection.Count)
To get total number of objects or items in a collection, we use .Count function of collection. The .Count method returns total number of items in collection.
items.Count
Above line will give the count of objects the collection has.
How to access all the items of a collection?
The only way to access all the items of the collection in VBA is loops. We iterate through each item of collection to access them. For that we use for loop. The for-each loop is considered fastest and cleanest way to access collection objects.
Looping Through a Collection in VBA
There are various ways to loop through the collection in VBA. We will use most elegant for each loop to iterate through the collection.
Too loop through each item of a collection just use this line of VBA code.
For Each itm In items Debug.Print itm Next itm
Removing Items From a Collection (Collection.Remove)
To remove an item from a vba collection, we can use Remove method. The remove method takes one argument. It can be the index or the key of item (if available).
items.Remove (1) 'using index -- items.Remove 1 -- items.Remove ("apl") 'using key -- items.Remove "apl"
Deleting Collection in VBA
Well, you can't delete a collection in vba. However, you can remove all the items of that collection by setting it again as a new collection.
Set items = New Collection
This line of code will create a new collection on the address of items. Hence our collection will be emptied.
Array vs Collection in VBA
Array |
Collection |
So yeah guys, this is the basics of collections. There are many benefits and uses of VBA collection in office tools. We will explore them together. Till then, if you have any queries regarding collection or anything related to Excel/VBA, put down in the comments section below.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
