VBA Collection

What Is VBA Collection in Excel?

VBA Collection is a data type that can be assigned to a variable. Using the variable, we can store or collect a set of items. Using VBA collection in excel, we can easily access and manipulate items of the Collection object.

VBA comes with built-in collections like Workbooks, Worksheets, Range, and Cells collections. The Collection data type is used to group items of one collection, somewhat like arrays in VBA but a bit different.

Let us see how to use VBA Collection in this article.

Collections Versus Arrays

Collections and Arrays have some similar features. However, there are several differences between them. Let us have a look at them now.

Particulars	Array	Collection
Dimension	Arrays are multi-dimensional. We can define the array with several dimensions	Collections are one-dimensional.
Size of the Variable	Arrays’ size needs to decide before we assign values to them. We need to be sure of the size of the data well in advance. We can adjust the size of the array in the middle of the code by using the keyword “ReDim”, however, we need to use the word “Preserve” if you do not want to lose the previous array values.	Collections do not need any size, we can keep assigning values to them. Need not be aware of the size of the data here.
Editable	Arrays can be changed	Collections are read-only
Data Type	An array accepts only a single data type that was assigned to the variable while declaring the array. All the values should be of the same data type.	In Collection, we can assign any data type, and it accepts all kinds of data.
Locating the Data	Arrays require looping code to loop through and find the specific value	Collections can fetch the required data by using the key name.

How To Create Collection Object In VBA?

Follow the steps listed below to create a collection object in VBA.

Step 1- First, go to the Visual Basic Editor window and then, start the sub-procedure by naming the macro.

Step 2 – Next, define a variable by using the DIM keyword.

Step 3 – Then, for the defined variable, we need to assign the collection object like the following.

Step 4 – Since the collection is an object variable, we need to use the Set keyword, and then, we need to create the instance of the collection like the following.

Step 5 – Now, by using the collection variable name MyCollection, we can start assigning values to the collection.

To see the methods available with the collection, enter the collection name MyCollection and enter the dot (.) to see the IntelliSense list.

As we can see, we have 4 methods i.e., Add, Count, Item, and Remove.

Step 6 – Let us add a few items to the collection list using the Add method.

After entering the collection name, choose the add method to see the syntax of the add method.

The syntax of the add method has 4 arguments. Out of these 4, the first one is mandatory, and the remaining 3 are optional.

Item – In this argument, we need to give the value that we are assigning to the collection variable.

[Key] – In this argument, we need to provide the key name to the given item name, so that we can retrieve the item by using this key name later.

These two arguments are good enough to start off with.

Step 7 – Next, enter some value under the Item argument.

Step 8 – Then, give the key name as “KA”.

Step 9 – Now, let us show the assigned value in the message box. Enter the MsgBox function and then enter the collection variable name.

Step 10 – To retrieve the value of the collection variable, we need to give the index number of the assigned value. Since we have assigned only one value to the collection variable, give the index number as 1.

Sub Collection_Object()

   Dim MyCollection As Collection
   Set MyCollection = New Collection
   MyCollection.Add "Bangalore", "KA"
   MsgBox MyCollection(1)

End Sub

Let us run this code, and when we run this code, we will see the assigned value in the message box like the following.

In the message box for the collection variable, we have given the index number as 1 because the value that we have assigned is only one. However, we can also give the key name apart from the index number given while assigning the value to the collection variable.

Sub Collection_Object()

  Dim MyCollection As Collection
  Set MyCollection = New Collection
  MyCollection.Add "Bangalore", "KA"
  MsgBox MyCollection("KA")

End Sub

This will also return the collection variable assigned value.

Removing All Items From A Collection

We have seen how to assign the values to a Collection object variable. Similarly, we can remove the assigned values from the collection as well.

For example, look at the following assigned values.

Capital_Cities.Add “Mumbai”, “MH”

Capital_Cities.Add “Bangalore”, “KA”

Capital_Cities.Add “Ahmedabad”, “GJ”

    Capital_Cities.Add “Bhopal”, “MP”

    Capital_Cities.Add “Jaipur”, “RJ”

We have assigned 4 values to the collection variable Capital_Cities. To remove the value of the 3^rd assigned value of the collection object, we can simply use the Remove method followed by the index number of the collection.

Next, for the index argument of the remove method, give the number as 3.

This will remove the 3^rd assigned value, and all the subsequent positions will be shifted up, and the 4^th positioned value becomes 3 now.

Similarly, to remove all the collection values, we need to simply reset the collection to a new instance of the collection object.

Adding Items To A Collection

Assuming we need to create a VBA Collection of capital cities of some of the states in India, we will use the collection object variable.

First, declare a variable to hold capital cities and assign the data type as Collection.

Next, create an instance of the collection by using the set keyword.

Remember, this method is a delayed method of creating the instance of the collection. However, we can define a variable and create the instance of the collection object in a single line itself like the following.

This method reduced one more line of code by just adding the word New before we assign the collection object data type.

Now we will use the collection object variable name and assign the capital city names. For example, for the state of Maharashtra, the capital city is Mumbai; so, we can add the item as Mumbai.

