In This Chapter
Understanding the Visual Basic Editor
Discovering the Visual Basic Editor parts
Knowing what goes into a VBA module
Understanding three ways to get VBA code into a module
Customizing the VBA environment
It’s an experienced Excel user, you know a good deal about workbooks, T \ formulas, charts, and other Excel goodies. Now it’s time to expand your horizons and explore an entirely new aspect of Excel: the Visual Basic Editor (VBE). In this chapter, you find out how to work with the VBE, and get down to the nitty-gritty of entering VBA code.
What Is the Visual Basic Editor*!
The Visual Basic Editor is a separate application where you write and edit your Visual Basic macros. It works seamlessly with Excel. By seamlessly, I mean that Excel takes care of opening VBE when you need it.
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 menus within Excel. To do this, choose Tools Macro Visual Basic Editor.
Understanding VBE components
Figure 3-1 shows the VBE program window, with some of the key parts identified. Because so much is going on in the VBE, maximize the window to see as much as possible.
Chances are your VBE program window won’t look exactly like the window shown in Figure 3-1. This window is highly customizable — you can hide, resize, dock, rearrange, and so on in the window.
Actually, the VBE has even more parts than are shown in Figure 3-1. I discuss these additional components in both Chapter 13 and in Part IV.
Menu bar
The VBE menu bar, of course, works 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 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.
Figure 3-1:
The VBE window is your customizable friend.
Toolbar
The Standard toolbar, which is directly under the menu bar by default (see Figure 3-1), is one of four VBE toolbars available. VBE toolbars work just like those in Excel: You can customize them, move them around, display other toolbars, and so on. Use the View Toolbars command to work with VBE toolbars.
Project Explorer window
The Project Explorer window displays a tree diagram that consists of every workbook currently open in Excel (including add-ins and hidden workbooks). I discuss this window in more detail in “Working with the Project Explorer,” later in this chapter.
If the Project Explorer window is not visible, press Ctrl+R or use the View Project Explorer command. To hide the Project Explorer window, click the Close button in its title bar (or right-click anywhere in the Project Explorer window and select Hide from the shortcut menu).
Code window
A Code window (sometimes known as a Module 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 Explorer window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you’ve added some VBA code, the Code window will be empty.
You find out more about Code windows later in this chapter’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 ViewOImmediate 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 get it out of the way.
In Chapter 13, I discuss the Immediate window in detail. It may just become your good friend!
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 window. Contract a project by clicking the minus sign (-) to the left of a project’s name. Figure 3-2 shows a Project Explorer window with three projects listed.
Figure 3-2:
This Project Explorer window lists three projects — Topic1, investments.
xls, and PERSONAL. XLS.
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 This Workbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node. And, as you see in Part IV, a project may also contain a node called Forms, which contains User Form objects (also known as custom dialog boxes).
The concept of objects may be a bit fuzzy for you. However, I guarantee that things become much clearer in subsequent chapters. Don’t be too concerned if you don’t understand what’s going on at this point.
Adding a new VBA module
Follow these steps to add a new VBA module to a project:
1. Select the project’s name in the Project Explorer window.
2. Choose Insert Module.
Or
1. Right-click the project’s name.
2. Choose Insert Module from the shortcut menu.
When you record a macro, Excel automatically inserts a VBA module to hold the recorded code.
Removing a VBA module
Need to remove a VBA module from a project?
1. Select the module’s name in the Project Explorer window.
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.
You can remove VBA modules, but there is no way to remove the other code modules — those for the Sheet objects, or This Workbook.
Exporting and importing objects
Every object in a VBA project can be saved to a separate file. Saving an individual object in a project is known as exporting. It stands to reason that you can also import objects to a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project.
Follow these steps to export an object:
1. Select an object in the Project Explorer window.
2. Choose File Export File or press Ctrl+E.
You get a dialog box that asks for a filename. Note that the object remains in the project; only a copy of it is exported.
Importing a file to a project goes like this:
1. Select the project’s name in the Explorer window.
2. Choose File Import File or press Ctrl+M.
You get a dialog box that asks for a file. You should only import a file if the file was exported using the File Export File command.
Working with a Code Window
As you become proficient with VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module. Just to make sure you’re straight with the concept, remember that a VBA module holds your VBA code, and a VBA module is displayed in a Code window.
Minimizing and maximizing windows
If you have several projects open, the VBE may have lots of Code windows at any given time. Figure 3-3 shows an example of what I mean.
Figure 3-3:
Code window overload isn’t pretty.
Code windows are much like workbook windows in Excel. You can minimize them, maximize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code window that they’re working on. Doing so lets you see more code and keeps you from getting distracted.
To maximize a Code window, click the maximize button in its title bar (or just double-click its title bar). To restore a Code window to its original size, click the Restore button in its title bar.
Sometimes, you may want to have two or more Code windows visible. For example, you might want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually, or use the Window Tile Horizontally or Window Tile Vertically command to arrange them automatically.
Minimizing a Code window gets it out of the way. You can also click the Close button in a Code window’s title bar to close the window completely. To open it again, just double-click the appropriate object in the Project Explorer window.
Creating a module
In general, a VBA module can hold three types of code:
Sub procedures: A set of programming instructions that performs some action.
Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function such as SUM).
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.
A single VBA module can store any number of Sub procedures, Function procedures, and declarations. 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 different modules. It’s a personal choice.
Getting VBA code into a module
An empty VBA module is like the fake food you see in the windows of some Chinese restaurants; it looks good but it doesn’t really do much for you. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:
Entering the code directly
Using the Excel macro recorder to record your actions and convert them to VBA code (see Chapter 6)
Copying the code from one module and pasting it into another
Pause for a terminology break
I need to digress for a moment to discuss terminology. Throughout this topic, I use the terms Sub procedure, routine, procedure, and macro. These terms are a bit confusing. Programming folks usually use the word procedure to describe an automated task. Technically, a procedure can be a Sub procedure or a Function procedure — both of which are sometimes called routines. I use all these terms interchangeably. As detailed in the following chapters, however, there is an important difference between Sub and Function procedures. For now, don’t worry about the terminology. Just try to understand the concepts.
Entering code directly
Sometimes, the best route is the most direct. Entering code directly involves . . . well, entering the code directly. In other words, you type the code via your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.
Use the Tab key to indent some of the lines to make your code easier to read. This isn’t necessary but it’s a good habit to acquire. As you study the code I present in this topic, you’ll understand why indenting code lines is helpful.
A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. Here’s an example of a single line of code split into three lines:
This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that I indented the second and third lines of this statement. Indenting makes it clear that these lines are not separate statements.
A VBA module has multiple levels of undo and redo. Therefore, if you deleted a statement that you shouldn’t have, use the Undo button on the toolbar until the statement comes back. After undoing, you can use the Redo button to perform the changes you’ve undone. This undo/redo business is more complicated to describe than it is to use. I recommend playing around with this feature until you understand how it works.
Ready to enter some real live code? Try the following steps:
1. Create a new workbook in Excel.
2. Press Alt+F11 to activate the VBE.
3. Click the new workbook’s name in the Project Explorer window.
4. Choose Insert Module to insert a VBA module into the project.
5. Type the following code into the module:
6. Make sure the cursor is located anywhere within the text you typed.
7. Press F5 to execute the procedure.
F5 is a shortcut for the Run Run Sub/UserForm command. If you entered the code correctly, Excel executes the procedure and you can respond to the simple dialog box shown in Figure 3-4.
Figure 3-4:
The Guess Name procedure displays this message box.
When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.
If you followed the previous steps, you’ve just written a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen.
For the record, this simple macro uses the following concepts, all of which are covered later in this topic:
Defining a Sub procedure (the first line) Assigning values to variables (Msg and Ans) ‘ Concatenating (joining) a string (using the & operator) Using a built-in VBA function (MsgBox) Using built-in VBA constants (vbYesNo, vbNo, and vbYes) Using an If-Then construct (twice) Ending a Sub procedure (the last line)Not bad for a beginner, eh?
Using the macro recorder
Another way you can get code into a VBA module is by recording your actions using the Excel macro recorder. If you worked through the hands-on exercise in Chapter 2, you already have some experience with this technique.
There is absolutely no way you can record the Guess Name procedure shown in the preceding section. You can record only things that you can do directly in Excel. Displaying a message box is not in Excel’s normal repertoire. (It’s a VBA thing.) The macro recorder is useful, but in many cases you’ll probably have to enter at least some code manually.
Here’s a step-by-step example that shows you how to record a macro that turns off the cell gridlines in a worksheet. If you want to try this example, start with a new, blank workbook and follow these steps:
1. Activate a worksheet in the workbook.
Any worksheet will do.
2. Choose Tools Macro Record New Macro.
Excel displays its Record Macro dialog box.
3. Click OK to accept the defaults.
Excel automatically inserts a new VBA module into the project that corresponds to the active workbook. From this point on, Excel converts your actions into VBA code. While recording, Excel displays the word Recording in the status bar. Excel also displays a miniature floating toolbar that contains two toolbar buttons: Stop Recording and Relative Reference.
4. Choose Tools Options.
Excel displays its Options dialog box.
5. Click the View tab.
6. Remove the check mark from the Gridlines option.
If the worksheet you’re using has no gridlines, put a check mark next to the Gridlines option.
7. Click OK to close the dialog box.
8. Click the Stop Recording button on the miniature toolbar.
Excel stops recording your actions
To view this newly recorded macro, press Alt+F11 to activate the VBE. Locate the workbook’s name in the Project Explorer window. You’ll see that the project has a new module listed. The name of the module depends on whether you had any other modules in the workbook when you started recording the macro. If you didn’t, the module will be named Module1. You can double-click the module to view the Code window for the module.
Here’s the code generated by your actions:
Try out this macro:
1. Activate a worksheet that has gridlines displayed.
2. Choose Tools Macro Macros
Alternatively, you can press Alt+F8. Excel displays a dialog box that lists all the available macros.
3. Select Macrol.
4. Click the Run button.
Excel executes the macro, and the gridlines magically disappear. Are you beginning to see how this macro business can be fun?
Of course, you can execute any number of commands and perform any number of actions while the macro recorder is running. Excel dutifully translates your mouse actions and keystrokes to VBA code. It works similarly to a tape recorder, but Excel never runs out of tape.
The preceding macro isn’t really all that useful. To make it useful, activate the module and change the statement to this:
This modification makes the macro serve as a toggle. If gridlines are displayed, the macro turns them off. If gridlines are not displayed, the macro turns them on. Oops, I’m getting ahead of myself — sorry, but I couldn’t resist that simple enhancement.
Copying VBA code
The final method for getting code into a VBA module is to copy it from another module. For example, a Sub or Function procedure that you write for one project might also be useful in another project. Rather than waste time reentering the code, you can activate the module and use the normal Clipboard copy-and-paste procedures. After pasting it into a VBA module, you can modify the code if necessary.
Customizing the VBA Environment
If you’re serious about becoming an Excel programmer, you’ll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible (no, please keep your shoes on), the VBE provides quite a few customization options.
When the VBE is active, choose Tools Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. I discuss some of the most useful options in the sections that follow.
Using the Editor tab
Figure 3-5 shows the options accessed by clicking the Editor tab of the Options dialog box. Use the option in the Editor tab to control how certain things work in the VBE.
Figure 3-5:
This is the Editor tab in the Options dialog box.
Auto Syntax Check option
The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, VBE flags syntax errors by displaying them in a color different from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen.
I usually keep this setting turned off because I find the dialog boxes annoying and I can usually figure out what’s wrong with a statement. Before I was a VBA veteran, I found this assistance quite helpful.
Require Variable Declaration option
If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module you insert:
Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. In Chapter 7, I explain why you should develop this habit.
Auto List Members option
If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing.
I like this option and always keep it turned on. Figure 3-6 shows an example (which will make lots more sense when you start writing VBA code).
Figure 3-6:
An example of Auto List members.
Auto Quick Info option
If the Auto Quick Info option is set, VBE displays information about functions and their arguments as you type. This can be very helpful. Figure 3-7 shows this feature in action.
Figure 3-7:
Auto Quick Info offers help about the Input Box function.
Auto Data Tips option
If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code. When you enter the wonderful world of debugging (as described in Chapter 13), you’ll appreciate this option.
Auto Indent setting
The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. I’m big on using indentations in my code, so I keep this option on.
Use the Tab key to indent your code, not the spacebar. Also, you can use Shift+Tab to “unindent” a line of code.
The VBE’s Edit toolbar (which is hidden by default) contains two useful buttons: Indent and Outdent. These buttons let you quickly indent or “unindent” a block of code. Select the code and click one of these buttons to change the block’s indenting.
Drag-and-Drop Text Editing option
The Drag-and-Drop Text Editing option, when enabled, lets you copy and move text by dragging and dropping. I keep this option on, but I hardly ever remember to use it.
Default to Full Module View option
The Default to Full Module View option sets the default state for new modules. (It doesn’t affect existing modules.) If set, procedures in the Code window appear as a single scrollable list. If this option is turned off, you can see only one procedure at a time. I keep this option turned on.
Procedure Separator option
When the Procedure Separator option is turned on, separator bars appear at the end of each procedure in a Code window. I like the idea of separator bars, so I keep this option turned on.
Using the Editor Format tab
Figure 3-8 shows the Editor Format tab of the Options dialog box. With this tab you can customize the way the VBE looks.
Code Colors option
The Code Colors option lets you set the text color and background color displayed for various elements of VBA code. This is largely a matter of personal preference. Personally, I find the default colors to be just fine. But for a change of scenery, I occasionally play around with these settings.
Figure 3-8:
Change the VBE’s looks with the Editor Format tab.
Font option
The Font option lets you select the font that’s used in your VBA modules. For best results, stick with a fixed-width font such as Courier New. In a fixed-width font, all characters are exactly the same width. This makes your code more readable because the characters are nicely aligned vertically and you can easily distinguish multiple spaces.
Size setting
The Size setting specifies the point size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and your eyesight.
Margin Indicator Bar option
This option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise, you won’t be able to see the helpful graphical indicators when you’re debugging your code.
Using the General tab
Figure 3-9 shows the options available under the General tab in the Options dialog box. In almost every case, the default settings are just fine. If you’re really interested in these options, consult the Help system for details.
Using the Docking tab
Figure 3-10 shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it is fixed in place along one of the edges of the VBE program window. This makes it much easier to
identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.
Figure 3-9:
The General tab of the Options dialog box.
Figure 3-10:
The Docking tab of the Options dialog box.
