How to use collection in Excel VBA Easily (6 Examples)

Welcome to Excel Avon

Collection in Excel VBA?

DOWNLOAD USED EXCEL FILE FROM HERE>>

Today’s article is about Collection in Excel VBA, today we are going to understand how to use Collection in Excel VBA, Collection is an object contains group of objects having similar characteristics (with same properties and methods). VBA Collection is very similar to the “VBA Dictionary. One can use collections to store object or Data. Use collection in Excel VBA

The advantage of use Collection in Excel VBA 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.

 

Use Collection in Excel VBA?

we have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below. 

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

To store data or Object in collection in VBA, we will write the subroutine for Collection in VBA after inserting the module in VBE.

Sub UseCollection()

End Sub

We can create Collection by 2 ways first way variable define for ‘Instant Collection Creation.

Sub UseCollection()
'Instant Collection creation
Dim Col as New Collection
End Sub

This is 2nd way (Delay Collection Creation) 

Sub UseCollection()
'Delay Collection creation
Dim Col as Collection
End Sub

Set the Defined variable as New Collection.

Sub UseCollection()
'Delay Collection creation
Dim Col as Collection

Set Col = New Collection
End Sub

Under the “Add” method, we have specific parameters. we are storing some data or object.

Sub UseCollection()
'Delay Collection creation
Dim Col as Collection

Set Col = New Collection

Col.add = "Cat"
Col.add = "Dog"
Col.add 1212
End Sub

Now we count the items of the collection using col.count, we print the result using debug. Print

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212 
debug. Print Col.Count
End Sub

Click the run Button

As you can see after running the code, we get 3 in the result, we have stored 3 objects.

Now we can print stored item Using index No. OF item using Col.item, we are print the 3rd item.

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection 

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212 

debug. Print Col.Item(3) 
End Sub

Click the run Button

As you can see after running the code, when we print the 3rd item, we get ‘1212’ in the immediate window which is the 3rd item.

Now I can print by using Col. and No. of item

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection 

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212 

debug. Print Col(3) 
End Sub

Click the run Button

We print the third item which is ‘1212‘

Now I will store new item and add a key for it. As you can see, we have defined ‘a’ for key, And Now I will print item with key. 

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection 

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212
 
Col.add "Bull", "a"
debug. Print Col(a) 
End Sub

Click the run Button

We made the key ‘a’ For ‘Bull’ and bull got printed in Immediate window.

Now we remove the item from the collection. we will remove 3rd item and after removing add item in 3rd item. and we are using ‘after‘ arguments we have to add after 2 items.

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection 

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212 

Col.remove 3
Col.add "bull",,,2
Debug.print Col(3) 
End Sub

Now I will Click on Run button.

After run code we get the result

Let us Replace the 2nd item with bull. without any key we add 2 for before, and print Collection 2. we will also do after instead of before, but we will do after 1 

Sub UseCollection() 
'Delay Collection creation 
Dim Col as Collection 

Set Col = New Collection 

Col.add = "Cat" 
Col.add = "Dog" 
Col.add 1212 

Col.remove 2
Col.add "bull",,2
Debug.print Col(2) 
End Sub

Now I will Click on Run button.

After run code we get the result, 2nd item removed and bull added to 2nd Item

So, I hope you have understood How to use collection in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about Use collection in Excel VBA