VBA Collection

Excel VBA Collection Object

We have seen the VBA Dictionary and VBA Array both have a similar connection with VBA Collection. In VBA Collection, we can create our own collection of group and provide the item and key to get the value stored under it. Whereas in VBA Dictionary we used to create the dictionary itself in VBA, which create our own dictionary which has word and value definition as normal dictionary has. And same in VBA Array also, where we used to create a collection object with limited size.

The advantage of VBA Collection is that we don’t need to extend the size of any object which we define here. Whatever object is created in VBA Collection does not have any limitations. And we don’t have to change the size of any object as if we think it requires.

Along with the creation of Add Collection object, we can use Count, Item and Remove collection objects as well.

How to Create Collection Object in Excel VBA?

Below are the different examples to create collection object in excel using VBA code.

Excel VBA Collection – Example #1

In this example, we will see how to create an Object Collection in VBA. For this, follow the below steps:

Step 1: Go to the VBA window, under the Insert menu tab select Module as shown below.

Step 2: Write the subcategory of VBA Collection in the same name or we can choose any other name as per our convenience.

Code:

Sub Excel_Collection1()

End Sub

Step 3: Now define a variable as Collection in any name as shown below.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection

End Sub

We can choose any word, text or name of defining a variable, but it is recommended to define that in the process of performing function first.

Step 4: Set the defined variable as New Collection. This will help in activating and setting the already defined object as Collection to New Collection.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

End Sub

Step 5: We have seen in the description of different collection objects as Add, Item, Count and Remove. Now we will use all of these objects for storing new content. First, to add, we will assign the ADD to defined variable ColObject.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add

End Sub

Step 6: Now under ITEM, we will choose a sequence number that can be anything. Here, we are selecting it as 1.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1,

End Sub

Step 7: In the Syntax of VBA Collection, we need to enter the KEY for which we can assign the ITEM. Here also we can choose anything to be added in VBA repository. We have considered the word Newton here.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"

End Sub

Step 8: We can skip the Before and After portion of Syntax. Now after that, we will assign the Key to a Collection object variable.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"
ColResult = ColObject("Newton")

End Sub

Step 9: Use a message box to see the value stored in the Collection object variable.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"
ColResult = ColObject("Newton")
MsgBox ColResult

End Sub

Step 10: Now compile the code and run it by clicking on the Play button located below the menu bar.

We will get the message as 1. This means that for key Newton, the Item is located at 1^st position.

Excel VBA Collection- Example #2

There is another method or way by which we can see what item number is assigned to what Key. We can also count the number of keys filled in VBA Collection object. This process is quite similar to the process we have seen in example-1. For this, follow the below steps:

Step 1: Write the subcategory of VBA Collection as shown below.

Code:

Sub Excel_Collection2()

End Sub

Step 2: Consider the same variable which we have seen in example-1 as Collection and set it as New Collection as ColObject.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

End Sub

ColObject is the only variable here in this example by that help we will be seeing the count or sequence of define Item and Key.

Step 3: Now to the defined Collection object assign the ADD function and choose any value as per your choice. Here we are selecting 10.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10

End Sub

Step 4: Similarly, add 2 or 3 more Collection Objects as shown below.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

End Sub

Step 5: To know the count of items used in Collection Object, we will use Debug print.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

Debug.Print

End Sub

Major plus point of using Debug print here instead of the Message box is that we would be able to get the count along with the error if there is any. For any error we will get an output as Zero and whereas for the correct process we will get the actual value.

Step 6: To get the count of variable ColObject, we will use Count function which is the part of an inbuilt function in the list.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

Debug.Print ColObject.Count

End Sub

Step 7: From the View menu option, select the Immediate window as shown below. Or we can use a short cut key as Ctrl + G to get this window.

This is the place where we will be seeing the output using Debug print.

Step 8: Now compile the code and run it by clicking on the Play button located below the menu bar. We will see, in the immediate window, the count of total object collections is coming as 3.

Now to test, whether the obtained count is correct or not, we will delete one row of collection object or convert that into text by placing apostrophe ( ‘ ) and again run the code.

We will get the updated count as 2 which is the count of collection object 10 and 20 Item number. And we can notice that 3^rd line of collection object is colored as Green and this is the sign of getting code converted into text.

Pros of Excel VBA Collection

• We don’t need to resize the object we defined.
• We can use different Collection objects which are inbuilt in excel VBA Collection. These objects are not seen in other function such as VBA Dictionary or VBA Array.
• By VBA Collection, we can any type of customer or product database as well.

Things to Remember

• It is recommended to use Debug print instead of Message Box. By this, we would be able to see the error and rectify it.
• By VBA Collection, we can various types of objects and add any size of data.
• In Item object function, we can define any number as per our choice. It can be product code, sequence or price which we want to see.
• Saving the file in Macro enable excel will allow us to retain the code further.

Recommended Articles

This is a guide to VBA Collection. Here we discuss how to create a Collection object in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Array Length
2. VBA RGB
3. VBA Cells
4. VBA XML