Collection Object in VBA Excel – debugvba.com

vba excel collections

Objective

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  –

  1. Add
  2. Item
  3. Remove
  4. Count


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.

 

vba excel collection add item

 

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.

 

vba excel add item collection

 

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

 

vba excel collection add item before

 

(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

 

vba excel add item

 

(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.

 

vba excel collection remove

 

(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.

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object

Post you may like

Dictionary in VBA Excel