VBA Collection

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Collection Object

In VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, we can create our collection groups apart from the existing collection of items under one group. In our many articles, we have spoken about object variables. This tutorial will give you details about the VBA collection object.

If you have gone through our previous article “VBA ArraysVBA ArraysA VBA array in excel is a storage unit or a variable which can store multiple data values. These values must necessarily be of the same data type. This implies that the related values are grouped together to be stored in an array variable.read more,” this will be much easier for you to understand. Arrays are used to group variables under one roof. Similarly, we can use the collection to store a group of variables.

One can use collections to store objects. They are far more flexible than the VBA arrays. Whereas arrays have fixed size limits, readers do not have any fixed size limit at any given time and do not require manual resizing.

VBA Collection is very similar to the “VBA DictionaryVBA DictionaryVBA Dictionary combines various types of data into a dictionary so that you may access all of them with just single variable.read more,” but the dictionary requires external object reference to be set up under the object reference window. With “VBA Dictionary,” we need to set the reference type as “Microsoft Scripting Runtime,” but Collection does not require extra fittings.

VBA Collection

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Collection (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Create Collection Object in VBA?

To get started with collection first, we need to declare the variable as “Collection.”

You can download this VBA Collection Excel Template here – VBA Collection Excel Template

Code:

Sub Collection_Example()

  Dim Col As Collection

End Sub
VBA Collection Example 1

Since the collection is an object variable, we need to set the object reference by creating a new instance.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

End Sub
VBA Collection Example 1-1

Now with the variable, we can access all the methods of the collection variable “Col.”

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.

End Sub
VBA Collection Example 1-2

Before using these methods, we need to declare a variable as a string.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Dim ColResult As String
End Sub

Now, use the variable “Col” to choose the “Add” method.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add

End Sub
VBA Collection Example 1-3

Under the “Add” method, we have specific parameters. Let us assume we are storing mobile brand names with their average selling price in the market.

Under Item, the argument enters the price of the mobile.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add Item:=15000,

End Sub
VBA Collection Example 1-4

Next, under the Key argument, enter the mobile brand name.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add Item:=15000, Key:="Redmi"

End Sub
VBA Collection Example 1-5

For the variable “ColResult,” we will store the result of the “Col” object variable.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add Item:=15000, Key:="Redmi"
  ColResult = Col(

End Sub
Example 1-6

When you open the parenthesis of the variable “Col,” we can see the argument as Index. For this argument, we need to supply the critical argument value from the Collection add method, i.e., the name of the mobile brand.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add Item:=15000, Key:="Redmi"
  ColResult = Col("Redmi")

End Sub
Example 1-7

Now, let us show the result in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.Add Item:=15000, Key:="Redmi"
  ColResult = Col("Redmi")

  MsgBox ColResult

End Sub
Example 1-8

We have completed it when we run the code. We should see the price of the mobile brand, “Redmi.”

Example 1-9

Better Understanding of Key & Item Parameters

We are sure it is not easy to understand the parameters of the Collection object. Let me explain to you a simple example.

Imagine you have a menu of fruits with their name and the price of the fruits. For example, assume you are searching the “Apple” fruit price by the name of the fruit.

To search for the price of the fruit, we need to mention the name of the fruit, i.e., in the collection VBA language, the Name of the Fruit is Key, and the price of the fruit is “Item.”

It is like applying the VLOOKUP or HLOOKUP functionHLOOKUP FunctionHlookup is a referencing worksheet function that finds and matches the value from a row rather than a column using a reference. Hlookup stands for horizontal lookup, in which we search for data in rows horizontally.read more, based on the lookup value. But, first, we will fetch the required data from the database. Here, the lookup value is Key, and the result is Item.

Advanced Example

Imagine you are a store manager in one of the retail stores and responsible for managing the customer queries. For example, one customer query is an inquiry about the product price.

It would help if you allowed the customer to search the product’s price with complete information. Similarly, you need to show the message if no data is found. Below is the example code which will present the input box in front of the user. They require you to enter the product name they are looking for. If the product is in the collection, it will show the price of the mentioned product. Otherwise, it will show the message: “The product you are searching for doesn’t exist.”

Code:

Sub Collection_Example2()

  Dim ItemsCol As Collection
  Dim ColResult As String
  Set ItemsCol = New Collection

    ItemsCol.Add Key:="Apple", Item:=150
    ItemsCol.Add Key:="Orange", Item:=75
    ItemsCol.Add Key:="Water Melon", Item:=45
    ItemsCol.Add Key:="Mush Millan", Item:=85
    ItemsCol.Add Key:="Mango", Item:=65

  ColResult = Application.InputBox(Prompt:="Please Enter the Fruit Name")

  If ItemsCol(ColResult) <> "" Then
    MsgBox "The Price of the Fruit " & ColResult & " is : " & ItemsCol(ColResult)
  Else
    MsgBox "Price of the Fruit You are Looking for Doesn't Exists in the Collection"
  End If

End Sub

Recommended Articles

This article is a guide to the VBA Collection. Here, we learn how to create a VBA collection object, advanced examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –