EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Array Functions VBA Collection
Secondary Sidebar
VBA Array Functions
  • VBA Arrays
    • VBA Arrays
    • VBA ReDim
    • VBA Dim
    • VBA Sort
    • VBA Array Length
    • VBA ArrayList
    • VBA UBound
    • VBA Join
    • VBA Collection
    • VBA Declare Array
    • VBA ReDim Array
    • VBA Array Length
    • VBA Dynamic Array
    • VBA Filter
    • VBA Lbound

VBA Collection

By Ashwani JaiswalAshwani Jaiswal

VBA Collection

Excel VBA Collection Object

We have seen the VBA Dictionary and VBA Array both have a similar connection with VBA Collection. In VBA Collection, we can create our own collection of group and provide the item and key to get the value stored under it. Whereas in VBA Dictionary we used to create the dictionary itself in VBA, which create our own dictionary which has word and value definition as normal dictionary has. And same in VBA Array also, where we used to create a collection object with limited size.

The advantage of VBA Collection is that we don’t need to extend the size of any object which we define here. Whatever object is created in VBA Collection does not have any limitations. And we don’t have to change the size of any object as if we think it requires.

Along with the creation of Add Collection object, we can use Count, Item and Remove collection objects as well.

VBA Collection Object

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create Collection Object in Excel VBA?

Below are the different examples to create collection object in excel using VBA code.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,580 ratings)
You can download this VBA Collection Excel Template here – VBA Collection Excel Template

Excel VBA Collection – Example #1

In this example, we will see how to create an Object Collection in VBA. For this, follow the below steps:

Step 1: Go to the VBA window, under the Insert menu tab select Module as shown below.

VBA Collection Example 1-1

Step 2: Write the subcategory of VBA Collection in the same name or we can choose any other name as per our convenience.

Code:

Sub Excel_Collection1()

End Sub

VBA Collection Example 1-2

Step 3: Now define a variable as Collection in any name as shown below.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection

End Sub

VBA Collection Example 1-3

We can choose any word, text or name of defining a variable, but it is recommended to define that in the process of performing function first.

Step 4: Set the defined variable as New Collection. This will help in activating and setting the already defined object as Collection to New Collection.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

End Sub

VBA Collection Example 1-4

Step 5: We have seen in the description of different collection objects as Add, Item, Count and Remove. Now we will use all of these objects for storing new content. First, to add, we will assign the ADD to defined variable ColObject.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add

End Sub

Assign ADD Example 1-5

Step 6: Now under ITEM, we will choose a sequence number that can be anything. Here, we are selecting it as 1.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1,

End Sub

ITEM Example 1-6

Step 7: In the Syntax of VBA Collection, we need to enter the KEY for which we can assign the ITEM. Here also we can choose anything to be added in VBA repository. We have considered the word Newton here.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"

End Sub

VBA Collection Example 1-7

Step 8: We can skip the Before and After portion of Syntax. Now after that, we will assign the Key to a Collection object variable.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"
ColResult = ColObject("Newton")

End Sub

Assign Key Example 1-8

Step 9: Use a message box to see the value stored in the Collection object variable.

Code:

Sub Excel_Collection1()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add Item:=1, Key:="Newton"
ColResult = ColObject("Newton")
MsgBox ColResult

End Sub

Use Message Box Example 1-9

Step 10: Now compile the code and run it by clicking on the Play button located below the menu bar.

VBA Collection Example 1-10

We will get the message as 1. This means that for key Newton, the Item is located at 1st position.

Excel VBA Collection- Example #2

There is another method or way by which we can see what item number is assigned to what Key. We can also count the number of keys filled in VBA Collection object. This process is quite similar to the process we have seen in example-1. For this, follow the below steps:

Step 1: Write the subcategory of VBA Collection as shown below.

Code:

Sub Excel_Collection2()

End Sub

VBA Collection Example 2-1

Step 2: Consider the same variable which we have seen in example-1 as Collection and set it as New Collection as ColObject.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

End Sub

VBA Collection Example 2-2

ColObject is the only variable here in this example by that help we will be seeing the count or sequence of define Item and Key.

Step 3: Now to the defined Collection object assign the ADD function and choose any value as per your choice. Here we are selecting 10.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10

End Sub

VBA Collection Example 2-3

Step 4: Similarly, add 2 or 3 more Collection Objects as shown below.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

End Sub

VBA Collection Example 2-4

Step 5: To know the count of items used in Collection Object, we will use Debug print.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

Debug.Print

End Sub

Use Debug print Example 2-5

Major plus point of using Debug print here instead of the Message box is that we would be able to get the count along with the error if there is any. For any error we will get an output as Zero and whereas for the correct process we will get the actual value.

Step 6: To get the count of variable ColObject, we will use Count function which is the part of an inbuilt function in the list.

Code:

Sub Excel_Collection2()

Dim ColObject As Collection
Set ColObject = New Collection

ColObject.Add 10
ColObject.Add 20
ColObject.Add 30

Debug.Print ColObject.Count

End Sub

VBA Collection Example 2-6

Step 7: From the View menu option, select the Immediate window as shown below. Or we can use a short cut key as Ctrl + G to get this window.

Immediate window Example 2-7

This is the place where we will be seeing the output using Debug print.

Step 8: Now compile the code and run it by clicking on the Play button located below the menu bar. We will see, in the immediate window, the count of total object collections is coming as 3.

VBA Collection Example 2-7

Now to test, whether the obtained count is correct or not, we will delete one row of collection object or convert that into text by placing apostrophe ( ‘ ) and again run the code.

VBA Collection Example 2-8

We will get the updated count as 2 which is the count of collection object 10 and 20 Item number. And we can notice that 3rd line of collection object is colored as Green and this is the sign of getting code converted into text.

Pros of Excel VBA Collection

  • We don’t need to resize the object we defined.
  • We can use different Collection objects which are inbuilt in excel VBA Collection. These objects are not seen in other function such as VBA Dictionary or VBA Array.
  • By VBA Collection, we can any type of customer or product database as well.

Things to Remember

  • It is recommended to use Debug print instead of Message Box. By this, we would be able to see the error and rectify it.
  • By VBA Collection, we can various types of objects and add any size of data.
  • In Item object function, we can define any number as per our choice. It can be product code, sequence or price which we want to see.
  • Saving the file in Macro enable excel will allow us to retain the code further.

Recommended Articles

This is a guide to VBA Collection. Here we discuss how to create a Collection object in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Array Length
  2. VBA RGB
  3. VBA Cells
  4. VBA XML
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more