When do I use the Sheets Collection in Excel VBA?

When do I use the Sheets Collection in Excel VBA?

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

Objects

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.

Collections

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:

  1. The Usual Worksheet that has grids of cells in them
  2. 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.


Take Away

  1. If you know the type of sheet you are working with in advance, then use their respective Collections.
  2. If you want to make changes to all the sheets in a workbook irrespective of their type, use the Sheets collection.
  3. 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.


Recommended Reading

  1. 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.
  2. 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.
  3. 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.

5 Comments

  1. Jim

    There is one other reason for using Sheets over Worksheets: it’s easier to type. However, that’s a bad reason to use it, for the reasons you cite above.

    You might want to add a paragraph explaining that being easier to type is not really a good reason to use Sheets over Worksheets.

  2. Al

    Similar to Jim, I have been using the Sheets collection because it’s easier to type, and never bothered looking into the differences between the two collections despite knowing the existence of Worksheets.

    Is it safe to say the Sheets collection has additional properties (such as Print Layout) that you can edit compared to Worksheets?

    • Ejaz

      I am trying to drive the point that these two collections are there for a reason, and I am urging my readers to try to use the right one at the right time. And I don’t intend to recommend something based on how easy it is to type. Also, I replied to Jim’s comment on my Linked in Post; I’ve been meaning to put it here, but I must have forgotten. If you are targeting very specify sheets, you can use the Code-Name property of the Worksheets; it is way more easy to type than accessing the sheets from the collections. Also it is a good practice to declare objects for things you use repeatedly in your code – declare a worksheet object, and set it to the sheet you intend to work with once, and then use the object name to refer to the sheet going forward.

      And as for you comment about Print Layouts. Although I have not looked at it in depth, I do not think the Sheets collection would let you do anything more than what you can do with the individual objects it holds. Sheets does not contain any “new” object in it; it just hosts the same Worksheet and Chart objects that are in the Worksheets and Charts collections respectively. Therefore I expect that Excel will let you do all that you can with the Worksheet object irrespective of whether you retrieve it from the Sheets or the Worksheets collection. Do let me know if it is not the case.

  3. Thanks for taking the time to provide this background information. Many of us learned VBA by looking at other people’s code and modifying it without properly understanding the underlying principles.

    • Ejaz

      Even I learnt VBA that way, but then I realized something. If you understand the underlying principles, you would not have to look at other people’s code.

      And I wanted to be able to convey that to people with my blog. And I am happy that you feel that way. You just made my day.

      Thank you for visiting. I hope my posts will make you come back. Have a great day.

Comments are closed