For the key, we can give the shortcode of the state MH.

So, now whenever we need to find the capital city of Maharashtra, we just have to ask the collection variable Capital_Cities in the shortcode MH, and it will return the capital city name Mumbai.

Clearly, as we can see, we have assigned some capital city names, and the following is the complete code for you.

Sub Collection_Object_Add_Item()

   Dim Capital_Cities As Collection
   Set MyCollection = New Collection

   Capital_Cities.Add "Mumbai", "MH"
   Capital_Cities.Add "Bangalore", "KA"
   Capital_Cities.Add "Ahmedabad", "GJ"
   Capital_Cities.Add "Bhopal", "MP"
   Capital_Cities.Add "Jaipur", "RJ"

End Sub

When we assign more than one value, we need to use loops to go through each and every collection and store or retrieve the values of the collection one by one.

For example, the following code will store each capital city in cells.

Sub Collection_Add_Items()

Dim Capital_Cities As New Collection

Capital_Cities.Add "Mumbai", "MH"
Capital_Cities.Add "Bangalore", "KA"
Capital_Cities.Add "Ahmedabad", "GJ"
Capital_Cities.Add "Bhopal", "MP"
Capital_Cities.Add "Jaipur", "RJ"

Dim k As Long
For k = 1 To 5
    Cells(k, 1).Value = Capital_Cities(k)
Next k

End Sub

The above code will loop through the collection variable values and will store the values in cells.

Similarly, we can also use this as a question-and-answer kind of thing, where we will give the option of entering the state shortcode to the user, and based on the user input, we will retrieve the capital city of the given state.

The following code is designed to get the input from the user using the input box.

Sub Collection_Add_Items_User_Input()

Dim Capital_Cities As New Collection

Capital_Cities.Add "Mumbai", "MH"
Capital_Cities.Add "Bangalore", "KA"
Capital_Cities.Add "Ahmedabad", "GJ"
Capital_Cities.Add "Bhopal", "MP"
Capital_Cities.Add "Jaipur", "RJ"

Dim State_Code As String

State_Code = Application.InputBox("Which State Capital City you need?", "Enter the state short code")

On Error GoTo MyValue
If State_Code <> "" Then

    MsgBox Capital_Cities(State_Code)
Else
    MsgBox "The state short code is wrong"

MyValue: MsgBox “The state short code is wrong”
End If

End Sub

This code will ask us to enter the state short code.

First, we need to enter the state short code for which we need to find the capital city. For example, let us enter the short code “MP”.

Next, click on OK, and we will get the capital city of Madhya Pradesh.

Assume, we enter the state short code that is not there in the collection list; then, we will get the following message box stating, The given state short code is wrong.

Accessing Items Of A Collection

Once the values are assigned to the collection variable, we can access these values by using either the index number or the keyword given while assigning the values.

For example, look at the following assigned values.

    Capital_Cities.Add “Mumbai”, “MH”

    Capital_Cities.Add “Bangalore”, “KA”

    Capital_Cities.Add “Ahmedabad”, “GJ”

    Capital_Cities.Add “Bhopal”, “MP”

    Capital_Cities.Add “Jaipur”, “RJ”

The index number for all the above collection values is in sequential order, the first value occupies the index number 1, and so on.

To access the 1^st value of a collection variable, we need to enter the collection variable name, open parenthesis, and enter the index number.

Give the index number as 3.

This will return the 3^rd collection value i.e., “Ahmedabad” in a message box.

However, we can also access the collection values based on the key names i.e., “GJ”.

This will also return the same value as the previous one, but this will be the most accurate one because, with the index number, we need to be sure of the position of the collection value.

Sorting a Collection

Once the collection values are stored, they will take the index position according to the order, they are in. For example, look at the following values.

   Index1 =  Capital_Cities.Add “Mumbai”, “MH”

   Index 2 = Capital_Cities.Add “Bangalore”, “KA”

   Index 3 = Capital_Cities.Add “Ahmedabad”, “GJ”

The second positioned value is “Bangalore”. However, by using the Before and After arguments, we can sort the position of the collection value.

Assuming that we need to move the second position value to the first position, we need to use the Before argument of the Add method.

For the Before argument, we have given the index number 1 i.e., to move before the first index number. So, whenever we try to retrieve the value, we will get “Bangalore” as the first value, not “Mumbai”.

Similarly, if we want to move down the value, we can use the After argument of the Add method.

Important Things to Note

• The VBA Collection is an Object data type and is different from arrays.
• When we define the VBA Collection object, we need to use the set keyword and create a new instance of the collection. For example, Set Collection Name is equal to New Collection.
• Only Add method is mandatory in the Collection object.
• To change the order of the collection values assigned, we need to use the Before or After argument of the Collection object in Excel.
• The keys should be unique for each assigned value otherwise, we will get the run time error.

Frequently Asked Questions

Download Template

This article must be helpful to understand the VBA collection, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Collection. Here we explain how to create, add, remove, access, and sort collection object in VBA. You may learn more from the following articles –

• VBA CSTR
• VBA ReDim
• VBA Not Equal