REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

One of the important data types available in VBA is ‘Collection’ which allows the storage and manipulation of a group of objects. In this blog post, we will discuss the syntax, storage, and range of a VBA ‘Collection’ data type. We will also provide five examples of VBA codes that utilize the ‘Collection’ data type.

Introduction to ‘Collection’ in VBA DataType

Syntax of VBA Collection

A collection in VBA is declared using the following syntax:

Dim collectionName As Collection

The keyword ‘Dim’ is used to declare a variable and ‘As Collection’ specifies the data type of the variable. The name of the collection can be anything the user desires, however, it is recommended to use a descriptive name.

Storage of VBA Collection

A collection in VBA can store a group of objects such as integers, strings, dates, or even other collections. Objects in a collection are stored in a sequential manner and can be accessed using an index number. The index of the first object in a collection is 1, the second object is 2, and so on. A collection can hold a maximum of approximately 16,000 objects.

Range of VBA Collection

Unlike an array, a collection in VBA can expand to accommodate new objects as needed. This means that the size of a collection is not fixed and can be dynamically changed during the execution of the code. Additionally, a collection can be both indexed and associative. This means that objects can be accessed using either an index number or a key value assigned to each object.

Example VBA Codes using ‘Collection’ data type

Example 1: Add Items to a Collection

Sub AddToCollection()
    Dim myCollection As Collection
    Set myCollection = New Collection
    'Add items to the collection
    myCollection.Add "Apple", "Fruit"
    myCollection.Add "Orange", "Fruit"
    myCollection.Add "Banana", "Fruit"
    myCollection.Add "Carrot", "Vegetable"
End Sub

In this example, we declare a collection called ‘myCollection’ and use the ‘Add’ method to add items to it. The first argument represents the object being added and the second argument is its associated key value. In this case, we are adding four items (fruits and vegetables) to the collection.

Example 2: Loop through a Collection using Index

Sub LoopThroughCollection()
    Dim myCollection As Collection
    Set myCollection = New Collection
    'Add items to the collection
    myCollection.Add "John", "Name"
    myCollection.Add "Jane", "Name"
    myCollection.Add "Eric", "Name"
    'Loop through the collection
    For i = 1 To myCollection.Count
        Debug.Print myCollection(i)
    Next i
End Sub

This code snippet shows how to loop through a collection using its index numbers. The ‘Count’ property is used to determine the number of items in the collection and the ‘For’ loop is used to iterate through each item. In this example, we print the names of three people stored in the collection.

Example 3: Loop through a Collection using Key Value

Sub LoopThroughCollection()
    Dim myCollection As Collection
    Set myCollection = New Collection
    'Add items to the collection
    myCollection.Add 10, "Number"
    myCollection.Add 20, "Number"
    myCollection.Add 30, "Number"
    'Loop through the collection
    For Each key In myCollection
        Debug.Print myCollection(key)
    Next key
End Sub

In this example, we use the ‘For Each’ loop to iterate through a collection using its key values. The ‘Debug.Print’ statement is used to print the items of the collection on the immediate window. In this code, we have added three numbers and used their associated key values to access them.

Example 4: Check if a Collection Contains a Specific Key Value

Sub CheckCollection()
    Dim myCollection As Collection
    Set myCollection = New Collection
    'Add items to the collection
    myCollection.Add "Red", "Color"
    myCollection.Add "Blue", "Color"
    myCollection.Add "Green", "Color"
    'Check if the collection contains a specific key value
    If myCollection.Contains("Color") Then
        MsgBox "Collection contains color values."
    Else
        MsgBox "Collection does not contain color values."
    End If
End Sub

This code snippet shows how to use the ‘Contains’ method to check if a collection contains a specific key value. If the collection contains the specified key value, it will return ‘True’ and execute the first message box. Otherwise, it will return ‘False’ and execute the second message box.

Example 5: Remove an Item from a Collection

Sub RemoveFromCollection()
    Dim myCollection As Collection
    Set myCollection = New Collection
    'Add items to the collection
    myCollection.Add "Michael", "Name"
    myCollection.Add "Lisa", "Name"
    myCollection.Add "Tom", "Name"
    'Remove an item from the collection
    myCollection.Remove "Name"
End Sub

In this final example, we use the ‘Remove’ method to, well, remove an item from a collection. The argument passed in the method is the key value of the object we want to remove. In this code, we remove the key value ‘Name’ and its associated object from the collection.

In Conclusion

VBA ‘Collection’ data type is a powerful tool that allows the storage and manipulation of a group of objects. With its dynamic size and ability to be both indexed and associative, collections can be a useful asset in your VBA coding. We have provided examples of VBA codes that demonstrate the usage of ‘Collection’ data type. Experiment with these codes and see how you can incorporate collections into your own VBA projects.

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBA Data TypesLast Updated: September 23, 2023

Leave A Comment