VBA Code

Sheets Collections - This is all the sheets including chart sheets
Worksheets Collections - This is all the sheets excluding chart sheets

Sheets Collection

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.


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.
The ActiveSheet property can return either a Chart object or a Worksheet object depending on what is currently active.

If TypeName(ActiveSheet) = "Chart Sheet" | "Worksheet" 

Worksheets Collection

There is also a Worksheets collection that contains just the worksheets in a workbook (no chart sheets).
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.

Avoid Using Worksheet Index

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.
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.
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)
Next isheetno

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)
Next isheetno

Snippet - Identifying the Selected Sheets

Dim objworksheet As Worksheet 
   For Each objworksheet In ActiveWindow.SelectedSheets
      Call MsgBox(objworksheet.Name)
   Next objworksheet

Snippet - Worksheet Exists

Public Function Wsh_Exists(ByVal sWshName As String) As Boolean 
Dim sName As String

    On Error GoTo ErrorHandler
    sName = ThisWorkbook.Sheets(sWshName).Name
    If Len(sName) > 0 Then Wsh_Exists = True
    Exit Function
    Wsh_Exists = False
End Function

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext