Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Difference between Sheets and Worksheets in VBA

Difference Sheets and Worksheets

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.

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *