The easiest way to access the Visual Basic Editor in Excel is to press the key combination ALT-F11 (ie. press the ALT key, and while this is pressed down, press F11). You will be presented with the Visual Basic Editor, similar to the image below (note that your normal Excel window will remain open behind this window).
There are a number of windows that can be viewed in the Excel VBA Editor. These are controlled via the View menu at the top of the VBA Editor window. The individual windows are described below.
The Project Window opens up on the left of the VBA Editor (as shown in the above image). This window provides you with an index of all Visual Basic files, linked to all open Excel workbooks. These consist of:and
Additional files (called Modules), which contain user-defined code and are not linked to any specific worksheet
If you want to write a macro that is to be linked to a specific worksheet, then this code should be entered into the relevant worksheet object. If your code is more general, you may want to create a new module to hold this.
In the Visual Basic Editor image above, the code window for the Module 1 is displayed.
The Code Window contains the VBA code for each individual worksheet object or module. Once a worksheet object or module is opened up, you can type your VBA code directly into the code window and the VBA editor assists you with typing in valid VBA code by highlighting lines that do not adhere to the VBA code rules, as you type.
The code window for an existing worksheet object or module is opened up by double-clicking on the worksheet or module name in the Project Window. Alternatively, you can create a new module as follows:
The Properties Window lists the properties of the object that is selected in the Project Windowat design time (ie. not during run time). These properties vary, depending on the type of object that is selected (a worksheet, workbook, module, etc).
View the Immediate Window by selecting View→Immediate Window from the Visual Basic Editor, or by pressing CTRL-G.
This window assists with the debugging of code by acting as an output window for debug statements or by allowing you to evaluate expressions or execute individual lines of code.
For example, the expression, ?j followed by the return key will print out the current value of the variable j.
A useful article on the Immediate Window is provided at excelcampus.com
View the Locals Window by selecting View→Locals Window from the Visual Basic Editor. This window displays all local variables that are declared in the current procedure. It is split into columns which show the name, value and type of each variable and updates these values automatically, as the programme is executed. The Locals window is therefore useful for debugging VBA code.
The Watch Window is also useful when debugging VBA code, as it shows the value, type and context of any watch expressions that have been defined by the user.
The Watch Window can opened by selecting View→Watch Window from the Visual Basic Editor, or the window appears automatically when a 'watch' expression is defined. to define a 'watch' expression: