There are two collections in Excel-VBA that lets the user access sheets in a workbook: the Sheets collection and the Worksheets collection. You can use these collections interchangeably in most situations, but they were each created for a specific purpose. Read on if you’d like to know more about why Microsoft created those two collections and their purpose.
Before we Begin
All Office Applications are designed to expose things known as Objects to the Visual Basic for Applications (VBA) Integrated Development Environment (IDE). These objects receive instructions and perform actions. Objects also interact with other objects and they follow a certain hierarchy by design. This system of objects is also known as the Object Model of the Application. You may already be familiar with some of the objects in Excel’s Object Model like Application, Worksheet and Chart objects.
A Collection, which is also an object, is an ordered group of other objects. They are used when the user needs to perform actions on multiple items that form a group. I bet every VBA beginner started using collections without even realizing they were using one. Excel’s object model has objects that come in singular and plural versions: Workbook and Workbooks for instance. The Plural versions are usually collections of their Singular counterparts. The Workbooks collection has all the Workbook objects that are open in Excel.
User Defined Collections
You can also create your own user defined collection objects. Collections help the user group objects together and access them when needed. For instance, you can loop through all the worksheets in a workbook, and add a subset of them to a collection based on some criteria. After adding the sheets to a collection, you can loop through the sheets in the collection and process them one by one.
Types of Excel Sheets
Excel has many types of sheets and only two of them are used very widely. They are:
- The Usual Worksheet that has grids of cells in them
- The Chart sheet that contains a chart in its entirety
The former is represented in VBA as the Worksheet object, and the later by the Chart object. The Worksheets collection contains all the Worksheet objects in a workbook, and similarly, the Charts collection contains all the Chart Objects.
Microsoft realized that the user would need to get access to all the worksheets and charts in their workbook and created a collection for each. While these collections are perfectly adequate for almost all operations, there are a few scenarios where the user may want to work with both collections simultaneously. Also, these two collections do not store any information related to the position of the Worksheets and Charts in Excel’s interface. Therefore Microsoft had to create an extra collection called Sheets that contains all the Worksheet and Chart objects in a workbook, in the order that they have been positioned in Excel’s interface.
You can use the Sheets collection to loop through all the sheets in a Workbook and make changes to their Window Properties, or Print Layouts. Without the sheets collection you would have had to loop through the Worksheets and Charts collections separately. The Sheets collection also comes in handy if you want to use the sheet based on its position in Excel’s interface, or rearrange sheets.
- If you know the type of sheet you are working with in advance, then use their respective Collections.
- If you want to make changes to all the sheets in a workbook irrespective of their type, use the Sheets collection.
- If you are repositioning sheets in a workbook, use the Sheets object.
Unless you are repositioning sheets, I recommend not using the Sheets collection, because there is a chance that you may retrieve a Chart object, and try to do something with it that you’d do with a Worksheet. For instance, the Worksheet object contains the Cells object, but a Chart object does not.
- Getting Started with VBA in Excel 2010 by Ben Chinowsky – This article is a must read for every beginner. I collected some background material for my article from there.
- Excel’s Worksheets and Sheets Collection – What’s the Difference? by Frank C. Rice – This article has a very detailed explanation of the differences between the two collections.
- Detecting Types of Sheets in VBA by Allen Wyatt – This article has a more detailed explanation about the types of sheet in Excel, and how to detect the type.