Workbook and Worksheet Object in Excel VBA

Excel VBA is an object-oriented programming language, which means in excel every component is an object. The superset of all the objects of excel is the excel application itself. The Excel Application Object is the root of all the other objects in excel. It contains Workbook Object. The Workbook Object contains another object such as Worksheet Object which includes all the worksheets present in a particular workbook. The Worksheet Object contains Cells Object. To perform any task or operation in excel we need to follow this excel object hierarchy.

 

Workbooks Object

In excel VBA, the workbooks object represents the collection of the workbook which are open in the excel application at the same time. This is the reason we write it as plural- Workbooks Object, which means it contains more than one workbook. In order to access a single workbook from the workbooks object, we can use the following VBA to access it from its name.

 

How to Refer a Workbook in VBA?

To refer a workbook in excel VBA, we can use the following different methods:

By Name

We can easily refer to a workbook using its name. We need to define a procedure in VBA and declare our excel workbook name. Make sure that the user name is only if the workbook is not saved otherwise we need to define the name along with its extension.

 

By Number

The excel application maintains an index order for all the workbooks we open. It gives numbers 1 to the first workbook, 2 to the second workbook, and so on. We can also use these numbers to refer to the workbook.

Note: It became really difficult to remember which workbook we have opened in which order. So, it is quite less used.

 

By ActiveWorkbook Property

We can refer to a workbook using the ActiveWorkbook property. Using ActiveWorkbook we can refer to the workbook which is in an active state, which means the workbook is open in excel.

 

Access Methods and Properties of a Workbook

Like any other object-oriented programming language, In VBA whenever we refer to an object it gives us access to all the methods and properties of that object. Similarly, when we access the Workbook Object, we get access to all its properties and methods.

Accessing Methods of Workbooks Object

In order to access the methods of the workbook object we will use the dot(.) operator. Once, we use our workbooks object as soon as we will use the dot(.) operator, it will display the list of methods associated with that particular object.

 

Accessing Properties of Workbooks Object

Similar to methods, we can use the dot(.) operator to access the properties of the workbook object.

 

As we can see, we have 3 different sheets currently open in our excel, if we run our procedure it will display the count as 3 in a message box.

 

Worksheets Object

In excel VBA, the worksheets object represents the collection of the worksheets which are open in the excel workbook. This is the reason we write it as plural- Worksheets Object, which means it contains more than one worksheet.

How to Refer a Worksheet in VBA?

By Name

In excel, we can refer to a sheet using its name. By default, Excel creates sheets in natural number ordering. For example, Sheet1, Sheet2, etc. We can also give our own name to these sheets.

 

By Number

We can also use sheet numbers to refer to a sheet in excel. The two operations to refer to a sheet, Sheets() and Worksheets() work in different ways while accessing the sheets by number.

 

By ActiveSheet Property

In excel VBA, we can also refer to a sheet using the ActiveSheet property.

 

As above, we can see we have the GFGCourse sheet active. When we run our procedure, it will pop up a message box with the active sheet name is.

 

Access Method and Properties of a Worksheet

Similar to the Workbook object, Worksheet Object also comes up with different methods and properties. In excel VBA, we can access methods and properties of excel worksheet objects using the dot(.) operator.

 

ankur035

Follow

Improve

Article Tags :

• Excel
• Microsoft Office
• News
• Excel-VBA
• ExcelGuide
• Technical Scripter 2022

+2 More