Chapter 19: Excel VBA Objects -Part 1

19.1: Objects

Most programming languages today deal with objects, a concept called object oriented programming. Although Excel VBA is not a truly object oriented programming language, it does deal with objects. VBA object is something like a tool or a thing that has certain functions and properties, and can contain data. For example, an Excel Worksheet is an object, cell in a worksheet is an object, range of cells is an object, font of a cell is an object, a command button is an object, and a text box is an object and more.

In order to view the VBA objects, you can insert a number of objects or controls into the worksheet, and click the command button to go into the code window. The upper left pane of the code window contains the list of objects you have inserted into the worksheet; you can view them in the dropdown dialog when you click the down arrow. The right pane represents the events associated with the objects, as shown in Figure 19.1 below.

>	Figure 19.1:Excel VBA Objects

To view all the available Excel VBA objects, you can click on the objects browser in the code window

Figure 19.2:Objects Browser

Figure 19.3: IntelliSense

19.2: Object Properties

An Excel VBA object has properties and methods. Properties are like the characteristics or attributes of an object. For example, Range is an Excel VBA object and one of its properties is value. We connect an object to its property by a period(a dot or full stop). The following example shows how we connect the property value to the Range object.

Example 19.1

Private Sub CommandButton1_Click()

Range("A1:A6").Value = 10

End Sub

In this example, by using the value property, we can fill cells A1 to A6 with the value of 10. However, because value is the default property, it can be omitted. So the above procedure can be rewritten as

Example 19.2

Private Sub CommandButton1_Click()

Range("A1:A6")= 10

End Sub

Cells is also an Excel VBA object, but it is also the property of the range object. So an object can also be a property, it depends on the hierarchy of the objects. Range has higher hierarchy than cells, and interior has lower hierarchy than Cells, and color has lower hierarchy than Interior, so you can write

Range("A1:A3").Cells(1, 1).Interior.Color = vbYellow

This statement will fill cells (1,1) with yellow color. Notice that although the Range object specifies a range from A1 to A3, but the cells property specifies only cells(1,1) to be filled with yellow color, it sort of overwrite the range specified by the Range object.

Another object is font that belong to the Range object. And font has its properties.For example, Range(“A1:A4”).Font.Color=vbYellow , the color property of the object Font will result in all the contents from cell A1 to cell A4 to be displayed in yellow color.

Sometime it is not necessary to type the properties, Excel VBA IntelliSense will display a drop-down list of proposed properties after you type a period at the end of the object name. You can then select the property you want by double clicking the it or by highlighting it then press the Enter key. The IntelliSense drop-down is shown in Figure 19.3