Microsoft Office 2000/Visual Basic Programmer's Guide

Referring to a Worksheet Object

Because a Worksheet object exists as a member of a Worksheets collection, you refer to a worksheet by its name or its index value. In the following example, both object variables refer to the first worksheet in a workbook:

Sub ReferToWorksheetExample()
   ' This procedure illustrates how to programmatically refer to
   ' a worksheet.
   Dim wksSheetByIndex     As Excel.Worksheet
   Dim wksSheetByName      As Excel.Worksheet
   
   With ActiveWorkbook
      Set wksSheetByIndex = Worksheets(1)
      Set wksSheetByName = Worksheets("Main")
      If wksSheetByIndex.Index = wksSheetByName.Index Then
         MsgBox "The worksheet indexed as #" _
            & wksSheetByIndex.Index & vbCrLf _
            & "is the same as the worksheet named '" _
            & wksSheetByName.Name & "'", vbOKOnly, "Worksheets Match!"
      End If
   End With
End Sub

The ReferToWorksheetExample procedure is available in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

Note   You can also use the Application object's ActiveSheet property to return a reference to the currently active worksheet in the currently active workbook.

You can use the VBA Array function to work with multiple worksheets at the same time, as shown in the following example:

Sub ReferToMultipleSheetsExample()
   ' This procedure shows how to programmatically refer to
   ' multiple worksheets.
   Dim wksCurrent As Excel.Worksheet
   
   With ActiveWorkbook.Worksheets(Array("Employees", "Sheet2", "Sheet3"))
      .FillAcrossSheets (Worksheets("Employees").UsedRange)
   End With
   Stop
   ' The worksheets named "Sheet2" and "Sheet3" should now
   ' contain the same table that is found on the "Employees"
   ' sheet. Press F5 to clear the contents from these worksheets.
   For Each wksCurrent In ActiveWorkbook _
      .Worksheets(Array("Sheet2", "Sheet3"))
      wksCurrent.UsedRange.Clear
   Next wksCurrent
End Sub

The ReferToMultipleSheetsExample procedure is available in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

You can specify or determine the name of a worksheet by using its Name property. To change the name of a new worksheet, you first add it to the Worksheets collection and then set the Name property to the name you want to use.