Working in the Visual Basic Editor
The Visual Basic Editor (VBE) is a separate application where you write and edit your VBA macros. You
can’t run the VBE separately; Excel must be running in order for the VBE to run.
Activating the VBE
The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press
Alt+F11 again.
You can also activate the VBE by using the Developer➜Code➜Visual Basic command.
Understanding VBE components
Figure 1-9 shows the VBE program with some of the key parts identified. Because so much is going
on in the VBE, I like to maximize the window to see as much as possible.
Chances are your VBE program window won’t look exactly like what you see in Figure 1-9. The VBE
contains several windows and is highly customizable. You can hide windows, rearrange windows,
dock windows, and so on.
Project
Toolbar
Menu bar Code window
Properties window Immediate window
Figure 1-9: The VBE with significant elements identified.
Menu bar
The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands
that you use to do things with the various components in the VBE. You will also find that many of the
menu commands have shortcut keys associated with them.
The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut
menu of common commands.
Toolbar
The Standard toolbar, which is directly under the menu bar by default, is one of four VBE toolbars
available. You can customize the toolbars, move them around, display other toolbars, and so on. If
you’re so inclined, use the View➜Toolbars command to work with VBE toolbars. Most people just
leave them as they are.
Project window
The Project window displays a tree diagram that shows every workbook currently open in Excel
(including add-ins and hidden workbooks). Double-click items to expand or contract them. We
discuss this window in more detail in the “Working with the Project Window” section later in
this Part.
If the Project window is not visible, press Ctrl+R or use the View➜Project Explorer command. To hide
the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the
Project window and select Hide from the shortcut menu.
Code window
A Code window contains VBA code. Every object in a project has an associated Code window. To view an
object’s Code window, double-click the object in the Project window. For example, to view the Code
window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you’ve added some
VBA code, the Code window will be empty.
You find out more about Code windows later in this Part’s “Working with a Code Window” section.
Immediate window
The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View➜
Immediate Window command. To close the Immediate window, click the Close button in its title bar
(or right-click anywhere in the Immediate window and select Hide from the shortcut menu).
The Immediate window is most useful for executing VBA statements directly and for debugging your
code. If you’re just starting out with VBA, this window won’t be all that useful, so feel free to hide it
and free up some screen space for other things.
Working with the Project window
When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of
a project as a collection of objects arranged as an outline. You can expand a project by click- ing the plus
sign (+) at the left of the project’s name in the Project window. Contract a project by clicking the minus
sign (-) to the left of a project’s name. Or, you can double-click the items to expand and contract them.
Figure 1-10 shows a Project window with two projects listed: a workbook named Book1 and a work-
book named Book2.
Figure 1-10: This Project window lists two projects. They are expanded to show their objects.
Every project expands to show at least one node called Microsoft Excel Objects. This node expands
to show an item for each sheet in the workbook (each sheet is considered an object), and another
object called ThisWorkbook (which represents the Workbook object). If the project has any VBA
modules, the project listing also shows a Modules node.
Adding a new VBA module
When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. The
workbook that holds the module for the recorded macro depends on where you chose to store the
recorded macro, just before you started recording.
In general, a VBA module can hold three types of code:
➤ Declarations: One or more information statements that you provide to VBA. For example,
you can declare the data type for variables you plan to use, or set some other module-wide
options.
➤ Sub procedures: A set of programming instructions that performs some action. All recorded
macros will be Sub procedures.
➤ Function procedures: A set of programming instructions that returns a single value (similar
in concept to a worksheet function, such as Sum).
A single VBA module can store any number of Sub procedures, Function procedures, and declara-
tions. How you organize a VBA module is completely up to you. Some people prefer to keep all their
VBA code for an application in a single VBA module; others like to split up the code into several dif-
ferent modules. It’s a personal choice, just like arranging furniture.
Follow these steps to manually add a new VBA module to a project:
1. Select the project’s name in the Project window.
2. Choose Insert➜Module.
Or you can
1. Right-click the project’s name.
2. Choose Insert➜Module from the shortcut menu.
The new module is added to a Modules folder in the Project window (see Figure 1-11). Any modules
you create in a given workbook are placed in this Modules folder.
Figure 1-11: Code modules are visible in the Project window in a folder called Modules.
Removing a VBA module
You may want to remove a code module that is no longer needed. To do so, follow these steps:
- Select the module’s name in the Project window.
- Choose File➜Remove xxx, where xxx is the module name.
23 Part I: Getting Started with Excel Macros
Or
- Right-click the module’s name.
- Choose Remove xxx from the shortcut menu.
You can remove VBA modules, but there is no way to remove the other code modules —
those for the Sheet objects or ThisWorkbook.
