Workbook and Worksheet Object in MS Excel

In MS Excel VBA, Workbook and Worksheet objects are very important. These objects play an extremely important as all the information is maintained through these objects. In this tutorial, you will learn the usage and implementation of these objects in VBA.

Object Hierarchy

In MS Excel VBA, the concept of nested objects is very common which mean that an object may contain another object in a nested form. Similarly, the nested object may contain another nested object etc. This is called Object Hierarchy.

All the objects are the child to MS Excel itself which makes it an Application object. The application object contains sub objects. For instance, MS Excel Workbook is an example of application object. This can be any workbook created by a user. The next main object carried by Workbook object is the Worksheet object. Worksheet object covers all the worksheets contained within a particular workbook. Now, Worksheet object further contains other objects, such as the Range object.

In a previous tutorial of “Create a Macro” demonstrated how to execute a code by clicking on a command button. Let’s carry forward the same for this demonstration:

In the previous demonstration, you had observed the following syntax

Range(“A1”).Value = “Hello”

But, to make it more concrete and valid, we will use the following:

Application.Workbooks(“WorkBook_Name”).Worksheets(1).Range(“A1”).Value = “Hello”

Please note that the objects are connected using a dot symbol (.). Now both the syntax generate similar but using the recent one will make sure that the Range A1 of a Worksheet 1 of a Particular Workbook is targeted.

Collections

It is quite observable that Workbooks and Worksheets are both plural in nature because they collections. The Workbooks collection carries all the workbooks currently open on a particular system. Similarly, the Worksheets collection carries all the worksheet objects contained within a particular workbook.

For instance, in the figure given below, there are 4 instances or Workbooks are shown that are open on a particular system at the time of this tutorial is being written. Under all these workbooks, MyBook.xlsx is the main Workbook associated with this tutorial.

Now, similar to Workbooks object, within the Workbook “MyBook”, you will observe multiple Worksheets under the worksheet object as shown in the figure below.

There are multiple ways available to refer or access a member of the collection, for instance, a single Worksheet object can be accessed in three ways.

1. Using the worksheet name.

Worksheets(“Group”).Range(“A1”).Value = “Information Technology”

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

Worksheets(1).Range(“A1”).Value = “Information Technology”

3. Using the CodeName.

Sheet1.Range(“A1”).Value = “Hello”

To verify the CodeName of a worksheet, open the Visual Basic Editor through Developer tab or by using Alt + F11 shortcut key. In the Project Explorer, the first name is the CodeName. The second name is the worksheet name (Group). As shown in the figure below, Sheet5 is the CodeName and Group is the Worksheet name.

It is important to note that the CodeName always remains static even if you change its name or its position on the workbook.

Properties and Methods

Properties refer to the attributes associated with an object whereas methods refer to the functions or actions that can be performed through that object.

To have a better understanding of the concept, follow the steps below:

1. Place a Command Button on a worksheet. Label it as Method and add the following line of code under the click event of the button:

Workbooks.Add

The Add method creates a new workbook in a Workbooks collection

2. Now, either add a new button with a Label Properties and add the following line of code under the click event of the button.

MsgBox Worksheets.Count

On clicking, Properties button, you will find a Msgbox showing the count of total worksheets available within the workbook.

The code view of the sheet would look like the figure below.