Microsoft Excel VBA Collections

	Collections

Fundamentals of Collections

Introduction

A collection is a series of items where each item has the same characteristics. In other words, all items can be described the same way. Programmatically, a collection is a series of items where all items share the same properties and methods, if any. For example, a collection can be made of employees of a company where each employee can be described with a characteristics such as a name.

Practical Learning: Introducing Objects

Start Microsoft Excel
From the resources that accompany these lessons, open the gdcs1 project
To open Microsoft Visual Basic, on the Ribbon, click Developer and, in the Code section, click Visual Basic:
Right-click the form and click View Code

Creating a Collection

In our lessons, we will not create new collections. We will only use two categories: the Collection class and the built-in collection.

To support collections, the Visual Basic language is equipped with a class named Collection.

Actually, the Collection class we are going to study here is the one defined in VBA. The parent Visual Basic language has a somewhat different Collection class with additional functionality not found in the VBA's version.

This class can be used to create a collection. To do this, declare a variable of type Collection. Here is an example:

Sub Exercise()
    Dim Employees As Collection
End Sub

After declaring the variable, to allocate memory for it, use the Set operator to assign a New instance to the variable. Here is an example:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
End Sub

Instead of always creating a new collection unless you have to, VBA for Microsoft Excel comes equipped with many collections so that you almost may never need to create your own collection. The collections that are already built in the VBA language are referred to as built-in collections.

The built-in collection classes are derived from the Visual Basic's Collection class. As a result, all of their primary functionality comes from the Collection class. This also means that everything we will mention for the Collection class applies to any built-in collection.

To use a built-in collection, you can declare a variable for it. Here is an example:

Sub Exercise()
    
    Dim CurrentSheets As Worksheets
    
End Sub

In reality, and as we will next in the next lessons, when Microsoft Excel starts, most (if not all) of the built-in collection classes are already available so that you do not have to declare their variable before using them.

Characteristics of, and Operations on, a Collection

Adding an Item to a Collection

The primary operation to perform on a collection consists of adding items to it. To support this, the Collection class is equipped with a method name Add. Its syntax is:

Public Sub Add( _
   ByVal Item As Object, _
   Optional ByVal Key As String, _
   Optional ByVal { Before | After } As Object = Nothing _
)

This method takes three arguments. Only the first is required. The Item argument specifies the object to be added to the collection. Here is an example:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
End Sub

In the same way, you can add as many items as you want:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
    Employees.Add "James Wiley"
    Employees.Add "Gertrude Monay"
    Employees.Add "Helene Mukoko"
End Sub

Remember that if you are using one of the built-in collection classes, you do not have to declare a variable for it. You can just call the Add method on it to add an item to it. Here is an example:

Sub Exercise() 
    Worksheets.Add
End Sub

Practical Learning: Introducing Objects

In the Object combo box, make sure UserForm is selected.
In the Procedure combo box, select Activate

Type the code as follows:

