Examining the Macro

So far, you've recorded a macro and you've tested it. If you're a curious type, you're probably wondering what this macro looks like. And you might even wonder where it's stored.

Remember when you started recording the macro? You indicated that Excel should store the macro in This Workbook. The macro is stored in the workbook, but you need to activate the Visual Basic Editor (VBE, for short) to see it.

Follow these steps to see the macro:

1. Choose DeveloperOCodeO Visual Basic (or press Alt+F11).

The Visual Basic Editor program window appears, as shown in Figure 2-3. This window is highly customizable, so your VBE window may look different. The VBE program window contains several other windows and is probably very intimidating. Don't fret; you'll get used to it.

2. In the VBE window, locate the window called Project.

The Project window (also known as the Project Explorer window) contains a list of all workbooks and add-ins that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information).

Figure 2-3:

The Visual Basic Editor is where you view and edit VBA code.

Figure 2-3:

The Visual Basic Editor is where you view and edit VBA code.

The VBE uses quite a few different windows, any of which can be either open or closed. If a window isn't immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project window is not visible, you can choose ViewO Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner. I explain more about the components of the Visual Basic Editor in Chapter 3.

3. Select the project that corresponds to the workbook in which you recorded the macro.

If you haven't saved the workbook, the project is probably called VBAProject (Book1).

4. Click the plus sign (+) to the left of the folder named Modules.

The tree expands to show Module1, which is the only module in the project.

5. Double-click Module1.

The VBA code in that module is displayed in a Code window. Figure 2-4 shows how it looks on my screen. Your screen may not look exactly the same.

The code in Module1 should look like this:

Sub NameAndTime()

' NameAndTime Macro

' Keyboard Shortcut: Ctrl+Shift+N

ActiveCell.FormulaR1C1 = "John Walkenbach" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=NOW()n Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveCell.Offset(-1, 0).Range("A1:A2").Select ActiveCell.Activate Selection.Font.Bold = True With Selection.Font

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .ThemeColor = 2 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With End Sub

At this point, the macro probably looks like Greek to you. Don't worry. Travel a few chapters down the road and all will be as clear as the view from Olympus.

The NameAndTime macro (also known as a Sub procedure) consists of several statements. Excel executes the statements one by one, from top to bottom. A statement preceded by an apostrophe (') is a comment. Comments are included only for your information and are essentially ignored. In other words, Excel skips right over comments.

The first actual VBA statement (which begins with the word Sub) identifies the macro as a Sub procedure and gives its name — you provided this name before you started recording the macro. If you read through the code, you may be able to make sense of some of it. You see your name, the formula you entered, and lots of additional code that changes the font. The Sub procedure ends with the End Sub statement.

Hey, I didn't record that!

I've noted that the macro recorder is like recording sound on a tape recorder. When you play back an audiotape and listen to your own voice, you invariably say, "I don't sound like that." And when you look at your recorded macro, you may see some actions that you didn't think you recorded.

When you recorded the NameAndTime example, you changed only the font size, yet the recorded code shows all sorts of font-changing statements. Don't worry, it happens all the time. Excel often records lots of seemingly useless code. In later chapters, you find how to remove the extra stuff from a recorded macro.

Figure 2-4:

The VBE displays the VBA code in Modulel of Bookl.

Figure 2-4:

The VBE displays the VBA code in Modulel of Bookl.

0 0

Post a comment