FIGURE 1-14: You can give your audience a choice in how they view data.
Chapter 2
Getting Cozy with the Visual Basic Editor
IN THIS CHAPTER
The Visual Basic Editor (VBE) is the environment where all Excel macros are written or recorded. The VBE is included with Excel free of charge. Even if you never record one macro, the VBE is in the background waiting to be used. When you create a macro, the VBE quietly comes to life ready to process the various procedures and routines you give it.
In this chapter, you take your first look behind the curtain to explore the VBE.
Working in the Visual Basic Editor
The VBE is actually a separate application that runs when you open Excel. To see this hidden VBE environment, you need to activate it. With Excel open, one of these two ways does the trick:
Press Alt+F11.
Click the Visual Basic button on the Ribbon’s Developer tab.
To return to Excel, press Alt+F11 or click the Close button in the VBE.
Figure 2-1 shows the VBE with the key parts identified. Chances are your VBE program window won’t look exactly like what’s shown in Figure 2-1. The VBE contains several windows and is highly customizable. You can hide windows, rearrange windows, dock windows to the main window, and so on.
FIGURE 2-1: The VBE with significant elements identified.
The VBE 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. Many of the menu commands also have shortcut keys associated with them, such as Ctrl+G to show the Immediate window and Ctrl+R to show the Project Explorer.
The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands specific to the area you clicked. For example, clicking in the Project Explorer shows a different list of commands that clicking in Code pane.
The VBE toolbars
The VBE has four toolbars: Standard, Edit, Debug, and Userform. The Standard toolbar is the only one shown by default and is directly under the menu bar. It contains many of the most commonly used commands such as Return to Excel, Save, Cut, Copy, and Paste. You can customize the toolbars, move them around, display other toolbars, and so on. If you're so inclined, choose View ⇒ Toolbars to work with the VBE toolbars.
The Project Explorer
The Project Explorer 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. You explore this window in more detail in the “Working with the Project Explorer” section later in this chapter.
If the Project Explorer is not visible, press Ctrl+R or choose View ⇒ Project Explorer. To hide the Project Explorer, click the Close button in its title bar. Alternatively, right-click anywhere in the Project Explorer and select Hide from the shortcut menu.
The Code pane
A Code pane contains VBA code. Every object in a project has an associated Code pane. To view an object’s Code pane, double-click the object in the Project Explorer. For example, to view the Code pane for the Sheet1 object, double-click Sheet1 in the Project Explorer. Unless you’ve added some VBA code, the Code pane is empty.
You find out more about Code panes later in this chapter’s “Working with a Code Pane” section.
The Immediate window
The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or choose View ⇒ Immediate Window. 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 Explorer
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 clicking the plus sign (+) at the left of the project’s name in the Project Explorer. Collapse 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 collapse them.
Figure 2-2 shows the Project Explorer with two projects listed: a workbook named Book1 and a workbook named Book2.
FIGURE 2-2: The Project Explorer with two projects open, expanded to show their objects.
Every project expands to show at least one folder called Microsoft Excel Objects. This folder expands to show an item for each sheet in the workbook (each sheet is considered an object), and another object called ThisWorkbook (that represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules folder.
Adding a new VBA module
When
