VBE-Visual Basic Editor

Forward
The Majority of the following text was taken verbatim from a publication "The Visual Basic Editor" by Martin Green who has graciously given permission to republish in Wiki format.

About the Visual Basic Editor
The Visual Basic Editor is where you create, edit and manage your visual basic code. You can use the Visual Basic Editor from within most Microsoft Office programs from Office 97 onwards. From Office 2000 onwards Word, Excel, Access, PowerPoint, FrontPage and Outlook all use the same Visual Basic Editor. Access 97 retains the older style module editing window. Outlook 97 did not have a VBA code editing tool. The Visual Basic Editor can only be opened from within a Microsoft Office host application. All the programs use the same Visual Basic Editor, which adapts itself to suit the host program. You can have several copies of the Visual Basic Editor open at one time. For example, you might be working in Excel with the Visual Basic Editor window open and, at the same time, working in the Visual Basic Editor window in Access.

Opening the Visual Basic Editor
Open the Visual Basic Editor from the Tools menu by choosing Tools > Macro > Visual Basic Editor (Fig. 1). Alternatively, use the keyboard shortcut [Alt]+[F11].

If the file in which you are working already contains macros, you can go straight to the macro code by choosing Tools > Macro > Macros, or use the keyboard shortcut [Alt]+[F8], to open the Macros dialog box. Select a macro name and click the [Edit] button. If you do a lot of macro work in Word, Excel or PowerPoint, you might also find it useful to open the Visual Basic toolbar (View > Toolbars > Visual Basic) which has buttons offering quick access to several useful commands (Fig. 2).

In addition to these methods, in Access the VBA code can be viewed from either the main database window or from the design view of a form or report. To go direct to a module's code window click the Modules tab of the database window and double-click the name of the module. Alternatively, click the module name to select it and click the Design button (Fig. 3), or click the module name and choose View > Code.

To view the code module of a form or report from the database window, select its name and choose View > Code (Fig. 4). In addition to these methods you can use the View > Code command from the design view of an Access form or report.

All these methods can be applied to Access 97 which, although it does not have the Visual Basic Editor, will display the code window for the chosen module. When the Visual Basic Editor is open a button for it is displayed on the Windows taskbar (Fig. 5). If more than one Visual Basic Editor is open at the same time (because you are working with code in more than one program) then each will have its own button on the taskbar.

Closing the Visual Basic Editor
Since the Visual Basic Editor occupies its own program window, it can be closed as you would any other program by clicking the Close button in the upper-right corner of the window, or by choosing Close and Return to <...program name...> from the Visual Basic Editor's File menu (Fig. 6). If you close the host program its Visual Basic Editor window will close automatically. A program's Visual Basic Editor can not remain open after the program itself has closed. It is important to remember that closing the Visual Basic Editor window will not prompt you to save your work. Changes to a file's code module (including changes to code in Word's Normal.dot and Excel's Personal.xls) are considered changes to the file itself and therefore no warnings about saving changes are displayed until the file itself is closed. You can save the changes manually by clicking the Save button in either the Visual Basic Editor or the host program, or by choosing Save from the host program's File menu or Save <...filename...> from the Visual Basic Editor's File menu. In the case of Normal.dot and Personal.xls, any changes to their code will prompt a message to save the file when you close down Word or Excel (respectively).

The Menu Bar
The Menu Bar gives access to all the commands available in the Visual Basic Editor.

The Toolbar
The Toolbar has buttons giving access to the more commonly used commands. In the illustration above (Fig. 7) the Standard toolbar is shown. There are also available Debug, Edit and UserForm toolbars which can be displayed by choosing View > Toolbars or rightclicking on a toolbar or the menu bar and choosing from the context menu.

