The Office applications differ in the objects they expose to VBA. To program an application, you need
tobe familiar with its object model. The object model is a hierarchy of all the objects that you find in the
application. For example, part of the Excel object model tells us that there is an Applicationobject that
contains a Workbookobject that contains a Worksheetobject that contains a Rangeobject.
VBAis somewhat easier to learn than the XLM macro language, is more powerful, is generally more
efficient, and allows you to write well-structured code. You can also write badly structured code, but by
following a few principles, you should be able to produce code that is readily understood by others and is
reasonably easy to maintain.
In Excel 5, VBAcode was written in modules, which were sheets in a workbook. Worksheets, chart
sheets, and dialog sheets were other types of sheets that could be contained in an Excel 5 workbook.
In Excel 97, Microsoft introduced some dramatic changes in the VBAinterface and some changes in the
Excel object model. From Excel 97 onward, modules are not visible in the Excel application window and
modules are no longer objects contained by the Workbookobject. Modules are contained in the VBApro-
ject associated with the workbook and can only be viewed and edited in the Visual Basic Editor (VBE)
In addition to the standard modules, class modules were introduced, which allow you to create your
own objects and access application events. CommandBars were introduced to replace menus and tool-
bars, and UserForms replaced dialog sheets. Like modules, UserForms can only be edited in the VBE
window. As usual, the replaced objects are still supported in Excel, but are considered to be hidden
objects and are not documented in the Help screens.
In previous versions of Excel, objects such as buttons embedded in worksheets could only respond to a
single event, usually the Clickevent. Excel 97 greatly increased the number of events that VBAcode
can respond to and formalized the way in which this is done by providing event procedures for the
workbook, worksheet, and chart sheet objects. For example, in Excel 2007 workbooks have 29 events
they can respond to, such as BeforeSave, BeforePrint, and BeforeClose. Excel 97 also introduced
ActiveX controls that can be embedded in worksheets and UserForms. ActiveX controls can respond to
awide range of events such as GotFocus, MouseMove, and DblClick.
The VBE provides users with much more help than was previously available. For example, as you write
code, pop-ups appear with lists of appropriate methods and properties for objects, and arguments and
parameter values for functions and methods. The Object Browseris much better than previous versions,
allowing you to search for entries, for example, and providing comprehensive information on intrinsic
Amodule is really just a word-processing document with some special characteris-
tics that help you write and test code.