Collections in VBA – Create, Add, Remove Items

In this blog, we would learn about Collections in VBA. It has a lot of applications in the VBA programming language. We kept the article simple and easy to understand.

What are Collections in VBA?

A Collection is a special type of VBA object that contains a number of items that we can easily access. A Collection is used to group variables. We do not need to specify the total number of items in a Collection while declaring it. Whenever we add or remove an item from the Collection, the size of collection and allocated memory space is automatically updated.

The items in a collection are numbered. It there are ten items in a collection, then the index would start from 1 for the first item, 2 for second item and 10 for the last item.

A Collection is Read-only. This means that we cannot change the items of a collection once added to it.

We can create our own collection. Also, there are some in built collections in VBA like the Sheets Collection.

Syntax – Creating a Collection

We need to declare a collection and then create it. We can declare a collection as follows.

Dim <collection_name> As New Collection        'declare and create in same line

The other method is to first declare the collection and then use Set and New keywords to create it in next statement,

Dim <collection_name> As Collection         'Declaring the collection
Set <collection_name> = New Collection        'Crating a Colletion

You can download the practice workbook to follow along.

Example – Using Sheets Collection in VBA

We would now use code to unhide all the worksheets of our workbook using the Collections in VBA. The Sheets Collection is an inbuilt VBA collection that contains all the worksheets of our workbook. In this example, we would use the Sheets Collection to access each Sheet of our workbook, print the Sheet name, and Set its visibility to TRUE.

Sub example_sheets()

'code to print name of all sheets and unhide them
'declaring a worksheet object to refer to each worksheet

Dim sh As Worksheet

'looping through all the worksheets in Sheets Collection

For Each sh In Sheets
Debug.Print sh.Name             'printing name of each worksheet on Immediate Window
sh.Visible = True               'changing the visibility of each worksheet to TRUE
Next sh

End Sub

When you run the code, name of all worksheets would appear on Immediate Window (Ctrl G keys). If there would be any hidden worksheets then they would be visible in the workbook after code execution.

Add Items to the Collection Object – Add Method

When we add a new worksheet, it is automatically added to the Sheets Collection as it is an inbuilt collection.

For a collection that we created, use the Add method to add item to the Collection.

<collection_name>.Add (Item,[key],[before],[after])

Add method of Collection object needs four arguments as follows.

• Item – This is the expression value that we wish to add to the Collection.
• [key] – This is used to refer to the item of the Collection. It must be unique.
• [before] – This is the index number of the item before which we want to add a new item. The new item would be added in its place and the subsequent items would move one index down.
• [after] – This is the index number of the item after which we want to add our new item.

key, before and after are the optional arguments of Add method.

In this example, we create a collection named Books and add three items to it.

Sub Example_Books()
'code to create and add items to a collection
'create a collection

Dim Books As New Collection

'add item using add method
Books.Add "It Ends With Us"
Books.Add "It Starts With Us", "MyFav"          'we specify the key of the second Books item
End Sub

Accessing the Items of Collection – Item method

The Item method of the Collection method is used to read the items of the Collection object.

<collection_name>.Item (Index)

• Index – The index of the item which we want to access. We can also access the item using the key in double quotes.

For instance, print item 1 of the Books Collection using this statement.

'printing the name of first item of books collection
MsgBox (Books.Item(1))

You can also use the key of the second Books item to access it.

'accessing the item using key
Msgbox (Books.Item("MyFav"))

Key is supplied in double quotes and it is very useful when we want to access a particular item in a very large collection. Key acts as a name of the item that helps in easy access rather than remembering its index to access it.

To access all the items of the collection, we would use the For Next Loop.

Sub Example_Books()
'code to access all items of collection

Dim Books As New Collection

'add item using add method
Books.Add "It Ends With Us"
Books.Add "It Starts With Us", "MyFav"          'we specify the key of the second Books item

'accessing all the items an their index using For Next loop
For i = 1 To Books.Count
Debug.Print (Books.Item(i)) & " - index is - " & i
Next i

End Sub

Run this code and you see that the book items are printed on the Immediate Window.

We can also Add an item before the first item of the Books collection using this statement.

Books.Add ("moving before It ends with us",,1)     '1 is the index of existing item

Here we specify the item. , , indicates that key is left empty and then in before we passed 1 as we want to add this item before the existing first item.

Now that you run the code.

Sub Example_Books()
'code to add item before first item

Dim Books As New Collection

'add item using add method
Books.Add "It Ends With Us"
Books.Add "It Starts With Us", "MyFav"          
Books.Add "moving before It ends with us", , 1          'adding item before 1

'accessing all the items using For Next loop
For i = 1 To Books.Count
Debug.Print (Books.Item(i)) & " - index is - " & i
Next i

End Sub

Now that you run this code,

Removing Item from Collection

Use the Remove method of the Collection object to remove the item from the collection.

<collection_name>.Remove (Index)

Specify the index of the item you wish to remove. If you wish to delete the entire collection, then set the collection object to Nothing. When we use the Nothing keyword, it deallocates the memory allocated to the object.

Set <collection_name> = Nothing

This brings us to end.

Thank you for reading.❤