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 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 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 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.
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
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
In Excel VBA you should use Worksheets() instead of Sheets() when referencing a sheet from another sheet in a formula.