Excel VBA – Workbook and Worksheet Object in Excel

In Excel VBA, the Workbook object represents a single Excel workbook. It contains a collection of Worksheet objects, which represent the individual worksheets in the workbook. The Worksheet object has a number of properties and methods that allow you to manipulate the worksheet, such as its cells, formatting, and data.

Excel VBA: Workbook Object

The Workbook object has a number of properties and methods that allow you to manipulate the workbook, such as its name, sheets, and macros. Some of the most commonly used Workbook object properties and methods include:

• Name: The name of the workbook.
• Sheets: A collection of Worksheet objects that represent the worksheets in the workbook.
• Macros: A collection of Macro objects that represent the macros in the workbook.

Excel VBA: Worksheet Object

The Worksheet object has a number of properties and methods that allow you to manipulate the worksheet, such as its cells, formatting, and data. Some of the most commonly used Worksheet object properties and methods include:

• Cells: A collection of Cell objects that represent the cells in the worksheet.
• Rows: A collection of Row objects that represent the rows in the worksheet.
• Columns: A collection of Column objects that represent the columns in the worksheet.
• Range: A Range object that represents a range of cells in the worksheet.
• DataFormat: The formatting of the data in the worksheet.

Using the Workbook and Worksheet Object in Excel VBA

To use the Workbook and Worksheet object in Excel VBA, you first need to declare a variable that can hold a reference to the object. You can then use the object’s properties and methods to manipulate the workbook or worksheet.

The Create a Macro chapter illustrates how to run code by clicking on a command button. We used the following code line:

but what we really meant was:

Note: the objects are connected with a dot. Fortunately, we do not have to add a code line this way. That is because we placed our command button in create-a-macro.xlsm, on the first worksheet. Be aware that if you want to change things on different worksheets, you have to include the Worksheet object. Read on.

Collections

You may have noticed that Workbooks and Worksheets are both plural. That is because they are collections. The Workbooks collection contains all the Workbook objects that are currently open. The Worksheets collection contains all the Worksheet objects in a workbook.

You can refer to a member of the collection, for example, a single Worksheet object, in three ways.

1. Using the worksheet name.

2. Using the index number (1 is the first worksheet starting from the left).

3. Using the Code Name.

To see the Code Name of a worksheet, open the Visual Basic Editor. In the Project Explorer, the first name is the Code Name. The second name is the worksheet name (Sales).

Note: the Code Name remains the same if you change the worksheet name or the order of your worksheets so this is the safest way to reference a worksheet. Click View, Properties Window to change the Code Name of a worksheet. There is one disadvantage, you cannot use the Code Name if you reference a worksheet in a different workbook.

Properties and Methods

Now let’s take a look at some properties and methods of the Workbooks and Worksheets collection. Properties are something which an collection has (they describe the collection), while methods do something (they perform an action with an collection).

Place a command button on your worksheet and add the code lines:

1. The Add method of the Workbooks collection creates a new workbook.

Note: the Add method of the Worksheets collection creates a new worksheet.

2. The Count property of the Worksheets collection counts the number of worksheets in a workbook.

Result when you click the command button on the sheet:

Note: the Count property of the Workbooks collection counts the number of active workbooks.

Conclusion

The Workbook and Worksheet object are two of the most important objects in Excel VBA. They allow you to manipulate workbooks and worksheets, which is essential for automating tasks and creating macros.