Microsoft Office Tutorials and References
In Depth Information
Chapter 13: Introducing VBA
In addition to Sub procedures, a VBA module can have Function procedures.
A Function procedure returns a single value. A function can be called from
another VBA procedure, or used in a worksheet formula. This topic does not cover
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 manipulate them
using VBA code.
Object classes are arranged in a hierarchy.
Objects can act as containers for other objects. For example, Excel is an object
called Application, and it contains other objects, such as Workbook and
CommandBar objects. The Workbook object can contain other objects, such as
Worksheet objects and Chart objects. A Worksheet object can contain objects such
as Range objects, PivotTable objects, embedded chart objects (called ChartObjects),
and so on. The arrangement of these objects is referred to as Excel’s object model .
Like objects form a collection .
For example, the Worksheets collection consists of all the worksheets in a particular
workbook. The Charts collection consists of all Chart sheet objects. The ChartObjects
collection consists of all embedded charts. Collections are objects in themselves.
When you refer to a contained or member object, you specify its position in
the object hierarchy 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.xls as
This refers to the Book1.xls workbook in the Workbooks collection. The
Workbooks collection is contained in the Excel Application object. Extending this
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:
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