Difference between Sheets and Worksheets in VBA

I use the terms Sheet and Worksheet interchangeably when talking about Excel, I think most users do.  Google also appears to think they are the same thing; If I search for “How to loop through Sheets with Excel VBA”, all the results returned on the first page refer to Worksheets.

Yet, Sheets and Worksheets from a VBA perspective are definitely not the same.  Unfortunately, most of the VBA code I write doesn’t really consider the differences… bad habits, I know.  Hopefully, by writing this post it will inspire me to correct my bad habits, and prevent you from creating bad habits.

The difference between Sheets and Worksheets

In essence, all Worksheets are Sheets, but not all Sheets are Worksheets.  There are different types of Sheets:

• Worksheet – the sheet with the gridlines and cells
• Chart – the sheet which contains a single chart
• DialogSheet – an Excel 5 dialog sheet.  These are effectively defunct as they have been replaced by VBA UserForms
• Macro sheets – A sheet containing Excel 4 macros. These were replaced by VBA in 1995.
• International Macro sheet – A sheet containing an internationally compatible Excel 4 macro (also replaced in 1995).

Since DialogSheets, and both forms of Macro sheets were replaced in the 90’s, we can pretty much ignore them.  That leaves just two types of sheets we are likely to encounter: Charts and Worksheets.

So, in summary, when we refer to Sheets in VBA, we are talking about Charts and Worksheets.  And when we refer to Worksheets, we are excluding Charts.

What type of sheet is it?

Different sheet types have their own properties.  For example, on a Worksheet you can select cells, but you can’t on a Chart sheet, because there are no cells.  So, if we want to perform certain actions on the active sheet, we need to know which type of sheet we are on.

Sub TypeOfActiveSheet()

MsgBox TypeName(ActiveSheet)

End Sub

Running the code above will generate a message box with either “Worksheet” or “Chart” depending on the type of sheet.

Objects and collections

Understanding objects and collections will help us master the use of Charts, Worksheets and Sheets.

Worksheet – A Worksheet is an object which refers to a single worksheet.  Each worksheet is a member of the Worksheets and Sheets collection objects.

Worksheets – Worksheets is a collection object which contains all the individual Worksheet objects in a workbook.

Chart – A Chart is an object which refers to a single chart. The chart:

• Can be contained within a Chart Object for a chart contained on the face of the worksheet (outside the scope of this post).
• Can be a member of the Charts and Sheets collection objects.

Charts – Charts is a collection object which contains all the individual Chart sheet objects within a workbook.  This should not be confused with the ChartObjects object, which is the collection of charts contained on the face of the worksheet.

Sheets – Sheets is a collection object which contains all the individual Worksheet and Chart sheet objects in a workbook.

Looping through Sheets, Worksheets and Charts

When using the For Each loop, we are looping through each object within a collection.

Example 1 – Looping through each Worksheet in the Worksheets collection

The VBA code below will loop through each Worksheet in the Worksheets collection.

Sub LoopThroughWorksheets()

'Create an instance of the Worksheet object called "ws"
Dim ws As Worksheet

'Loop through each Worksheet object in the Worksheets collection
For Each ws In ActiveWorkbook.Worksheets

    MsgBox ws.Name

Next ws

End Sub

Example 2 – Looping through each Chart in the Charts collection

The following code will loop through each Chart in the Charts collection.

Sub LoopThroughCharts()

'Create an instance of the Chart object called "cht"
Dim cht As Chart

'Loop through each Chart object in the Charts collection
For Each cht In ActiveWorkbook.Charts

    MsgBox cht.Name

Next cht

End Sub

Example 3 – Looping through each Object in the Sheets collection

To loop through every sheet, we cannot declare a Sheet object, because it doesn’t exist.  The example below loops through every object (i.e. it will include Chart and Worksheet objects) contained within the Sheets collection.

Sub LoopThroughSheets()

'Create a variable to hold any object called "obj"
Dim obj As Object

'Loop through each Object in the Sheets collection
For Each obj In ActiveWorkbook.Sheets

    MsgBox obj.Name

Next obj

End Sub

Example 4 – Looping through every Sheet in the Sheets collection

As an alternative to Example 3, we could count the sheets, then loop using a For loop.

Sub LoopThroughSheets()

Dim i As Integer

'Count all sheets and loop through each
For i = 1 To Sheets.Count

    MsgBox Sheets(i).Name

Next i

End Sub

This method of looping by counting the objects will work equally well with Charts and Worksheets.

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy