Worksheet vs Sheet in Excel VBA (Visual Basic for Applications)

Worksheet

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

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

Leave a Reply

Your email address will not be published.