Visual Basic Editor in Excel

This chapter gives a very brief overview of the Visual Basic Editor in Excel. If you are an inquisitive reader and want to know even more information about the editor, then if you wish, you can easily find resources with a more detailed description.

Launching the Visual Basic Editor

The easiest way to launch the Visual Basic editor in Excel is to press the key combination Alt + F11 (i.e. press the key Alt and while holding it, press the key F11). After that, the Visual Basic Editor window will open, as shown in the picture below. Keep in mind that the Excel window remains open and behind the editor window.

Visual Basic Editor Windows

While working in the Visual Basic Editor in Excel, various windows may be open. Window management is carried out in the View menu, which is located at the top of the VBA editor window. The individual windows are described below.

Project window

Window Project opens on the left side of the VBA editor (shown in the picture above). In this window, a VBA project (VBA Project) is created for each open workbook. A VBA project is a collection of all VBA objects and modules associated with the current workbook. Initially it includes:

• Object This book (ThisWorkbook) linked to an Excel workbook;
• objects Sheet (Sheet) attached to each sheet of the current Excel workbook.

You can add objects to the project yourself Userform, Modules и Class Module. If you look at the picture above, you will see that in the VBA project for the book Book1.xlsm object added Modules with the name Module1.

Here’s how to create a new object Userform, Modules or Class Module:

• In the Project Select the workbook you want to add the object to and right-click on it.
• In the menu that appears, click Insertion and from the drop down menu select Userform, Modules or Class Module.

For each of the objects described above, a special window is provided in which new VBA code will be created and stored. The order is this:

• The code that refers to the workbook must be entered into the corresponding object This book (ThisWorkbook);
• The code that refers to the worksheet must be entered in the corresponding object Sheet (Sheet);
• A more general code must be entered in Modules;
• The code for the new object must be entered in Class Module;
• If you want to create a dialog box for user interaction, you can use Userform.

Code window

Double click on any object in the window Project opens the corresponding window —A for entering VBA code from the keyboard. One of the figures above shows the code window for Module1.

As you enter VBA code into the window —, the Visual Basic editor monitors the correctness of the input, looks for errors in the code, and highlights the code that needs to be corrected.

Properties window

In the Properties lists the properties of an object that is selected in the project window at the time of creation (not during program execution). These properties can be different depending on the type of the selected object (sheet, book, module, and others).

Debugger window (Immediate)

Window immediate can be displayed in the Visual Basic editor via the menu View > Immediate Window or by pressing a key combination Ctrl + G. This window helps you debug your code. It acts as an output area for debugging expressions and allows you to evaluate individual expressions or execute lines of code one at a time.

For example, enter the expression “?j” and press Enter – as a result, the current value of the variable will be displayed j.

Variables window (Locals)

To open a window premisesclick Locals Window the menu View Visual Basic editor. This window displays all variables declared in the current procedure. The window is divided into columns that contain the name, value, and type of each variable, and this information is updated automatically as the program runs. Window premises very useful when debugging VBA code.

Watch window (Watches)

Window Watches also helps a lot when debugging VBA code, as it can see the value, type, and context of any monitored expression that the user specifies. To open a window Watchesclick Watch Window the menu View Visual Basic editor. Also window Watches will be opened automatically if you set a monitored expression.

To set a watch expression, you need to:

1. Highlight an expression in editable VBA code.
2. On the menu Debug VBA editor click Quick Watch.
3. Click Add.

In addition to those discussed, there are many more options and commands in the menu of the Visual Basic editor in Excel that are used when creating, executing, and debugging VBA code.