An object that can store a number of values whether it can be of string data type or integer which can be easily manipulated or iterated is called Collection. In Excel VBA we should declare it as a new collection. It is an unchangeable, one-dimensional entity, unaffected by size adjustments. This immutable nature implies that alternations are

What is a Collection in VBA

In the realm of Visual Basic for application(VBA), a collection emerges as a versatile entity that ushers efficiency and structure into the world of data handling. Collections are your gateway to managing diverse sets of data, offering an organized approach even when faced with a voluminous array of elements.

Control with Collections

The essence of a collection lies in its knack for corralling akin items while preserving their distinct identities. This assembly isn’t just about quantity; it’s about having a strategic handle on data. VBA equips you with the power to craft your own collections, tailored to your specific needs. VBA comes replete with pre-built collections, presenting you with a variety of tools to streamline your coding endeavors. One of the built-in collections is the Sheets Collection, which stores every sheet in the workbook. By using a For Each Loop, you can iterate through each worksheet in the Sheet Collection.

Sub TestWorksheets()

Dim Sh As Worksheet

For Each Sh In Sheets

MsgBox Sh.Name

MsgBox Sh.Visible

Next SH

End Sub

You can also address a specific worksheet in the collection using the index value, or the actual name of the worksheet:

MsgBox Sheets(1).Name

MsgBox Sheets(“Sheet1”).Name

When you add or delete the worksheets, sheets collection grows or shrinks in size.

Note: Index number in VBA begins with 1 not with 0.

Collection Vs Array

Arrays and collections both can be used to store data, however they have several notable differences:

Scope of a Collection Object

When we talk about “Scope”, we refer to something that can be used. For a collection object, it’s like a special tool that’s there to help as long as your workbook is open. But this tool doesn’t stick around when you save your workbook and close it. If you open the workbook again later, yu ‘ll need to bring this tool back using VBA code.

If you want all your code in a specific part of your workbook to be able to use this helpful tool, you have to introduce it at the beginning of that part. And if you want this helpful tool to be available to any part of your workbook, you can it a special name and make it global. To do this, just declare it as a global object. This way, any code anywhere in your workbook can make use of it.

Create Collection

This will ensure that all your code within that module can access the collection. If you want any module within your workbook to access the collection, then define it as a global object.

Global MyCollection As New Collection

Create a Collection, Add Items, and Access Items

Follow the below steps for simple collection object creation in VBA:

Step 1: Go to Insert Tab and Select Module

Press Alt + F11 to get the VBA box and select Insert and Module to write the code.

Select Insert >module

Step 2: Create a Collection

Create a collection and add elements with the help of Add method.

create a collection

Step 3: Use For or For Each Loop

To iterate the items in Collection we can use for each loop or normal for loop

for each loop

for loop

Add Item to Collection

The Add method in a collection has 3 optional parameters – Key, Before, and After.

When you’re using the ‘Before’ and ‘After’ parameters, you’re basically giving your new item a place to fit in. It’s like arranging seats in a theater; you decide if your new guest gets a spot before or after someone already there. This is all done by mentioning the index number you want your new item to be close to.

Consider the below code:

Sub CreateCollection()

Dim MyCollection As New Collection

MyCollection . Add “Item1”

MyCollection. Add “Item2”, , 1

MyColleection. Add “Items3”

End Sub

Remove an Item from the Collection

We can use the remove method to delete items from the collection

Deletion using index number

Deletion using index number

Deletion using key

deletion using key

Count the Number of Items 

We can use the count method to count the number of items in a collection.

Count method

Test collection for a specific value

We will use each loop to iterate the collection and then if case checks whether the specified value is present in the collection or not.

Test collection

Return a Collection from a Function

If you want to know to how to fetch a collection form a function, it’s quite similar to getting any other object. You just need to use the Set Keyword.

Sub ReturnFromFunction()

Dim MyCollection As Collection

Set MyCollection = PopulateCollection

MsgBox MyCollection.Count

End Sub

In this code, a sub-routine crafts an object named ‘MyCollection’, Then, using the ‘Set’ Keyword, it makes a call to a function called ‘PopulateCollection’. After this tag team effort, a message box chimes in to tell you that there 2 items in your collection.

Function PopulateCollection() As Collection

Dim MyCollection As New Collection

MyCollection.Add “Item1”

MyCollection.Add “Item2”

Set PopulateCollection = MyCollection

End Function

It conjures up a new collection object, fills it with two items, and then hands it over to the collection object in the earlier sub-routine. It’s like creating a special gift, packing it with goodies, and passing it along to the eager recipient.

The key here is the ‘Set PopulateCollection = MyCollection’ line. It’s the final flourish that hands over your creation to where it’s needed.

FAQs VBA Collections in Excel

What is a VBA collection in Excel?

A VBA collection in Excel is a type of object that allows you to store and manage a group of related items, such as numbers, text, or other objects. Collections are useful for organizing and manipulating data in your VBA code.

How is a VBA collection different from an array?

While both collections and arrays store multiple items, there are differences. Arrays have a fixed size, and you access their elements using indexes. Collections can grow or shrink dynamically and can hold different data types. Collections use keys or index to access their elements.

How to Create a VBA collection?

To Create a VBA collection, declare a variable of teh collection type using the “Dim” statement. Then, use the “NeW” keyword to initialize the collection. For example:

Dim MyCollection As New Collection

How to add items to a VBA collection?

You can add items to a collection using the “Add” method. For Example:

MyCollection. Add “Item1′

MyCollection.Add “Item2”

What are the benefits of using Collections in VBA?

Collections provide dynamic storage for data, allowing items to be added or removed easily. They offer a flexible alternative to arrays and can hold different data types. Collections are particularly useful when you need to manage an manipulate groups of related objects.