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.
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.