OBJECTS, METHODS, AND PROPERTIES
To VBA, every item in the Excel environment is considered an object rather than an abstract
set of data structures or an arrangement of pixels on the screen.
Objects can contain other
At the top of the hierarchy, the largest object within the Excel object model is the Excel
application. Objects contained within this largest container include workbooks. Workbooks
contain worksheets and chart sheets, worksheets contain ranges (and can also contain chart
objects), and so on.
When you record a macro, Excel follows the Sub statement with some comment lines. These
lines, which always begin with the apostrophe character and are, by default, displayed in
green, include the name and description of your macro as well as the keyboard shortcut you
chose. These lines are ignored when you run the macro.
The first VBA statement in the CompanyAddress macro after the Sub statement and the
comments is the following:
This line illustrates an important characteristic of VBA code: The syntax of many statements
specifies first an object and then an action. An object can be a range, a worksheet, a graphic
object, a workbook, or any of the more than 100 types of objects in Excel. Here, we specify a
range object—the absolute cell reference A2—and an action—select.
The behaviors, or sets of actions, that an object “knows” how to perform are called the
methods of the object. Methods are like verbs. To understand this concept, imagine that we
are programming a robotic dog through VBA. To cause the dog to bark, we might use this
Robotic dogs, however, are (or ought to be) capable of more than just barking. For example,
you might want the dog to be able to do the following actions:
The tricks our robodog can perform, such as barking, rolling over, and fetching, are its methods.
The list of methods an object can perform depends on the object. A range object, for
example, supports almost 80 different methods that you can use to copy and paste cells, sort,
add formatting, and so on.
Like objects in the “real” world, objects in VBA also have properties. If you think of objects as
the nouns of VBA and methods as the verbs, properties are the adjectives. A property is a
quality, characteristic, or attribute of an object, such as its color or pattern. Characteristics
such as our robodog’s color (such as brown or black) in RGB values,
size (big or small) in pounds, and noise level (quiet or constantly making barking noises) in decibels.
You set a property by following the name of the property with an equal sign and a value.
Continuing our robotic dog example, we could set the size of a 10 pound dog with
Dog.size = 10
For example, the following executable statement in our MyPolyAddress macro:
ActiveCell.FormulaR1C1 = "Prof. Ming Leung"
changes one of the properties, FormulaR1C1, of the active cell, setting that property to the
value “Prof. Ming Leung”.
The remaining statements in the MyPolyAddress macro consist of three more cell-selection
and text-entry couplets. The macro selects cells A3, A4 and A5 and enters text into each cell.
The Object Browser:
You can view the various types of objects, methods, and properties available to Excel by
switching to the VBE.
To do so, select Tools, Macro, Visual Basic Editor (or press Alt+F11).
Then choose View, Object Browser (or press F2).
A window is then displayed on the right of the
Select Excel from the dropdown list of libraries at the
top of the Object Browser screen.
On the left
is a list of the various classes of objects available to Excel.
You can think of a class
as a template or description for a type of object; a specific chart, for example, would be an
object that is an instance of the Chart class.
In VBA, classes belong to a project or library.
The Object Browser lists the object classes belonging to the library Excel.
If you scroll down the classes and select a class—the Range class, for example—the right side
of the Object Browser lists the properties and methods (called the members of the class) that
belong to that object.
Collections of Objects:
You can have more than one instance of the same VBA object. Together, such instances comprise
Each instance in a collection of objects can be identified by either its index
value (its position within the collection) or its name. For example, the collection of all sheets
in a workbook is
and a specific instance of a sheet, the third one in the collection, is
If the third sheet were named Summary, it could also be identified as
Manipulating Collections with For … Each:
In VBA, each item in a collection has its own
index, but the index numbers for an entire collection are not necessarily consecutive. If you
delete one instance of an object in a collection, the index values of the remaining instances
might not be renumbered. For example, if you delete Sheets(3) from a collection of 12 sheets
in a workbook, there’s no guarantee that Excel will renumber Sheets(4) through Sheets(12)
to fill the gap.
In other programming languages, you might use a For … Next construction such as the
following to repeat an operation many times:
For n = 1 to 12 ' Activate each sheet
If you run this code in a VBA macro after deleting Sheets(3), VBA displays an error message
and stops the macro because Sheets(3) no longer exists.
To allow for nonconsecutive indexes,
VBA offers For Each … Next, a control structure that applies a series of statements to each
item in a collection, regardless of the index numbers.
For example, suppose you’d like to
label each sheet in the active workbook by entering the text Sheet 1, Sheet 2, and so on, in
cell A1 of each sheet. As you won’t, in general, know how many sheets there are in any given
workbook, you might use the following VBA macro:
n = 0
for Each Sheet In Sheets()
n = n + 1
ActiveCell.FormulaR1C1 = "Sheet" + Str(n)