Excel Macros For Dummies. Dick Kusleika
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 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 choose to store the recorded macro, just before you start 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 module-level variables (variables that apply to all procedures in the module instead of just one), or set some other module-wide options.
Sub procedures: A set of programming instructions that performs some action. All recorded macros are 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 declarations. How you organize a VBA module is dependent on how may macros you have. If you have just a few, one module is probably all you need. If you start to get a lot of macros in a workbook, splitting them into well-named folders is best so you can easily find them later. Simply cut and paste any code from one module to another to move it.
Follow these steps to manually add a new VBA module to a project:
1 Select the project’s name in the Project Explorer.
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 Explorer (see Figure 2-3). Any modules you create in a given workbook are placed in this Modules folder.
FIGURE 2-3: Code modules are visible in the Project Explorer in a folder called Modules.
If you want to change the name of your module, select the module in the Project Explorer and press F4 to show the Properties window. Modules only have one property, Name, so it's easy to find where to change it.
Removing a VBA module
You may want to remove a code module that is no longer needed. To do so, follow these steps:
1 Select the module’s name in the Project Explorer.
2 Choose File ⇒ Remove xxx, where xxx is the module name.
Or
1 Right-click the module’s name.
2 Choose Remove xxx from the shortcut menu.
Whichever