WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Collection

VBA Collection

Excel VBA Collection Object

In VBA coding, apart from the existing collection of items under one group, we can create our collection groups. In our many articles, we have spoken about object variables, and in this tutorial, we will take through the VBA collection object in detail.

If you have gone through our previous article “VBA Arrays,” this will be much easier for you to understand. Arrays are used to group variables under one roof; similarly, Collection is also used to store a group of variables.

Collections are used to store objects. They are far more flexible than the VBA arrays, whereas arrays have fixed size limits, but readers don’t have any fixed size limit at any given point in time and even don’t require manual resizing.

VBA Collection is very similar to the “VBA Dictionary,” but the dictionary requires external object reference to be set it up under the object reference window. With VBA Dictionary, we need to set the reference type as “Microsoft Scripting Runtime,” but Collection doesn’t require any extra fittings.

VBA Collection

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 collection variable “Col.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Collection_Example()

  Dim Col As Collection
  Set Col = New Collection

  Col.

End Sub

VBA Collection Example 1-2

Before making use of 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’s 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 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 show the result in the message box in VBA.

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

Ok, we are done when we run the code. We should see the price of the mobile brand, “Redmi.”

Example 1-9

Better Understanding of Key & Item Parameters

I am 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 price of the fruits. Assume you are searching the “Apple” fruit price by the name of the fruit.

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

It is like applying the VLOOKUP or HLOOKUP function, based on the lookup value, and we will fetch the required data from the database. Here lookup value is Key, and the result is Item.

Advanced Example

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

It would help if you allowed the customer to search the price of the product with complete information. Similarly, you need to show the message in case of no data found. Below is the example code which will present the input box in front of the user. They require to enter the name of the product they are looking for. If the product is there in the collection, it will show the price of the mentioned product, or else it will show the message as “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 has been a guide to VBA Collection. Here we learn how to create a VBA collection object and Advanced examples, and a downloadable excel template. Below are some useful excel articles related to VBA –

  • Hide Columns in VBA
  • Free Course on VBA
  • VBA JOIN Function
  • GetOpenFilename VBA
7 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Collection Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More