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.
|
Operation |
Working |
|---|---|
|
Sheets(n) |
This is used to refer to the nth sheet starting from the first sheet |
|
Worksheets(n) |
This is used to refer to the nth sheet itself. |
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.