Programming Excel with VBA and .NET

Work with Worksheet Objects

Worksheets are the workhorses of Excel. Most of the time when you program Excel, you are doing something with a worksheet, either with the active sheet or some other sheet that you specify. Use the Worksheets collection to create new worksheets or to refer to a specific worksheet. For example, to create a new, empty worksheet in Excel, use the Add method on the Sheets or Worksheets collection:

Dim ws1 As Worksheet
Dim ws2 As Sheet
 
Set ws1 = Worksheets.Add
Set ws2 = Sheets.Add

Once you’ve declared a worksheet variable and assigned it a reference to a Worksheet object, as in the previous code for creating a new worksheet, you can use the variable to refer to the worksheet’s properties and methods. You can also use the ActiveSheet method to refer directly to the worksheet that currently has the focus, or refer to a specific worksheet as a member of the Worksheets collection.

For example, to set the text for all cells in the current worksheet to bold, you can use either of the variables in the preceding example to return an object that represents all the cells in the worksheet:

ws1.Cells.Font.Bold = True

You could accomplish the same task for the current worksheet by using the ActiveSheet property, which represents the active worksheet. This is the most common way to refer to the properties and methods of the currently active worksheet:

ActiveSheet.Cells.Font.Bold = True

You can also refer to a specific worksheet as a member of the Worksheets collection:

Worksheets("WombatBattingAverages").Cells.Font.Bold ...