Page 175 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Workbooks and Worksheets

The Sheets and Worksheets Collections

When most users think of a “sheet” in a workbook, they think of a worksheet, with rows, col- Chapter 7
umns, cells, data, and formulas. However, there are several types of sheets you can have in a
workbook. There is the worksheet, of course, but there is also a chart sheet. It’s a bit confusing
that a worksheet can contain a chart, but when you go through the Chart Wizard, you get the
option to put the chart on a separate chart sheet. The other two types of sheets are meant to
handle Excel 4 macros; there is one sheet for U.S. macros and another for international macros.

Properties

The Sheets and Worksheets collections have a number of properties in common, but there are
a few things you have to watch out for when you work with every sheet in a workbook. Those
issues are most pronounced with regard to the Count property, which is the first property
you’ll encounter in this section.

Count Property
The Count property of the Worksheets collection looks through the named workbook and
counts the number of worksheets in the workbook, while the Count property of the Sheets
collection reflects the combined number of chart sheets and worksheets in your workbook.
You can use the Count property of the Sheets and Worksheets collections to check your work-
books’ structure for accuracy before you pass the workbook to another procedure for addi-
tional processing.

Sub CheckWorkbooks()
Do While Worksheets.Count < 12
ThisWorkbook.Sheets.Add
Loop
End Sub
You’ll find the remainder of this procedure below in the discussion of the Workbook object’s
Add method.

Name Property
Part of a sheet’s public face is its name, which is how the sheet is identified on the tab bar and
one way you can identify the sheet in your VBA code. If you want to change the name of a
worksheet, you can do that by setting the Name property. For example, if you copy the weekly
sales totals to a worksheet at the end of a workbook, you can change the name of that work-
sheet to Summary using this procedure:

Sub ChangeName()
Dim strWkshtName As String
strWkshtName = "Summary"
Sheets(Sheets.Count).Name = strWkshtName
End Sub

149