The Sheets Collection

Intro

While working on trying to get a new window's worksheets to have the same FreezePane settings as the original I started to get a better understanding of something that had baffled me earlier, i.e. the difference between a "Sheet" and a "Worksheet".

Two Collections, One Object Type

In the object model there is a collections called "Sheets" and one called "Worksheets".  

• 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."

Well, that makes it intuitively obvious to a  noob that there is an object type of  "Worksheet", but not "Sheet".

Another Object Type, Another  Collection

By looking at the event-triggered workbook level Subs, one can surmise there is also a Chart Object type.  (NOTE: A Chart embedded in a Worksheet is a member of a Shapes collection and is not considered to be the same object type as a Chart sheet.) 

Further investigation proves that Chart object types do exist, as well as the existence of the Charts collection. 

Sheets: An odd Collection

My observation is that normally a collection consists on homogeneous objects, i.e. all members of the Collection are of the same Object Type.  However, the Sheets collection consists of 

• Members of the Worksheets collection, aka each worksheet
• Members of the Charts collection, aka each chart sheet.

There are two other members of the Sheets collection that also have their own collection.  These are remnants of past releases that are minimally supported.

• 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.

• Workbook.Sheets
• WorkBook.Charts
• Application.Dialogs
• WorkBook.Excel4IntlMacroSheets
• WorkBook.Excel4MacroSheets
• Workbook.WorkSheets

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!

As I mentioned, I dove into this when I was working on a Window issue.  I needed to identify which sheets had which FreezePane settings and apply those settings to a new Window.  

Of course, I was primarily interested in the Worksheets.  The .FreezePane and related properties make no sense for Chart and Dialog sheets.  Macro sheets are seldom seen and most likely hidden.  I could have ignored these or I could have done it in a way that someone isn't going to complain about the outliers not being considered.

Identifying the Types of Sheets 

Like other collections, where we refer to the members directly, e.g. each Range (object) of Ranges (collection), we refer to each Sheet of Sheets.  This bit of code rolls through the Sheets collection of the Workbook, identifying each by it's .Type property.  

Method 1: Using the .Type Property

One 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.

Sub MsgBoxSheetNames()

    Dim sh As Object

           ' "Object" as type because the Sheets collection is non-homogeneous

    Dim wb As Workbook

    Dim strType As String

    Set wb = ActiveWorkbook

    For Each sh In wb.Sheets

        With sh

            On Error Resume Next ' (1) Needed because Excel 5 Dialog sheets do not have a .Type property

            Select Case .Type

                Case xlWorksheet

                    strType = "Worksheet"

                Case xlChart

                    strType = "Chart"

                Case xlExcel4MacroSheet

                     strType = "Macro"

                Case xlExcel4IntlMacroSheet

                     strType = "International Macro"

                  ' (1) Next 2 lines commented out because Excel 5 Dialog sheets do not have a .Type property

 '               Case xlDialogSheet

 '                    strType = "Dialog"

                Case Else    ' Other values.

                     strType = "Don't Know"

            End Select

            ' (1) Next two lines needed because Excel 5 Dialog sheets do not have a .Type property

            If Err.Number <> 0 Then strType = "Dialog"

            On Error GoTo 0

            MsgBox sh.Name & Chr(10) & strType

            

        End With

    Next sh

End Sub

Method 2: Using the TypeName() Function

The TypeName() function is often useful when passing an object to a Function or Sub to determine how the code should react.  For example, acceptable arguments for a parameter may be either an atomic element or an array then the TypeName() function is used to determine how to the value(s) needed.

For this case, the error handling above can be avoided by using the TypeName() function. 

There is a tradeoff.

While the code above will differentiate the 5 types of Sheets, the Typename() function will return "WorkSheet" for three kinds of sheets:  the two excel 4 macro sheet-types and the "normal" worksheet.  TypeName() for Chart and Dialog sheets return "Chart" and "DialogSheet", respecticely.

Sub MsgBoxSheetNames2()

    Dim Sh As Object

           ' "Object" as type because the Sheets collection is non-homogeneous

    Dim wb As Workbook

    Dim strType As String

    Set wb = ActiveWorkbook

    For Each Sh In wb.Sheets

        With Sh

            Select Case TypeName(Sh)

                Case "Worksheet"

                    Select Case .Type

                        Case xlWorksheet

                            strType = "Worksheet"

                        Case xlExcel4MacroSheet

                             strType = "Macro"

                        Case xlExcel4IntlMacroSheet

                             strType = "International Macro"

                    End Select

                Case Else

                    strType = TypeName(Sh)

            End Select

            MsgBox Sh.Name & Chr(10) & strType

        End With

    Next Sh

End Sub

Sheets in the VBE

There are only two kinds of Sheets that show in the VBE, non-macro Worksheets and Chart sheets.  It is not surprising these are the only types of sheets shown.

• 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.

This should cause no real problems and act as a motivator to migrate older sheet-types.

Conclusion

One may ask, "Why not just use the Worksheets and Charts collections, or to deal with the Sheets collection as if it has only Worksheets and Chart sheets?"

Good questions!  Here some two reasons this information is, or may be, important.

• 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.