Section 26.1. The Visual Basic Editor | Excel 2010: The Missing Manual

26.1. The Visual Basic Editor

Before you can modify a macro, you first need to find it. The tool you use to edit macros isn't actually part of Excel. Instead, it's a separate application called the Visual Basic editor.

The Visual Basic editor is designed to edit the macros created for Office applications like Word and Excel. To show the Visual Basic editor window from inside Excel, choose Tools Macro Visual Basic Editor. When you do, Excel launches the standalone window shown in Figure 26-1.

Figure 26-1. The Visual Basic editor window is divided into three main regions . The Document window displays code (it starts off blank). In this example, there are three projects, including an Excel file named InsertHeader.xls, and two more specialized files you'll learn about in the next section. The Properties window shows individual settings that you can change for the currently selected item in the Project window.

26.1.1. The Project Window

The best way to orient yourself in the Visual Basic editor window is to start by looking at the Project window in the top-left corner. This window shows a directory tree representing all the currently open workbooks. Each open workbook becomes a separate VBA project, which means that it contains its own set of macro code.

Along with the workbooks you currently have open, you might also find a few workbooks that you don't recognize in the project window. Don't panicthese interlopers are completely Kosher. They correspond to hidden workbooks workbooks that Excel opens but doesn't show you or allow you to edit. Often, a hidden workbook loads when you activate an add-in like the Analysis ToolPak (Section 7.2.4). That's because all the code and specialized functions that the add-in provides are actually written using VBA modules (code files). A little later in this chapter, you'll see how you can use this technique to not only create macros, but also to define your own specialized functions.

Some of the unexpected workbooks that you might find in the project window include:

PERSONAL.XLS . This project is the personal macro workbook (Sidebar 25.1) that contains the macros you share between all workbooks. If you haven't recorded any macros to the personal macro workbook, you won't see this project because Excel won't have created it.
FUNCRES.XLA . This project contains the code for the Analysis ToolPak functions, if you've turned them on (Section 7.2.4).
LOOKUP.XLA . This project contains the code for the Lookup Wizard add-in, if you've turned it on (Section 11.2.5).
SOLVER.XLA . This project contains the code for the Solver add-in, if you've turned it on (Section 19.3).
SUMIF.XLA . This project contains the code for the Conditional Sum Wizard add-in, if you've turned it on.

Note: Don't try to open the add-in projects. They're password-protected so that you can't modify any of the code that they contain (or even look at it). However, you can change the macros you create, as well as your personal macro workbook.

Each workbook project in the Project window contains one or two folders. The first folder, Microsoft Excel Objects, is always present and contains a separate entry for each worksheet in the workbook. You use these entries to attach code that reacts to a specific worksheet event. For example, you could create a task that springs into action every time the person using the workbook selects this worksheet. The Microsoft Excel Objects folder also contains a single workbook objectcalled ThisWorkbookwhere you can respond to workbook actions (like saving, opening, or printing the workbook).

Note: Creating code that reacts to events is an advanced and infrequently used Excel technique. It comes in handy if you are generating complex templates that need to enforce certain rules. This book doesn't cover events.

The second folder in a workbook project is named Modules. It appears only if you've created a macro for this particular workbook. Figure 26-2 details the action inside one particular Project window.

Note: As you open new workbooks, they automatically appear in the Project window in the Visual Basic editor. Similarly, as you close workbooks, they disappear from the Project window.

26.1.2. Modules and Macros

Modules contain macro code. Ordinarily, Excel creates a new module the first time you record a macro, and names it Module1. Excel then places every macro you record into that module. If you want, you can separate macros into different modules for better organization. However, the number of modules you create, or the module you use for a given macro, has no effect on a macro's function.

