Every workbook has a Sheets collection that contains both Worksheets and Chart sheets.
This is a collection of Object datatypes.
Although Excel provides a Sheets collection as a property of a Workbook object, there is no Sheet object.
Every member of the Sheets collection is either a worksheet or a chart sheet.
Worksheet objects and chart objects both belong to the Sheets collection.
The Charts collection only includes chart sheets (not charts embedded on a worksheet).
Charts embedded on a worksheet belong to the Worksheet.ChartObjects collection.
Activating sheets is a slow process.
It is necessary to declare a sheet as a generic Object type if you want to refer to Worksheets and Chart sheets, since there is no Sheet object.
There is also an additional Worksheets collection that contains just the worksheets in a workbook.
You can refer to a worksheet either by its name or by using its index number in the collection.
You should always try and use the name if possible to specify the exact member in the Worksheets collection.
A lot of the methods only work with the Worksheets collection and not the Sheets collection (even when you refer to the same worksheet).
The index number of a worksheet in the Worksheets collection can be different from the index number of the worksheet in the Sheets collection.
In the following workbook the first sheet is a Chart sheet.
Worksheet Index Beware !!
The Index property of the Worksheet object returns the value of the Index in the Sheets collection and not the Worksheets collection.
You should avoid using the Index property of a worksheet.
Worksheet or Chart Sheet ?
The ActiveSheet property can return either a Chart object or a Worksheet object depending on what is currently active.
If TypeName(ActiveSheet) = "Worksheet"
If TypeName(ActiveSheet) = "Chart Sheet"
Processing all Worksheets
If you want to process all the worksheets in a workbook you should refer to each worksheet using its index number
Dim isheetno As Integer
For isheetno = 1 To ActiveWorksheet.Worksheets.Count
Call Msgbox(Worksheets(isheetno).Name & " is active" & vbclrf & _
"This has index number " & Worksheets(isheetno).Index)
Alternatively you could use the following:
Dim isheetno As Integer
For isheetno = 1 To ActiveWorksheet.Sheets.Count
Call Msgbox(Sheets(isheetno).Name & " is active" & vbclrf & _
"This has index number " & Sheets(isheetno).Index)
It is important to remember that the Index property of a worksheet refers to the position in the Sheets collection and not in the Worksheets collection.
If you run these two routines on a workbook that contains chart sheets this will be illustrated.
You can refer to your worksheets in VBA code using there programmatic name. This "name" property can be viewed from the Properties window in the VBE. (i.e. Sheet2.Range(A2").Select)
The Sheets collection and Worksheets collection are significantly different. The Sheets collection includes both Chart sheets and Worksheets. There is no actual "Sheet" object in Excel.
Identifying the Selected Sheets
Dim objworksheet As Worksheet
For Each objworksheet In ActiveWindow.SelectedSheets
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext