The Excel Visual Basic Editor
This page provides a very brief overview of the Excel Visual Basic Editor. Further resources providing more detailed descriptions are highlighted throughout the page.
Accessing the Visual Basic Editor
The easiest way to access the Visual Basic Editor in Excel is to press the key combination Alt + F11 (i.e. 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 usual Excel spreadsheet window will remain open behind this window).
The Visual Basic Editor Windows
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
The Project Window opens up on the left of the VBA Editor (as shown in the above image). This window provides you with a VBA 'Project' for each currently open Excel Workbook.
A VBA 'Project' is a collection of all the VBA objects and modules that are linked to the current workbook, and will initially consist of:
- A Workbook object, linked to the Excel workbook;
- Worksheet objects, linked to each worksheet of the workbook.
You can also add additional Userform, Module and Class Module objects into the project (in the above image, a Module (named Module1) has been added to the VBA project for Book1.xlsm).
To create a new Userform, Module or Class Module:
- Within the Project window, right click on the object that you want to add the module to;
- Select Insert from the right-click menu and then, from the submenu, select UserForm, Module or Class Module.
Each of the objects discussed above has a code window associated with it, which should be used to store new VBA code according to the following rules:
- Code that is to be linked to a workbook should be entered into the relevant workbook object;
- Code that is to be linked to a specific worksheet should be entered into the relevant worksheet object;
- General code (not linked to a specific object) should be entered into a Module;
- If you are creating a your own Objects, the code for this should be entered into a Class Module;
- If you are creating a dialog box to interact with a user, you can use a Userform.
The Code Windows
Double clicking on any of the objects within the Project Window will open up the associated Code Window, which is where you can type your VBA code. In the Visual Basic Editor image above, the code window for the Module 1 is displayed.
As you type your VBA code directly into the code window, the VBA editor helps you to produce valid VBA code by highlighting code that is invalid.
The Properties Window
The Properties Window lists the properties of the object that is selected in the Project Window at design time (i.e. not during run time). These properties vary, depending on the type of object that is selected (a worksheet, workbook, module, etc).
The Immediate Window
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
The Locals Window
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
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:
- Highlight an expression within the VBA code
- From the Debug menu at the top of the VBA editor, select the option Quick Watch...
- Click on Add
There are also numerous options and commands for designing, executing and debugging VBA code in the menus at the top of the Excel Visual Basic Editor.
Further information on the VBA editor is provided at excel-vba.com.