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,
Also Read: Search Item in Collection – VBA
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.
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.
- 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.
Also Read: Working with Worksheets in VBA Excel
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.
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.❤