Details of The Excel Visual Basic Editor

The Excel Visual Basic Editor can be set to appear in a variety of ways.  In the appearance shown below (the default) the majority of windows appear docked at one edge of the screen.  Code windows can be tiled together on the screen, or one can be maximized.

The main features of the screen are as follows:

• The Visual Basic Title Bar, Menu Bar and Toolbars –at the top of the screen.
• The Project Explorer Window (upper left).  To find the different parts of your project – Shortcut: Ctrl+R
• The Properties Window (lower left).  Properties of whatever has been highlighted – Shortcut: F4
• The Immediate (Debugging) Window (bottom right) – Shortcut: Ctrl+G
• Code Windows (e.g. the one showing “Option Explicit”):  Double-click on the relevant object in Project Explorer, or press F7.

Each window can be hidden by clicking the X in its top right-hand corner.  The window can be displayed or hidden using the View menu.

1.     Toolbars

To show or hide a toolbar, right-click on the menu/toolbar area, or click View > Toolbars.

1.1      The Edit Toolbar

• List Properties/Methods                 Ctrl+J
• List Constants                                Ctrl+Shift+J
• Quick Info                                     Ctrl+I
• Parameter Info                              Ctrl+Shift+I
• Complete Word                             Ctrl+Space
• Indent                                            Tab
• Outdent                                         Shift+Tab
• Breakpoint                                     F9 (Debug Menu) Click margin indicator bar
• Comment Selection                      (not on menus)
• Uncomment selection                  (not on menus)
• Toggle Bookmark                         
• Next Bookmark
• Previous Bookmark
• Clear All Bookmarks

1.2      The Standard Toolbar

Most of the buttons on the left should be obvious.

These buttons also feature on the Debug Toolbar:

• Run                                               F5
• Break                                            Ctrl+Break
• Reset
• Design

1.3      The Debug Toolbar

As the name indicates, used while debugging code.

Use the Debug menu command, Compile VBA Project, before you try to run the project, to pick up errors such as undeclared variables, syntax errors, etc.

2.     Editor Options

In the Visual Basic Editor, click Tools > Options > Editor:

The above are the settings we recommend.  Auto Syntax check, if on, will warn you of every syntax problem, with a message.  This is not needed because syntax errors show up in red anyway.  The other options are useful.  “Require Variable Declaration” is essential!  We find tab stops of 4 (the default) to be too wide.

For better visibility, you may wish to change the Font setting under “Editor Format”:

The Code Colours are worth keeping unchanged, but this is a useful place to learn the meanings of the various colours and symbols.

3.     Projects, Forms and Modules

The Visual Basic code associated with an Excel file forms a VBA Project.  You will see a VBA Project for each file that is open, including the hidden file Personal.xlsb if you have one.

A Project will consist of Excel Objects and, possibly, Forms and Modules.  Excel Objects are typically the Workbook and all the sheets.

Each Excel Object and Form will contain the programming code specific to that Form. Extra code common to more than one form may be in one or more modules.

Forms are used as dialog boxes, to ask for information that cannot be picked up directly from the Excel spreadsheet or with MsgBox or InputBox.  Such dialog boxes are modal, i.e. you cannot click on any part of the program outside of the dialog box until it is closed.

We will see more of Forms in later posts.