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



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext