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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
