Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
You can click here to download the file for this part of the lesson.
You can click here to download a file containing the completed code.
Download and extract the workbook linked to in the Files Needed section above.
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:
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:
This worksheet contains a collection of four ListObjects and a collection of four ChartObjects.
Each chart also contains collections of other objects:
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.
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 this subroutine in Module1.
Write the name of the Workbooks collection followed by a full stop:
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:
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:
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:
These are the results you will see if you only have open the workbook that you downloaded earlier.
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 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):
The output shows the counts of the collections belonging to the new workbook, rather than the one in which the code is stored.
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:
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:
The result will show the counts of collections in the workbook in which the code is stored, regardless of which workbook is active.
Some collections require an explicit reference to the object which contains the collection. Create a new subroutine called ReferToEmbeddedCollections in Module1:
Create a subroutine as shown here.
The NFL 2017 worksheet contains two tables, referred to as ListObject 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:
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:
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:
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:
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:
You must refer to the container of the ChartObjects collection.
Clear the contents of the Immediate window and run the subroutine again:
The results will appear as shown here.
To practise referring to collections:
Sub ChartCollections()
End Sub
Sub ChartCollections()
Debug.Print ThisWorkbook.Charts.Count & " charts"
End Sub
Sub ChartCollections()
Debug.Print ThisWorkbook.Charts.Count & " charts"
Debug.Print SeriesCollection.Count & " series"
End Sub
The SeriesCollection collection will not be recognised. Click OK to proceed.
Sub ChartCollections()
Debug.Print ThisWorkbook.Charts.Count & " charts"
Debug.Print chAFC.SeriesCollection.Count & " series"
End Sub
This is the output you should see.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.