As a VBA developer you must have ignored while putting code and accessing a Sheet from a workbook object using “Sheets” collection or “Worksheets” collection. because both serves almost same purpose like providing sheet object by referencing sheet name of an integer index. But when you try to go little depth you would realize the difference between Worksheet and Sheets collection which are underlying Workbook Collection. All Worksheets are Sheets, but not all Sheets are Worksheets. Let’s see various types of sheets:
- Worksheet: is a collection of cells and gridlines
- Chart – the sheet which contains a single chart
- Dialog Sheet – an Excel 5 dialog sheet
- Macro Sheet – an Excel 4 macro sheet
Sheets collection shares almost similar features like worksheet but it can refer to chart sheet, dialog sheet or macro sheet hence, while iterating sheets collection make sure to apply explicit cast with Type property.
Public Sub SheetTypeExample() Dim oSheet As Worksheet 'Iterating all sheets For Each oSheet In ActiveWorkbook.Sheets If oSheet.Type = xlChart Then MsgBox "This is Chart sheet" ElseIf oSheet.Type = xlDialogSheet Then MsgBox "This is Dialog sheet" ElseIf oSheet.Type = xlExcel4IntlMacroSheet Then MsgBox "This is Macro sheet" ElseIf oSheet.Type = xlExcel4MacroSheet Then MsgBox "This is Macro sheet" ElseIf oSheet.Type = xlWorksheet Then MsgBox "This is Worksheet" Next oSheet End Sub
Next>> COM Class C# with Example