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.