Collections In VBA – The Ultimate Guide
In this tutorial, we will provide a comprehensive overview of Excel VBA Collections. You will frequently need to work with Collections in your VBA code.
If you would like to learn more about VBA, then please consider joining our top-rated course.
What is a Collection?
We have already gone over what an object is, in previous tutorials. The workbook object and the worksheet object are examples of built-in Excel objects. Excel also allows you to create custom objects.
A quick reminder: If you’d like to go over some of the basics first, then please visit the following tutorials.
- Our Introduction to Macros article will get you up to speed with how to add the Developer Tab to the Ribbon. You will learn all about how to create simple macros in Excel, using the Macro Recorder. In addition, you will learn how to assign shortcut keys to macros and how to assign macros to buttons.
- Our Introduction to VBA article covers the VBA object model in Excel, and goes over what objects, properties and methods are. You will also learn about how to access the Visual Basic Editor. You will discover how to change the properties of objects.
- In our Mastering VBA Special Cells In Excel tutorial, we go over the Special Cells method in VBA, as well as the Activate, Select and Copy methods. This post will help you to get comfortable with using methods in your code. We also introduce you to Error Handling.
- The How to Use the COUNTA Function in Your VBA Code tutorial, covers the WorksheetFunction object. You will learn how to use the worksheet COUNTA Function and other built-in Excel worksheet functions in your VBA code.
- In our VBA Class Modules: A Step By Step Guide article, we go over what the difference between a normal module and a class module, is. You will also learn how to create your own custom object in a simple way.
We are now in the Intermediate section of our VBA learning journey.
Now, an Excel VBA Collection is a group of similar items. You can use built-in collections such as the Workbooks collection, which is a collection of all the open workbooks, in your VBA code.
The Worksheets Collection, on the other hand contains all the worksheets in your workbook.
Additionally, you can create your own collections.
Working with A Standard Built-In Excel VBA Collection
Excel VBA Collections have associated methods and properties. So, we can take advantage of this when working with standard built-in VBA collections.
Let’s say we wanted to count all the worksheets in a certain workbook.
We would use the Worksheets.Count property to do this.
Note: You maybe wondering what the difference is between the Worksheets collection and the Sheets collection.
The Worksheets collection refers to all the worksheets in a workbook. The Sheets collection refers to all the worksheets and chart sheets in a workbook.
Counting All the Worksheets in a Workbook
In our source example, we have a hypothetical VBA programmer working for a software development firm.
The programmer would like the user, to click on an image and then the user will be prompted for input about whether they would like to see the number of worksheets in the workbook.
If they type Yes, then the number of worksheets in the workbook will appear in a designated cell.
Additionally, the programmer would like to design the spreadsheet of interest, in a visually engaging manner.
Step 1:
Open a workbook, in Excel that contains multiple worksheets. Now, press ALT-F11 on your keyboard in order to access the Visual Basic Editor.
Step 2:
Go to the Insert Tab and select Module.
Step 3:
Enter the following code in the module we just created.
Sub countthenumberofWorksheets()
‘We start by ensuring that any existing data in cell H2 is cleared
Range(“H2”).Clear
‘We declare a variable called theAnswer and specify that the data type is string
Dim theAnswer As String
‘We now get input from the user by using an Input Box and specify that theAnswer will hold this value
theAnswer = InputBox(“Would you like to know the number of worksheets in this workbook?”, “Count Worksheets”)
‘Then we introduce conditional logic with the If statement
If theAnswer = “Yes” Then
‘In this line it specifies that if the user typed Yes,in the Input Box, then cell H2’s value
‘should show the number of worksheets in the workbook
Range(“H2”).Value = “There are” & ” ” & ThisWorkbook.Worksheets.Count & ” ” & “worksheets in this workbook”
Else
‘If the user enters any other value other than Yes in the Input Box, then we are specifying
‘in this line, that a message box will pop up instructing the user to enter the appropriate value
MsgBox “Please enter the appropriate value”
End If
End Sub
Now let’s look at the code in greater detail. The first thing we do is clear the existing contents of cell H2. This is the cell that will display the number of sheets in the workbook. We are using the Clear method of the Range object, to do this.
We then declare a variable of the string data type. The Input Box is used to get input from the user. The variable will store this input.
The conditional logic section has a simple If statement, that specifies if the user entered Yes in the Input Box, then cell H2, displays the number of worksheets in the workbook.
We are using the Worksheets.Count property of the Worksheets collection object to do this.
If the user entered any value other than Yes in the Input Box, then the user will see a message box asking them to enter the appropriate value.
Step 4:
Now select any sheet in the workbook and do the following.
Go to the Insert Tab and in the Illustrations Group, choose Pictures. Select Stock Images…
Select the Cutout People Tab. This category contains images of people in various poses with the background removed.
Type explaining in the Search box to see all the images that are related to this term for this category.
We will select the following image.
Click the Insert button.
Move the image as needed and resize the image to make it slightly smaller, ensure that no part of the image overlaps column H.
Now right-click the image and choose Format Picture…
Using the Format Picture Dialog Box, select Picture and expand the Picture Transparency section. Change the transparency to 9% as shown.
By doing this, in addition to making the image more transparent, the colours will also appear less intense.
Now close the Format Picture Dialog Box.
Step 5:
Right-click the image and select Assign Macro…
Using the Assign Macro Dialog Box, select the countthenumberofWorksheets macro that we just created and click Ok.
Step 6:
Ensure the picture is not selected.
Go to the View Tab, and in the Show Group, uncheck the Gridlines, Formula Bar and Headings options.
Click on the picture to run the macro.
You should see the following.
Type Yes in the Input Box and click Ok.
You should see the following.
Looping through all the items in a Built-In Excel VBA Collection
The ability to loop through all the items in a collection, and apply a process to each of those items is a great timesaver. This is an excellent way of automating time-consuming manual processes.
When looping through the items in a collection, we will use a specific loop structure called the For Each Loop.
Let’s consider the following scenario. We have five worksheets in our workbook as shown below.
We would like to write some code that tells Excel to loop through each worksheet in the Worksheets Collection.
The heading in cell A1, on each sheet should reflect the name of the sheet.
The heading in cell A1, on the Customers sheet will be Customers, and the heading in cell A1 on the Orders sheet will be Orders and so on.
We want the columns in each worksheet to be autofit. This means that cell A1 , in each worksheet will accommodate the length of the specific text, of the heading.
This is the entirety of the process that we would like to apply to each sheet.
Step 1:
Press ALT-F11 on your keyboard in order to access the Visual Basic Editor.
Step 2:
Go to the Insert Tab and select Module.
Step 3:
Sub loopingthroughalltheWorksheets()
‘We declare a worksheet object using the Dim keyword
Dim ws As Worksheet
‘We start the looping structure with the For Each line and this line says
‘that for every Worksheet in the Workbook
For Each ws In ThisWorkbook.Worksheets
‘This line states that the value in cell A1 of each of the respective sheets, should reflect the sheet name
ws.Range(“A1”).Value = ws.Name
‘This line states that all the columns in each worksheet, should be Autofit
‘This is a way to ensure that Cell A1, will accommodate the length of the heading
ws.Cells.EntireColumn.AutoFit
Next ws
End Sub
Now let’s look at the code in greater detail. We start off by declaring a worksheet object using the Dim keyword. We now start with a For Each Loop structure, that will allow us to loop through all the worksheets in the workbook.
You can use the For Each Loop, in any situation where you need to loop through each item in a collection and perform the same action on each of those items.
We then specify that we would like all the columns in each of the worksheets to be Autofit. So, this means cell A1 will accommodate the needed text length.
Step 4:
With the cursor positioned in the sub procedure. Press F5 to run the code. You should see the following.
How To Create A Collection – Simple Example
We will now look, at how to create a collection using VBA. A hypothetical technician working at a Gadget store, wants to create a collection for the store’s top selling items.
Step 1:
Press ALT-F11 on your keyboard in order to access the Visual Basic Editor.
Step 2:
Go to the Insert Tab and select Module.
Step 3:
Enter the following code.
Sub creatingaCollection()
‘We start by declaring our variable as a collection object
Dim topsellingItems as new Collection
End Sub
It’s as simple as that.
Adding And Removing Items From A Collection – Simple Example
Adding Items to a Collection
To add the required items to our collection. We would use the following code.
Sub creatingaCollection()
‘We start by declaring our variable as a collection object
Dim topsellingItems As New Collection
‘We will add six items to our collection using the Add method
topsellingItems.Add “Smartwatches”
topsellingItems.Add “Bluetooth speakers”
topsellingItems.Add “Electric scooters”
topsellingItems.Add “Noise-cancelling headphones”
topsellingItems.Add “VR headsets”
topsellingItems.Add “3D Printing pens”
End Sub
Removing an Item from a Collection
To refer to a specific item in a collection, to remove it, you could use the Index number of that item. The following code would remove Bluetooth speakers. This would be index number 2.
topsellingItems.Remove (2)
Note: When working with VBA collections it is important to remember that items start indexing at 1 and not 0.
Looping Through the Created Collection
As in the above example, we can use the For Each Loop structure to loop through the items in our collection.
We would like to loop through all the items in our collection and have Excel read the name of each item out loud.
So we would use the following code to do this.
Sub creatingaCollection()
‘We start by declaring our variable as a collection object
Dim topsellingItems As New Collection
‘We declare a variable of data type variant which refers to an item in the collection
Dim nameOfItem As Variant
‘We will add six items to our collection using the Add method
topsellingItems.Add “Smartwatches”
topsellingItems.Add “Bluetooth speakers”
topsellingItems.Add “Electric scooters”
topsellingItems.Add “Noise-cancelling headphones”
topsellingItems.Add “VR headsets”
topsellingItems.Add “3D Printing pens”
‘We use the For Each loop to access each item in the collection
For Each nameOfItem In topsellingItems
‘In this line we are specifying that Excel speak out loud, the name of each item in the collection
Application.Speech.Speak nameOfItem
Next nameOfItem
End Sub
To run the code insert your cursor anywhere in the sub procedure and press the F5 key on your keyboard.
You should hear Excel saying the name of each of the items, in the collection.
The Differences Between Collections and Arrays
You can use an Excel VBA array variable to store a list of items, of the same data type. There are some key differences between arrays and collections which we will review below.
Let’s review some of the additional advantages and disadvantages of using custom Excel Collections in your code.
It is very simple to create a custom collection and add items. Additionally, the concept of collections is easy to understand. You can also return a collection from a function or even convert a collection to an array, if needed.
However, there are some disadvantages associated with a custom collection. One is that you cannot change the items in the collection directly. Also when working with a list of a fixed size, it is not always advisable to use a collection due to efficiency concerns.
Learning Objectives
You now know how to:
- Use Built-In Excel VBA Collections
- Use the For Each Loop to Apply a process to each Item in a Collection
- Create your own custom Collection
- Add and Remove Items from the Collection
Additionally you have an understanding of:
- What the differences are between arrays and collections
Conclusion
Excel VBA Collections provide an efficient way, to work with multiple items. It is advisable, to learn how to use them in your VBA code.
Knowledge of how to use collections in VBA, is very useful for both intermediate and advanced level programmers.
Looking for more Excel tips? Check out The RANDARRAY Function – Mastering Excel Array Tools article here!
Special thank you to Taryn Nefdt for collaborating on this article!
