Sheets Vs. Worksheets in Excel VBA

If you are a beginner of Excel VBA, you will be confused with Worksheets and Sheets. What is the difference between Worksheets and Sheets? Can they be used synonymously?

Sheets Object

Sheets Object is a collection of all types of sheets in the specified or active workbook. There are 5 different types of sheet:

• Worksheet – the sheet with the gridlines and cells.
• Chart – the sheet which contains a single chart.
• DialogSheet – an Excel 5 dialog sheet. Replaced by VBA UserForms.
• Macro sheets – Excel version 4 macro sheet. Replaced by VBA.
• International Macro sheet – Excel version 4 international macro sheet. Replaced by VBA.

The Sheets collection can contain Charts and Worksheets object. Although today we only use 2 types of Sheets, Worksheets and Chart Sheets, there used to be 3 more types of Sheets, like Dialog Sheets or Macro Sheets. You may still have in your company old excel files that use them. In that case if you check the Sheets collection you'll seem them all there.

Worksheets Object

Worksheets Object is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

Charts object

Charts object is a collection of all the chart sheets in the specified or active workbook. The Chart object is also a member of the Sheets collection.

Examples

Sub SheetsVsWorksheets()
    Dim SheetsInNew As Integer
    Dim sht
    Dim ws
    Dim cht
    
    SheetsInNew = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1 'set create a new workbook include 1 worksheet
    
    Workbooks.Add   'add a new workbook
    
    'add 5 different types of sheet
    Sheets.Add Type:=xlWorksheet    'Worksheet
    Sheets.Add Type:=xlChart    'Chart
    Sheets.Add Type:=xlDialogSheet  'Dialog sheet
    Sheets.Add Type:=xlExcel4MacroSheet 'Excel version 4 macro sheet
    Sheets.Add Type:=xlExcel4IntlMacroSheet 'Excel version 4 international macro sheet
    
    Debug.Print "Sheets count: " & Sheets.count
    'Loop through each Object in the Sheets collection
    For Each sht In ActiveWorkbook.Sheets
        Debug.Print "Sheet Name: " & sht.Name & ", Type: " & TypeName(sht)
    Next sht
    Debug.Print
    
    Debug.Print "Worksheets count: " & Worksheets.count
    'Loop through each Worksheet object in the Worksheets collection
    For Each ws In ActiveWorkbook.Worksheets
        Debug.Print "Worksheet Name: " & ws.Name & ", Type: " & TypeName(ws)
    Next ws
    Debug.Print
    
    Debug.Print "Charts count: " & Charts.count
    'Loop through each Chart object in the Charts collection
    For Each cht In ActiveWorkbook.Charts
        Debug.Print "Chart Name: " & cht.Name & ", Type: " & TypeName(cht)
    Next cht
    
    Application.SheetsInNewWorkbook = SheetsInNew   'reset settings
End Sub

Result

Results in the Immediate window:

Sheets count: 6
Sheet Name: Macro2, Type: Worksheet
Sheet Name: Macro1, Type: Worksheet
Sheet Name: Dialog1, Type: DialogSheet
Sheet Name: Chart1, Type: Chart
Sheet Name: Sheet2, Type: Worksheet
Sheet Name: Sheet1, Type: Worksheet

Worksheets count: 2
Worksheet Name: Sheet2, Type: Worksheet
Worksheet Name: Sheet1, Type: Worksheet

Charts count: 1
Chart Name: Chart1, Type: Chart