VBA MODULE 5
VBA Object Model
Most of the famous languages are object based or object oriented likewise VBA also deals with objects.
In real world everything is an object for example house is an object and windows, doors, etc are child objects of house. Similarly an excel worksheet is an object and a range or cell in a worksheet are child objects of that worksheet
Let’s look into the object hierarchy of VBA
- Application Objects
- Workbook Objects
- Range objects
Now to fetch the value from the cell A1 we need to follow the above hierarchy to reach that cell object.
The above code is just to understand that how VBA navigate through the object hierarchy as in most of the cases we only need to refer to range object to fetch the value of cell A1 because we usually work on the application, workbook and worksheet which are already active on Excel so there is no need to call them. Hence the above code will reduce to
A collection is a group of objects of same type and a collection is itself an object. You might have observed the use of plural words like workbooks, worksheets in the previous hierarchy example, these plural words are known as object collection and always used whenever we need to refer to a single object from that collection.
Commonly used collections are:-
Workbooks: A collection of all currently open Workbook objects
Worksheets: A collection of all Worksheet objects contained in a particular Workbook object
Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object
Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object
Three components of Object
What are Object Properties?
Properties are characteristics of an object which can be quantified or measured
For example – house is having properties like width, height, color etc.
Similarly Excel objects are having properties like value, font etc.
Let’s take an example of value property– Type the below code in the VBA window (Alt+F11) and run it using F5. This example also uses “Msgbox” function which will give a popup window displaying the cell A1 value.
What Are Object Methods?
Methods are the actions that can be performed on an object or by an object.
For example – painting and building a new room in a house are examples of methods
Likewise in VBA if you want to select a range, you will use select method or if you want to copy something you will use copy method.
A method can change an object property or makes the object do something.
Let’s take an example of copy method– This example will copy the value of cell A1 and paste it in cell D1.
What Are Object Events?
VBA is also known as event driven programming language. Its workbook and worksheet objects are associated with some events which are shown in below figure.
For example the house has child objects like door which is associated to the events such as door open, door closed etc.