The Code Window
The Code Window is where the VBA code itself is displayed, written and edited. It is your main workspace when you are creating VBA procedures and works like a word processor. You can modify the appearance of your code by changing the font, point size and colour by choosing Tools > Options > Editor Format. Code is normally displayed in a monospaced font (Courier New) for legibility. If there are no files containing code open when you open the Visual Basic Editor, there will be no code window open. The Visual Basic Editor can have more than one code window open at a time. They can be managed in the usual way from either the Window menu or from the familiar Minimize, Restore/Maximize and Close buttons in the upper-right corner of the Visual Basic Editor window. At the top of the Code Window are two combo boxes, labelled "General" and "Declarations". The General combo box displays a list of all the objects to which the current code module applies (such as all the controls on a VBA UserForm or an Access form). In the case of an object that does not contain other objects, such as an Excel workbook or worksheet module or a Word document module only that item is shown. When an item has been chosen from the General list the Declarations combo box displays a list of that item's events for which a code procedure can be written. The illustration below shows a typical list for a VBA UserForm (General) and a command button (Declarations)(Fig. 8).

The Project Explorer
The Project Explorer is organized like the familiar Windows Explorer file manager, showing you the various objects, code modules, class modules or UserForms that are currently available. It uses a folder-tree structure to display the various elements. A plus symbol next to an object indicates that it contains other objects. Click the plus symbol to reveal the objects. The symbol then changes to a minus symbol which in turn hides the objects when clicked. Double-click an object in the Properties Window to see its code (if any) displayed in the Code Window. If code is password protected, you will be prompted to enter the password when you click the plus button, before the code is revealed. The Project Explorer is normally docked in the upper-left corner of the Visual Basic Editor window. Drag its title bar to dock it against the top, right or bottom edges of the Visual Basic Editor or to have it float anywhere within the main window. The Project Explorer can be resized by dragging its edges, and switched off by clicking its Close button. If the Project Explorer is not visible it can be switched on from the View menu.

The Project Explorer in Excel
In Excel the Project Explorer displays all the workbooks currently open in Excel as well as any loaded add-ins and, if present, the Personal Macro Workbook (Personal.xls). Note that, unless explicitly named, the Visual Basic Editor calls an Excel file VBAProject because a the code component of a file is referred to as a VBA Project. You can give your VBA Project a meaningful name by highlighting it in the Project Explorer and choosing Tools > VBAProject Properties > General and changing the Project Name (alternatively right-click on the item in the Project Explorer and choose from the context menu). The illustration below (Fig. 9) shows that an Excel add-in (Funcres.xla) is currently loaded. Third-party add-ins (this one is the Analysis ToolPak from Microsoft) are usually password protected so that you can not view or edit the code.

The Project Explorer in Access
Each instance of the Access application can open only one database file at a time, so the Project Explorer displays details for only one VBA project, the current database. Access is able to run multiple instances (i.e. to open another database you open a new copy of Access) and each instance can open its own copy of the Visual Basic Editor. In an Access database code can be stored in "class objects" like forms or reports, or in modules. The code stored in a class object is specific to that object, so each is listed separately in the Project Explorer. This makes it easy to find and view the code module of a form or report simply by double-clicking its name in the Project Explorer. A single database file might have many of these "class modules" (Fig. 10). Code for general use, such as custom functions and procedures likely to be used by more than one process, are stored in regular VBA code modules.

The Project Explorer in Word
Word bases all documents on the Normal template so this is always shown in the Project Explorer. The template object has its own code module ("ThisDocument") and any additional code modules contained in the Normal template (Normal.dot) are also displayed. If a document is open that is based on a template other than Normal.dot a reference is shown (Fig. 11) and the document's underlying template, its objects and any modules it contains are all listed in the Project Explorer window.

The Properties Window
The Properties Window displays the properties of the object currently selected in the Project Explorer. Don't confuse this with what happens to be on view in the Code Window. Like the Project Explorer, the Properties Window can be resized and docked against the edge of the main Visual Basic Editor window or floated anywhere within the window. Some objects have few properties, others have many. A code module has only one property, its name, whereas an Excel workbook or worksheet, a Word document (Fig. 12) or a VBA UserForm has many properties (Fig. 13). The main part of the properties window is divided into two columns. The left column shows the property name and the right column shows its current setting. The value of a property can be changed by either typing directly into the right-hand column or choosing from the list provided. You can opt to see the properties arranged in alphabetic order, or grouped by category. At the top of the Properties Window is a combo box. This displays a list of related items when the object whose properties are currently shown itself contains other objects. In the illustration below (Fig. 14) a list of UserForm control objects is shown.