Microsoft Office Tutorials and References
In Depth Information
Covering the Basics of VBA
h Objects: VBA manipulates objects contained in its host application. (In this case, Excel is
the host application.) Excel provides you with more than 100 classes of objects to
manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a
chart, and a shape. Many more objects are at your disposal, and you can use VBA code to
manipulate them. Object classes are arranged in a hierarchy.
Objects also can act as containers for other objects. For example, Excel is an object called
Application , and it contains other objects, such as Workbook objects. The Workbook
object contains other objects, such as Worksheet objects and Chart objects. A
Worksheet object contains objects such as Range objects, PivotTable objects, and
so on. The arrangement of these objects is referred to as Excel’s object model.
h Collections: Like objects form a collection. For example, the Worksheets collection
consists of all the worksheets in a particular workbook. Collections are objects in
h Object hierarchy: When you refer to a contained or member object, you specify its
position in the object hierarchy by using a period (also known as a dot ) as a separator
between the container and the member. For example, you can refer to a workbook
named Book1.xlsx as
This code refers to the Book1.xlsx workbook in the Workbooks collection. The
Workbooks collection is contained in the Excel Application object. Extending this
type of referencing to another level, you can refer to Sheet1 in Book1 as
You can take it to still another level and refer to a specific cell as follows:
h Active objects: If you omit a specific reference to an object, Excel uses the active objects.
If Book1 is the active workbook, the preceding reference can be simplified as
If you know that Sheet1 is the active sheet, you can simplify the reference even more:
h Objects properties: Objects have properties. A property can be thought of as a setting
for an object. For example, a range object has properties such as Value and Address . A
chart object has properties such as HasTitle and Type . You can use VBA to determine
object properties and also to change them. Some properties are read-only properties and
can’t be changed by using VBA.