Two Collections, One Object Type
- Sheets "A collection of all the sheets in the specified or active workbook."
- Worksheets "A collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet."
Another Object Type, Another Collection
Sheets: An odd Collection
- Members of the Worksheets collection, aka each worksheet
- Members of the Charts collection, aka each chart sheet.
- Excel 4.0 Macro Sheets are members of either the Excel4MacroSheets collection of the Excel4IntlMacroSheets collection.
- Excel 5.0 Dialog Sheets, members of the Dialogs collection.
|Sheets Collection...a Collection of other Collection' members|
Finding the Sheets Collection, and the sub-Collections
The Sheets collection , as well as most of the Collections whose members are included in it, are properties of the WorkBook object.
I don't understand why MicroSoft made the Dialogs collection a property of Application instead of WorkBook, but it seems to have a ramification to be discussed below.
Why care?Answer: For each Sheet of Sheets!
Identifying the Types of Sheets
Method 1: Using the .Type PropertyOne method of determining what type of sheet you've accessed is the Sheet.Type property. Unfortunately, Dialog sheets do not have the .Type property, which means using On Error when examining the Sheet.Type. This is probably related to the fact the Dialogs collection is a property of the Application object while all other sub-collections of the Sheets collection are properties of the Workbook object.
Method 2: Using the TypeName() Function
Sheets in the VBE
- The Dialogs collection is a propery of the Application object, not the Workbook object.
- The macro-types of do not have the ability to be calculated and are unlikely to be used for anything that needs manipulation by VBA, e.g. no need to sort or format on the fly. Further, VBA Code modifying macros would be a regression to some very dangerous programming practices.
- Sometimes there's the curve of maintaining an old workbook.
- More importantly, it is good to know more about how things are put together.
- For my purposes, I needed a deeper understanding of Sheets because the Window object includes a Sheets collection as a property, but not a Worksheets collection, an Excel4MacroSheets collection, or an Excel4IntlMacroSheets collection. The last two also have the ability to be frozen.