Figure 26-2. In this window, there are three VBA projects. The first, FUNCRES.XLA, is an Excel add-in used for the Analysis ToolPak functions. The second, InsertHeader.xls, is an Excel workbook file with three worksheets and one module for macro code. The last, PERSONAL.XLS, is the personal macro workbook, which may also contain worksheets and macro modules (although that branch of the tree is collapsed , so these objects aren't shown).

Inside every module are one or more VBA subroutines . Each subroutine is a named unit of code that performs a distinct task. In the VBA language, subroutines start with the word Sub followed by the name on a separate line, like all VBA commands. They end with the statement End Sub. Here's an example:

 Sub MyMacro     ' Your macro code goes here. End Sub

This small snippet of VBA code illustrates two important principles. First, it shows you how to start and end any subroutine (by using the statement's Sub and End Sub). This code also shows you how to create a comment . Comments are special statements that Excel ignores completely; they're notes to yourself (for example, to explain in plain English what the following or preceding line of code actually does). To create a comment, you just place an apostrophe (') at the beginning of the line.

In Excel, each macro is a separate subroutine. (In some cases, you might want to break a macro down into more than one subroutine, but that's not necessary, and the macro recorder won't do this for you.) When the macro recorder goes to work recording a new macro, it generates a new subroutine using the name of the macro that you assigned (as explained on Section 25.2.3). It also adds any description you entered when you were creating the macro. Then, it places all the code it generates into the subroutine.

Here's the basic skeleton for the InsertHeader macro created in the last chapter:

 Sub InsertHeader( )     '     ' InsertHeader Macro     ' Macro recorded 3/6/2004 by Matthew MacDonald         ' (Code goes here.) End Sub

To take a look at the subroutines in a module, double-click the module in the Project window. The Module opens in a new window, as shown in Figure 26-3. You can scroll through this window to see all the macro procedures it contains.

Figure 26-3. In this example, the Visual Basic editor shows a portion of the code for the InsertHeader macro. If you scrolled down through the window, you'd see additional macros in separate modules if this workbook contained any. Between each subroutine is a solid line (not shown). You can also jump directly to the subroutine that interests you by selecting the subroutine's name from the list box in the top-right corner of the code window.

26.1.3. Finding and Moving Macros

With your new knowledge of VBA, you can transfer a macro from one workbook to another. Just follow these steps:

Open both workbooks in Excel.

Before you start, you need to open the workbook that contains the macro you want to move or copy (that's the source workbook ), and the workbook where you want to move the macro to (the target workbook ).
Choose Tools Macro Visual Basic editor.

The Visual Basic editor appears.
Using the Project window, expand the source workbook until you see the module where the macro is stored (this is usually named Module1). Double-click this module.

The code window appears with the macro code for the source workbook.
Scroll through the code window until you find the macro you want to copy or move. Select it all, from the starting Sub line to the concluding End Sub statement. Press Ctrl+C to copy, or Ctrl+X to cut the macro code.

If you use Ctrl+X, you remove the macro code from the source workbook, and the macro will no longer be available in that workbook.
Using the Project window, expand the target workbook. If the target workbook doesn't already contain a macro module, you need to insert one. To do so, right-click the project name (for example, "VBA Project (NewWorkbook.xls)") and choose Insert Module.

When you add a new module, it appears in the project window with a generic name (like Module1).
Double-click the new module.

A blank code window appears.
Click inside the code window and press Ctrl+V to paste in your macro code.

This action places the macro code into the new workbook. From this point on, you can use the macro whenever this workbook is open.

26.1.4. Debugging a Macro

The Visual Basic editor isn't just for editing. You can also use it to run any macro. Just open the module that has the macro you want and scroll through the code window until you're somewhere inside the macro. Now, on the Visual Basic toolbar, click the Run button (which looks like the play button on a VCR control). If you have a long-running macro, you can click the pause button to temporarily halt the code, or the stop button to abort it altogether.

Note: You might assume that Excel performs the macro on the workbook that contains the macro code, but this isn't necessarily the case. Instead, Excel runs the macro on the workbook that you looked at most recently . In other words, if you have two workbooks open, and you use the Windows task bar to switch to the second workbook and then back to the Visual Basic editor, Excel will run the macro on the second workbook.

Another neat trick that you can perform from the Visual Basic editor is macro debugging . This feature lets you walk through your macro code and run it one line at a time. After each line executes, you can take a look at what's happened so far in the workbook. Macro debugging is a great tool for finding mistakes or diagnosing bizarre behavior. It's also a good way to learn what each statement in a macro actually accomplishes.

To debug a macro, follow these steps:

Find the macro you want to debug and click anywhere inside the code.

Excel's debugging tools aren't limited to misbehaving macros. You can use them to watch any macro in action, just for the fun of it.
From the Visual Basic editor menu, select Debug Step Into, or press F8.

Excel highlights the first line of the macro (the Sub statement) in yellow, and places an arrow next to it. The arrow indicates that this line is ready to run.
Press F8 to run the highlighted line.
Once Excel runs this line, it moves to the next line (skipping any comments), and highlights it in yellow. Then it moves you to the first actual line of code in the macro, as shown in Figure 26-4.

Figure 26-4. When you press F8, Excel runs the line that's highlighted in yellow and marked with an arrow. Then it moves to the next statement, and pauses again.
Press F8 to run the highlighted line.

Excel runs the highlighted line, moves one line down to the next line, highlights it, and waits for your command. At this point, you can switch back to the workbook to see if the first line of code produced any visible effect. Be careful not to change anything in your worksheet, however. For example, if you clear the current selection or move to a different place in the worksheet, you might throw the macro off completely.

When you're finished looking at the worksheet, switch back to the Visual Basic editor.

Return to step 4, and keep repeating it for each line in the macro.

At any point, you can stop stepping through your code and run everything that's left by clicking the play button, or you can cancel the remainder of the macro commands by clicking the stop button. When you reach the last line, the macro ends.

Tip: There's another way to jump right to a macro that interests you or that you need to debug. From the Excel window, choose Tools

Macro

Macros to show the list of macros that are available. Select a macro, and click Edit to open it in the Visual Basic editor or Step Into to start debugging it.

POWER USERS' CLINIC
Adding New Modules

Excel lets you use the macros that are stored in every module of your workbook project. That means it doesn't matter how many modules you use, or even what name each module has. Excel can find and use your macro code just as easily no matter what the arrangement.

But you might choose to use multiple modules in the same workbook project to help organize your code. This approach makes sense if you have a lot of macros in one place, and you want to organize them by function so they're easier to edit and maintain in the future. For example, you might create a MyMacroCollection.xls workbook that has three macro modules: QuickCalculations, Formatting, and Printing.

Adding new modules is easy. All you need to do is right-click the name of your project in the Project window, and choose Insert Module. Once youve created a new module, you can follow the steps shown earlier to transfer a macro from one module to another.

Of course, if you create multiple modules with generic names (like Module1, Module2, and so on), you'll have a hard time keeping track of what's what. So, once you've created your module, change the name to something more descriptive by selecting the module in the Project window and looking at the Properties window underneath it. In this figure, the Properties window shows one entry, called (Name). You can click here to replace the current name with something more specific. Module names have all the same restrictions as macro names, which means they must start with a letter, and use only letters , numbers , and the underscore .

If you end up with a module you don't want, it's easy enough to remove it. Just right-click the module in the Project window and choose Remove.