Private Sub UserForm_Activate()
    Rem Create the "other" cleaning items
    cbxNameItem1.AddItem "None"
    cbxNameItem1.AddItem "Women Suit"
    cbxNameItem1.AddItem "Dress"
    cbxNameItem1.AddItem "Regular Skirt"
    cbxNameItem1.AddItem "Skirt With Hook"
    cbxNameItem1.AddItem "Men 's Suit 2Pc"
    cbxNameItem1.AddItem "Men 's Suit 3Pc"
    cbxNameItem1.AddItem "Sweaters"
    cbxNameItem1.AddItem "Silk Shirt"
    cbxNameItem1.AddItem "Tie"
    cbxNameItem1.AddItem "Coat"
    cbxNameItem1.AddItem "Jacket"
    cbxNameItem1.AddItem "Swede"
        
    cbxNameItem2.AddItem "None"
    cbxNameItem2.AddItem "Women Suit"
    cbxNameItem2.AddItem "Dress"
    cbxNameItem2.AddItem "Regular Skirt"
    cbxNameItem2.AddItem "Skirt With Hook"
    cbxNameItem2.AddItem "Men 's Suit 2Pc"
    cbxNameItem2.AddItem "Men 's Suit 3Pc"
    cbxNameItem2.AddItem "Sweaters"
    cbxNameItem2.AddItem "Silk Shirt"
    cbxNameItem2.AddItem "Tie"
    cbxNameItem2.AddItem "Coat"
    cbxNameItem2.AddItem "Jacket"
    cbxNameItem2.AddItem "Swede"
        
    cbxNameItem3.AddItem "None"
    cbxNameItem3.AddItem "Women Suit"
    cbxNameItem3.AddItem "Dress"
    cbxNameItem3.AddItem "Regular Skirt"
    cbxNameItem3.AddItem "Skirt With Hook"
    cbxNameItem3.AddItem "Men 's Suit 2Pc"
    cbxNameItem3.AddItem "Men 's Suit 3Pc"
    cbxNameItem3.AddItem "Sweaters"
    cbxNameItem3.AddItem "Silk Shirt"
    cbxNameItem3.AddItem "Tie"
    cbxNameItem3.AddItem "Coat"
    cbxNameItem3.AddItem "Jacket"
    cbxNameItem3.AddItem "Swede"
        
    cbxNameItem4.AddItem "None"
    cbxNameItem4.AddItem "Women Suit"
    cbxNameItem4.AddItem "Dress"
    cbxNameItem4.AddItem "Regular Skirt"
    cbxNameItem4.AddItem "Skirt With Hook"
    cbxNameItem4.AddItem "Men 's Suit 2Pc"
    cbxNameItem4.AddItem "Men 's Suit 3Pc"
    cbxNameItem4.AddItem "Sweaters"
    cbxNameItem4.AddItem "Silk Shirt"
    cbxNameItem4.AddItem "Tie"
    cbxNameItem4.AddItem "Coat"
    cbxNameItem4.AddItem "Jacket"
    cbxNameItem4.AddItem "Swede"

    Rem Create the orders status
    cbxOrderStatus.AddItem "Processing"
    cbxOrderStatus.AddItem "Ready"
    cbxOrderStatus.AddItem "Picked Up"
End Sub

Close Microsoft Visual Basic
Save the document

Accessing an Item in a Collection

The items of a collection are organized in an arranged sequence where each item holds a specific index. the first item in the collection holds an index of 1. The second item holds an index of 2, and so on.

To give you access to the items of a collection, the Collection class is equipped with a property named Item. There are two ways you can use this property.

To formally use the Item property, type the name of the collection object, followed by the period operator, followed by Item and optional parentheses. After the Item property or inside its parentheses, type the index of the desired item. Here is an example:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
    Employees.Add "James Wiley"
    Employees.Add "Gertrude Monay"
    Employees.Add "Helene Mukoko"
    
    Employees.Item 2
End Sub

Remember that you an also use parentheses:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
    Employees.Add "James Wiley"
    Employees.Add "Gertrude Monay"
    Employees.Add "Helene Mukoko"
    
    Employees.Item (2)
End Sub

Instead of using the Item property, you can apply the index directly to the collection object. Here are examples:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
    Employees.Add "James Wiley"
    Employees.Add "Gertrude Monay"
    Employees.Add "Helene Mukoko"
    
    Employees.Item 2
    Employees.Item (2)
    
    Employees 2
    Employees (2)
End Sub

All these four techniques (notations) give you access to the item whose index you provided.

Removing an Item From a Collection

As opposed to adding a new item, you can delete one. To support this operation, the Collection class is equipped with a method name Remove. Its syntax is:

Public Sub Remove(Index As Integer)

This method takes one argument. When calling it, pass the index of the item you want to delete. Here is an example:

Sub Exercise()
    Dim Employees  As Collection
    
    Set Employees = New Collection
    
    Employees.Add "Patricia Katts"
    Employees.Add "James Wiley"
    Employees.Add "Gertrude Monay"
    Employees.Add "Helene Mukoko"
    
    Employees.Remove 2
End Sub

This code deletes the second item in the collection.

The Number of Items in a Collection

When you start a new collection, obviously it is empty and its number of items is 0. To keep track of the number of items in a collection, the Collection class is equipped with a property named Count whose type is an integer. Remember that all built-in collection classes inherit their behavior from the Collection class. This means that the built-in collection classes are equipped with a property named Count to hold their number of items.

We saw how you can add new items to a collection. Every time you add a new item to the collection, the Count property increases by 1.

We also know how to remove an item from a collection. Whenever an existing item is deleted, the value of the Count property is decreased by 1.

At anytime, to know the number of items that a collection is currently holding, get the value of its Count property.