Chapter 3: The Development Environment

This chapter describes in detail the operation of the VBA development environment, which since Excel 97 has appeared in its own window with its own menus , toolbars , etc. The development environment makes possible the input of program code and the definition of new forms, helps with debugging, and contains such delightful features as an object browser with cross-references to on-line help, and an "immediate window" for testing individual instructions.

3.1 The Components of a VBA Program

A VBA program is always part of an Excel workbook. Thus it is impossible to save, edit, or execute an Excel program outside of a normal Excel file. In speaking about the components of a VBA program, we mean then, the VBA components of an Excel file that are shown in the VBA development environment.

Note  

There are two special cases in saving VBA code: (1) You can export the code of a module or form as an ASCII file ”you cannot, however, execute these files. (2) You can save an Excel file as an add-in. In this case the worksheets are invisible, and the code cannot be changed. Although an Add-In has little in common visually with an Excel workbook and has quite a different purpose in life (see Chapter 14 on Excel add-ins), it is nonetheless nothing more than a special case of a normal Excel file.

An Excel application can comprise the following VBA components:

• Normal program code (module): Program code with definitions of variables , subprograms, and functions is saved in so-called modules. A module is thus a group of specially programmed procedures (subprograms) that can be used in Excel. In the development environment a module is displayed in a text window for the code that it is to contain.

• Program code for defining new object classes (class modules): Purely visually, a class module looks like a normal module, that is, a text window with program code. The difference is that class modules serve to define new objects. An introduction to programming of class modules is given in Chapter 4.

• Program code with event procedures associated to Excel objects: Every Excel sheet (table, chart) as well as the entire Excel workbook understands events, such as changing from one sheet to another, or saving or printing a workbook. When such a predefined event occurs, a so-called event procedure can be automatically triggered. The program codes for these event procedures are located in their own modules, which in turn , are associated to the corresponding Excel object. Detailed information about event procedures can be found in Chapter 4.

• Forms ( UserForm ): Since Excel 97 forms consist of two connected parts : the form itself with its controls, and the program code with the event procedures associated to the controls. (These event procedures are necessary for managing the form.) The creation and management of forms are the theme of Chapter 7.

• References: So long as you use only the standard Excel objects, you do not need to worry about references. However, as soon as you wish to use objects that are defined in external object libraries (such as in the ADO library for database programming), you must activate these with ToolsReferences (in the development environment). The references to the object libraries used are saved in the Excel file.

The first four points in this list have the following in common: The VBA code is shown in code windows that always look the same. The tools for code input and for debugging are also the same in each case.