28.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.
Before you can use the Visual Basic editor, you need to make sure the Developer tab is visible in the ribbon. (If it isn't, choose Office button Excel Options, and then choose the Popular section. Under the "Top options for working with Excel heading, switch on the "Show Developer tab in the ribbon" setting.)
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 Developer Code Visual Basic. When you do, Excel launches the standalone window shown in Figure 28-1.
28.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 may 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 Lookup wizard (Section 12.2.5). 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 may find in the project window include:
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. 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 ThisWork-bookwhere you can respond to workbook actions (like saving, opening, or printing the workbook).
Note: Creating code that reacts to events is an advanced Excel technique. For example, you could use an Excel event to run some code every time your workbook is opened. This book doesn't cover events.
Modules is the second folder in a workbook project. It appears only if you've created a macro for this particular workbook. Figure 28-2 details the action inside one particular Project window.
Note: As you open new workbooks, they automatically appear in the Visual Basic editor's Project window. Similarly, as you close workbooks, they disappear from the Project window.
28.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.
Every module contains 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 (like explaining 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.
Tip: Master programmers always leave comments in their code (or at least they feel terribly guilty when they don't). Comments are the best way to clarify what you want your code to do, so you can remember your intentions when you review it a few months later.
In Excel, each macro is a separate subroutine. (In some cases, you may want to break a complex macro down into more than one subroutine, but the macro recorder doesn'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 in Section 27.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 28-3. You can scroll through this window to see all the macro procedures it contains.
28.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:
28.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, scroll through the code window, and click to place the cursor 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 may assume that Excel performs the macro on the workbook that contains the macro code, but this scenario isn't necessarily true. Instead, Excel runs the macro on the active workbook , which is the one 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 runs the macro on the second workbook.
Macro debugging is another neat trick you can perform from the Visual Basic editor. 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:
Tip: Excel gives you another way to jump right to a macro that interests you, or that you need to debug. From the Excel window, choose Developer Code Macros to show the list of available macros. 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 could 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. You could, for instance, create a MyMacroCollection.xlsm workbook that has three macro modules: QuickCalculations, Formatting, and Printing.
You can easily add new modules. All you need to do is right-click the name of your project in the Project window, and then 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 Figure 28-4, 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 then choose Remove. (At this point, the Visual Basic editor asks if you want to export your macro code to a separate file so that you can import it back into some other workbook. If you just want to get rid of your module for good, choose No.)