Worksheets

You can refer to a worksheet by its name or index number in the Sheets collection and the Worksheets collection. If you know the name of the worksheet you want to work on, it is appropriate, and usually safer, to use that name to specify the required member of the Worksheets collection. If you want to process all the members of the Worksheets collection — in a For...Next loop, for example — you would usually reference each worksheet by its index number.

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 workbook shown in Figure 3-1, Sheetl can be referenced by any of the following:

ActiveWorkbook.Sheets("Sheet1") ActiveWorkbook.Worksheets("Sheet1") ActiveWorkbook.Sheets(2) ActiveWorkbook.Worksheets(l)

A B

&

o ■ e

F 1 G

si

1

2

Jan

Feb

Mar

-

3

122

212

232

i

5

6

7

8

1

n

1

H 1

* H ;

Chart! Sheetl Chan:2

shsää

Sheet!! -J 9

Figure 3-1

There is a trap, however, concerning the Index property of the Worksheet object in that it returns the value of the index in the Sheets collection, not the Worksheets collection. The following code tells you that Worksheets(l) is Sheetl with index 2, Worksheets(2) is Sheet2 with index 4, and Worksheets(3) is Sheet3 with index 5. You can see the message for Sheet2 in Figure 3-2.

Sub WorksheetIndex()

Dim i As Integer

For i = 1 To ThisWorkbook.Worksheets

. Count

MsgBox ThisWorkbook.Worksheets(i)

.Name & _

" has Index = " & _

ThisWorkbook.Worksheets(i)

Index

Next i

End Sub

Microsoft Excel

Figure 3-2

You should avoid using the Index property of the worksheet, if possible, because it leads to confusing code. The following example shows how you must use the worksheet Index as an index in the Sheets collection, not the Worksheets collection. The macro adds a new empty chart sheet to the left of every worksheet in the active workbook:

Sub InsertChartsBeforeWorksheets() Dim wks As Worksheet

For Each wks In Worksheets

Charts.Add Before:=Sheets(wks.Index) Next wks End Sub

In most cases you can avoid using the worksheet Index property. The preceding code should have been written as follows:

Sub InsertChartsBeforeWorksheets2() Dim wks As Worksheet

For Each wks In Worksheets

Charts.Add Before:=wks Next wks End Sub

Strangely enough, Excel will not allow you to add a new chart after the last worksheet, although it will let you move a chart after the last worksheet. If you want to insert chart sheets after each worksheet, you can use code like the following:

Sub InsertChartsAfterWorksheets() Dim wks As Worksheet Dim cht As Chart

For Each wks In Worksheets Set cht = Charts.Add cht.Move After:=wks Next wks End Sub

Chart sheets are covered in more detail in Chapter 8.

0 0

Post a comment