Module 6 - Collections and Loops
Lesson 6.1 - Collections and Objects
Topic 6.1.1 - Collections in Excel

From the earliest part of this training course we've been referring to objects and applying methods and properties in order to make things happen.  Some objects in VBA are organised into collections and this part of the lesson explains the basic concept.

Files Needed

You can click here to download the file for this part of the lesson.

Completed Code

You can click here to download a file containing the completed code.

The Example Workbook

Download and extract the workbook linked to in the Files Needed section above.

Examples of Collections

Many objects in Excel VBA are organised into collections.  A collection refers to all of the objects of a particular type within a specific container.  The example workbook that you have opened is a container for a number of collections:

Sheets

The workbook contains a collection of five Sheets. There is also a separate collection of three Worksheets, and a separate collection of two Charts.

Each worksheet is a container for other collections:

Worksheet collections

This worksheet contains a collection of four ListObjects and a collection of four ChartObjects.

Each chart also contains collections of other objects:

Chart collections

A chart contains a SeriesCollection collection. Each Series object in a SeriesCollection is a container for a Points collection.

The workbook itself belongs to a Workbooks collection which contains all of the currently open workbooks.

There are many other collections in VBA and the techniques described in this part of the lesson apply to all of them.

Referring to Collections

At a basic level, a collection is a special type of VBA object.  As with other VBA objects, you can use methods and properties of a collection to make things happen.  In order to do this, you first need to know how to refer to the collection.  Open the VBE and find the subroutine called ReferToCollections in Module1:

Find sub

Find this subroutine in Module1.

 

Write the name of the Workbooks collection followed by a full stop:

Workbooks

The IntelliSense list shows the methods and properties of the Workbooks collection.

 

Using the list shown in the image above, you could, for example, use the Add method to create a new, blank workbook.  You could use the Close method to close all of the currently open workbooks.  you could use the Count property to return the number of open workbooks.  We'll add some code to write the number of open workbooks into the Immediate window.  Add a Debug.Print statement as shown in the diagram below:

Debug print

We've added some text so that we know what the number represents when we run the code.

 

Add more code to print the Count property of the Worksheets, Charts and Sheets collections to the Immediate window:

Count items

Feel free to copy, paste and edit the code rather than writing out each line from scratch.

 

Run the subroutine and check the results in the Immediate window.  If you can't see this window choose View | Immediate Window from the menu:

Results

These are the results you will see if you only have open the workbook that you downloaded earlier.

 

Using the Default Container for a Collection

In some cases when you refer to a collection, VBA assumes which object is the container for the collection you have referenced.  For example, when you refer to the Worksheets collection, VBA assumes that you are referring to the collection of worksheets in the currently active workbook.  Add a line of code to the top of the ReferToCollections subroutine to create a new workbook:

Add workbook

Add an instruction to add an object to the Workbooks collection.

 

Adding a new workbook to the Workbooks collection automatically makes the new workbook the active one. Run the subroutine again and check the results in the Immediate window (you may wish to clear the contents of the window first):

Count items

The output shows the counts of the collections belonging to the new workbook, rather than the one in which the code is stored.

 

Specifying the Container of a Collection

You can reference the containing object of a collection if you prefer to be explicit.  Modify the instructions which count the number of Worksheets, Charts and Sheets to refer to the ThisWorkbook object:

Thisworkbook

Modify the last three instructions to specify ThisWorkbook as the container for each collection you want to reference.

Clear the contents of the Immediate window and then run the subroutine again:

Results

The result will show the counts of collections in the workbook in which the code is stored, regardless of which workbook is active.

 

Referring to Embedded Collections

Some collections require an explicit reference to the object which contains the collection.  Create a new subroutine called ReferToEmbeddedCollections in Module1:

New sub

Create a subroutine as shown here.

 

The NFL 2017 worksheet contains two tables, referred to as ListObject objects:

List objects

Each table shown here is a ListObject belonging to the ListObjects collection.

Add an instruction to write the Count of the ListObjects collection to the Immediate window:

Count tables

Refer to the Count property of the ListObjects collection.

 

Clear the contents of the Immediate window and execute the subroutine.  You'll see an error message as soon as you do so:

Error

The message indicates that it doesn't recognise the name of the ListObjects collection.

 

You can't refer to the ListObjects collection without first referencing the object which contains it.  In this case, the containing object is the worksheet called NFL 2017.  Add code to reference the correct worksheet before the ListObjects collection:

Reference worksheet

Here we're using the code name that we've assigned to the worksheet - wsNFL2017.

Try running the subroutine again and check the results in the Immediate window:

Result

This time you should see the number of tables appears as expected.

 

The same worksheet also contains two embedded charts which belong to the ChartObjects collection.  Add a line of code to display the count of items in this collection:

Chartobjects

You must refer to the container of the ChartObjects collection.

Clear the contents of the Immediate window and run the subroutine again:

Result

The results will appear as shown here.

 

To practise referring to collections:

  1. In the same module, create a new subroutine called ChartCollections:

Sub ChartCollections()

 

End Sub

  1. Add an instruction to print the Count of the Charts collection of the workbook in which the code is stored:

Sub ChartCollections()

 

Debug.Print ThisWorkbook.Charts.Count & " charts"

 

End Sub

  1. Add an instruction to print the Count of the SeriesCollection collection:

Sub ChartCollections()

 

Debug.Print ThisWorkbook.Charts.Count & " charts"

 

Debug.Print SeriesCollection.Count & " series"

 

End Sub

  1. Attempt to run the code and confirm that you see an error message:
Error

The SeriesCollection collection will not be recognised.  Click OK to proceed.

 
  1. Edit the second instruction you have written to refer to the AFC Chart as the container of the SeriesCollection.  You can use the code name of this chart - chAFC:

Sub ChartCollections()

 

Debug.Print ThisWorkbook.Charts.Count & " charts"

 

Debug.Print chAFC.SeriesCollection.Count & " series"

 

End Sub

  1. Clear the contents of the Immediate window and run the subroutine again:
Result

This is the output you should see.

 
  1. Save and close the workbook.
This page has 0 threads Add post