Excel 2010 VBA Lesson 16: Introduction to Excel VBA Object

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit

 [Lesson 15]<<[Table of Contents]>>[Lesson 17]

16.1 Object-Oriented Programming

Excel 2010  VBA  is an object-oriented programming language. An Excel 2010 VBA  object has certain functions and properties, and contain data. Some of the objects in Excel 2010 VBA  are Workbook, worksheet, Range, Cells, Chart, Pivot Table and more. In order to view the Excel 2010 VBA  objects, click object browser in the Excel 2010 VBA  editor and you will be presented with a list of objects(or classes) together with their properties and methods, as shown in Figure 16.1.




vb2010 Figure 16.1

Figure 16.1

If you have inserted some Active-X controls into the UserForm of a worksheet, clicking on the UserForm will reveal the objects together with the associated events, as shown in Figure 16.2

vba2010_fig16.2

Figure 16.2

16.2: Object Properties

An Excel 2010 VBA  object has properties and methods. Properties are the characteristics or attributes of an object. For example, Range is an Excel 2010 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 16.1

Private Sub CommandButton1_Click()
 Range("A1:A6").Value = 10
End Sub

As value is the default property, it can be omitted. So the above procedure can be rewritten as:

Private Sub CommandButton1_Click()
 Range(“A1:A6″)= 10
End Sub



Cells is also an Excel 2010 VBA  object, but it is also the property of the range object. So an object can also be a property, depending 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, the cells property specifies only cells(1,1) to be filled with yellow color, it sorts of overwriting the range specified by the Range object. If you wish to fill the cells from A1 to A3 with the yellow color you can use the following syntax:

Range(“A1:A3″).Interior.Color = vbYellow

Another object is the font that belongs to the Range object. The font object has its properties. In the following example

Range(“A1:A4”).Font.Color=vbYellow

, the color property of the object Font will fill all the contents from cell A1 to cell A4 with yellow color.

Sometimes 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 it or by highlighting it then press the Enter key. The IntelliSense drop-down is shown in Figure 16.3

vba2010_figure16.3

Figure 16.3



  [Lesson 15]<<[Table of Contents]>>[Lesson 17]

 

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on Reddit