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

  1. Application Objects
  2. Workbook Objects
  3. Range objects
 

     Now to fetch the value from the cell A1 we need to follow the above hierarchy to reach that cell object.

Application.workbooks(“book1.xlsx”).worksheets(1).range(“A1”).value   

 

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

 

Range("A1").value

 

 

OBJECT COLLECTION

 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

  1. PROPERTIES
  2. METHODS
  3. EVENTS

 

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.

 

Sub DisplayCellValue()   

Cellvalue =Worksheets(“Sheet1”).Range(“A1”).Value  

 MsgBox Cellvalue

End Sub

 

Let’s take an example of value propertyType 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 methodThis example will copy the value of cell A1 and paste it in cell D1.

 

Sub Copydata()   

Range(“A1”).Copy destination:=Range(“D1”)

End Sub

 

 

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.

 

 

© Snagoff. All right reserved.