Visual Basic for Applications is an Object-Oriented language and to make the best out of it you need to understand Excel Objects. The workbook you use in Excel is made up of different Objects.
And there are several properties that are attached to an object which you can access and there several methods which you can use on/with an object. Let’s understand this with an example.
Think about a big grocery store, which has different sections for products, and then those sections further have racks and, in those racks, have products.
So, if you want to buy clothes, you need to go to the clothes section and in the clothes section, you could have subsections for Men and Women.
And when you pick a cloth that cloth would have a specific color, size, and fabric (which are the properties of that cloth).
In the same way, in VBA, there are objects which are structured in a hierarchy.
VBA Object Model (Hierarchy)
In Excel Application, when you open a workbook, in that workbook, you have a worksheet (or multiple worksheets) and in that worksheet, you have cells and a range of cells.
Excel’s Hierarchy starts with itself Excel Application and below is the structure that defines this hierarchy:
Let’s say if you want to refer to a workbook the code you have written should be like this:
In the above code, you are referring to the workbook “Book1”.
What you have written is, you refer to the Excel application first and you have used “Workbooks” which is further a part of the Application object.
Now let’s say you want to go further and wants to refer to a worksheet in the workbook “Book1” and the code for this would be:
You know what it means, Right? But let me tell you. In the above code, after referring to the workbook “Book1” you have referred to the worksheet “Sheet1” which is a further part of the workbook.
Let’s go a bit further. Let’s refer to a specific cell in the worksheet “Sheet1” and the code for this would be:
Before you ask, let me tell you this. In the above code, you referred to cell A1 which is in the worksheet “Sheet1” of the workbook “Book1”.
When you refer to a Range object in this way, it’s called a fully qualified reference.
Note: Using the Application object is optional. Reason? When you do something in VBA you are already in the Excel Application.
Simplify the Reference to an Object
In the above code, you have used a fully qualified reference. That means you tell VBA to which workbook, worksheet, and cell to refer. But what if you are already in the Book 1 workbook?
In that case, you can simply refer to the worksheet in which you want to refer to a cell, just like the below code:
Even further, if the worksheet “Sheet1” is activated then you can simply refer to the cell or range of the cells, just like the below code:
More on VBA Objects: VBA Workbook Object | VBA Worksheet Object | VBA Range Object