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.
Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.
Therefore, what most people like you need is lots of examples that you can practice. That is why the 100 Excel VBA Macros eBook exists. It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers. The book contains:
- 100 example codes to practice reading and writing macros that will embed the language into your thinking.
- An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
- Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
- Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.
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.
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.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet 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.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: