To showcase the basic concepts of Collection object in VBA Excel and also demonstrate the methods associated with collection object.
What is a Collection object ?
Collection is a VBA object which can contain multiple items. Difference between array and collection is that, collection can contain items of different data types, while in array data type of all the items should be same. Most common examples of collections are workbooks, worksheets, cells, shapes, charts. Primary advantage of collection over array is that you don’t need to declare the size in case of collection, but for array you need to declare the size.
In the below statement we can see how to declare the collection –
Dim SampleCollection As New Collection
Collection subscript starts at 1 not from 0. Collection object has 4 methods associated with it –
Add – This method is used to add the items in the collection
Remove – To remove the items from the collection
Count – To count the number of items in collection
Item – To access a particular item in collection
(1) Add Method
In the below shown code, we have created a collection called SampleCity and we have added name of the cities in this collection. Name of these cities are stored in Cell A1 to A4. In this code we are adding the item in the collection by using For loop, and in the next line we are printing the items.
Sub AddIteminCollection() Dim SampleCity As Collection Set SampleCity = New Collection For Rownum = 1 To Range("A1").End(xlDown).Row 'Add item in collection SampleCity.Add Cells(Rownum, 1).Value 'Print the city added in collection Debug.Print SampleCity(Rownum) Next Rownum End Sub
We have data in excel cells as shown in the image below.
We have stored the data from excel cells to collection items using the code above.We can see the output in the immediate window ,which we can see in the screenshot below.
Add Item Before and After
To add the item before a specific item as shown in the code below, we mention the position parameter for before/after. We can see in the code below that first we have added an item (“NYC”) at first position, which places it at first position, now collection is (“NYC”). Then we add another item “LA” before first position (“i.e. before “NYC”). So now the collection looks like (“LA”, “NYC”)
In the last we have added “SF” after second position (which is “NYC” here) , so final collection looks like (“LA”,”NYC”, “SF”).
Sub AddIteminBeforeAfter() Dim SampleCollection As Collection Set SampleCollection = New Collection 'Add items in a collection SampleCollection.Add "NYC" Debug.Print "First Item is " & SampleCollection(1) 'Add another item before first item SampleCollection.Add "LA", before:=1 Debug.Print "" Debug.Print "First Item is " & SampleCollection(1) Debug.Print "Second Item is " & SampleCollection(2) 'Add another item after second item Debug.Print "" SampleCollection.Add "SF", After:=2 Debug.Print "First Item is " & SampleCollection(1) Debug.Print "Second Item is " & SampleCollection(2) Debug.Print "Third Item is " & SampleCollection(3) End Sub
(2) Item Method
We use item method of the collection object to access a particular item. In the code below we can see that we have added 2 city codes in the collection called “SampleCollection”, then we have printed each item by using debug.print method . We can see in the image below, that we have accessed item 1 and item 2 using item method.
Sub ItemMethodCollection() Dim SampleCollection As New Collection 'Add items in a collection SampleCollection.Add "NYC" SampleCollection.Add "LA" 'Print first and second item in collection Debug.Print "First Item in collection is = " & SampleCollection.Item(1) Debug.Print "Second Item in collection is = " & SampleCollection.Item(2) End Sub
(3) Remove Method
We use the remove method to remove the items from collection. Once we remove an item from collection then index changes for items which exist after the recently deleted items. For example, if we delete 4th item, then 5th item will become 4th item, 6th item will become 5th and so on. Due to this reason we have applied the for loop in reverse order. We can see in the code below that number of item before executing the code were 4, while the number of items after deletion are 0 , which means all the items have been deleted.
Sub CollectionRemoveMethod() Dim SampleCollection As New Collection Dim NumOfItems As Integer 'Add items in a collection SampleCollection.Add "NYC" SampleCollection.Add "LA" SampleCollection.Add "SF" SampleCollection.Add "DC" 'Find number of items in a collection NumOfItems = SampleCollection.Count 'Print Number of collection items before removing the collection item Debug.Print "Number of items before removing collection items = " & SampleCollection.Count 'Access all the items in collection For CollectionIndex = NumOfItems To 1 Step -1 SampleCollection.Remove CollectionIndex Next CollectionIndex 'Print all the items in collection Debug.Print "Number of items after removing collection items = " & SampleCollection.Count End Sub
In the image below we can see that there are zero items left , after executing this code. We can see the bottom part of this image for results n immediate window.
(4) Count Method
This method is used to find the number of items which exist in the collection. We can see in the code above how we can use count method with a collection. We can use the count method like shown below. Here we are finding the count of items in collection called “SampleCollectionName”.
NumOfItems = SampleCollectionName.Count
Loop through Collection Items
Here first we have added items in the “SampleCollection”, then we have loop through each item in collection using For loop.
Sub CollectionMethodForLoop() Dim SampleCollection As New Collection Dim objCollection 'Add items in a collection SampleCollection.Add "NYC" SampleCollection.Add "LA" SampleCollection.Add "SF" SampleCollection.Add "DC" 'Access all the items in collection For Each objCollection In SampleCollection Debug.Print objCollection Next objCollection End Sub
We can see in the image below all the items added in the collection can be printed using for loop, results can be seen in immediate window as shown below.
In this post we have gone through basics of collection objects and its associated methods.You can get more details from the link provided in reference section below.
Post you may like