VBA Code
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.
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.
Worksheets Collection
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.
ActiveWorkbook.Sheets("Sheet1").
ActiveWorkbook.Worksheets("Sheet1").
ActiveWorkbook.Sheets(1).
ActiveWorkbook.Worksheets(1).
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.
Main
ActiveWorkbook.Worksheets(1)
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
ActiveWorkbook.Worksheets(isheetno).Activate
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
ActiveWorkbook.Sheets(isheetno).Activate
Call Msgbox(Sheets(isheetno).Name & " is active" & vbclrf & _
"This has index number " & Sheets(isheetno).Index)
Next isheetno
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.
ThisWorkbook.NewWindow
Windows(2).Activate
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
Call MsgBox(objworksheet.Name)
Next objworksheet
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
ErrorHandler:
Wsh_Exists = False
End Function
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext