Dick Kusleika - Excel VBA Programming For Dummies

For your macro to run, the workbook that contains it must be open. If it’s closed, the macro won’t show up in the dialog box or in the VBE. The workbook doesn’t have to be active, however. You can activate a workbook and run code that’s contained in a different workbook. You can also store your macro in the Personal Macro Workbook (see Chapter 6) or in an add-in (see Chapter 21). Those are both types of files that are open in the background.

The NameAndTime macro you recorded in the previous section does not warn you if there is already data in the cells you are about to write data to. It’s not what you would call production-quality code.

Whatever way you choose to run your macro, run it and make sure it does what you expect it to. If it doesn’t, read on to see how to view and edit it.

After you record and run a macro, you may be curious to see what the macro looks like. You might even wonder where it’s stored.

Excel stores macros in the workbook you indicate when creating the macro. So for the example used in this chapter, the macro is stored in This Workbook. To view macros, however, you need to activate the Visual Basic Editor (VBE, for short).

Follow these steps to see the macro used as an example throughout this chapter:

1 Choose Developer ⇒ Code ⇒ Visual Basic (or press Alt+F11).The Visual Basic Editor program window appears, as shown in Figure 2-4. This window is highly customizable, so your VBE window may look a bit different. The VBE window contains several other windows, which can be intimidating. Don’t fret; you’ll get used to it.

2 In the VBE window, locate the Project Explorer.The Project Explorer 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). The VBE uses quite a few different windows, any of which can be 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 Explorer is not visible, you can choose View ⇒ Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner. Chapter 3covers the components of the VBE.

3 If necessary, expand the project that corresponds to the workbook in which you recorded the macro by clicking the plus sign next to it.If you haven’t saved the workbook, the project is probably called VBAProject (Book1). FIGURE 2-4:The VBE displays the VBA code in Module1 of 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 pane (refer to Figure 2-4). Your screen may not look exactly the same as Figure 2-4. The code that’s recorded depends on the specific actions you made while recording the macro.

At this point, the macro probably might look a bit mysterious. Don’t worry. Travel a few chapters down the road, and all will be crystal clear.

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

The first 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 might 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.

Reviewing the actions you recorded with the macro recorder has the same effect as listening to a voice memo you recorded on your phone. When you play back the voice memo and listen to your own voice, you probably say, “I don’t sound like that.” And when you look at your recorded macro, you might see some actions that you didn’t think you recorded.

For example, if you change only one aspect of the font, such as the font size, the recorded code will show all sorts of font-changing statements (Strikethrough, Superscript, Shadow, and so on). Don’t worry; it happens all the time. Excel often records lots of seemingly useless code. In later chapters, you find out how to remove the extra stuff from a recorded macro.

As you might expect, you not only can view your macro in the VBE, you can change it. You can edit a macro directly in the Code pane like the one you opened in the previous section by double-clicking Module1.

You modify a macro by typing new statements or modifying existing statements between the Sub and End Sub statements. For example, in the NameAndTime macro you recorded earlier in this chapter, you could change it to make the font size 18 instead of 16. Find the line .Size = 16and change it to:

.Size = 18

When you use the macro recorder, you don’t have to know a lot of VBA language to be able to find the line you want to change and change it. However, if it’s not as straightforward as the font size example and you’re not sure what to change, you can record a new macro to see what the recorder will generate when you do what you want. Then compare the new macro to the old one, manually edit the old one to look like the new one, and delete what you just recorded.

Working in a VBA code module is much like working in a word-processing document (except there’s no word wrap, and you can’t format the text). On second thought, it’s more like working in Windows Notepad. You can press Enter to start a new line, and the familiar editing keys work as expected.

After you make your changes, jump back to Excel and try the revised macro to see how it works. Just as you can press Alt+F11 in Excel to display the VBE, you can press Alt+F11 in the VBE to switch back to Excel.

If you store one or more macros in a workbook, the file must be saved as a macro-enabled file type. In other words, the file must be saved with an XLSM extension rather than the normal XLSX extension.

When you save a workbook that contains a macro, the Excel might try to save it as Excel Workbook (*.xlsx), a format that cannot contain macros. Unless you change the file format to Excel Macro-Enabled Workbook (*.xlsm), Excel displays the warning shown in Figure 2-5. You need to click No and change the file type to Excel Macro-Enabled Workbook (*.xlsm).

FIGURE 2-5:If your workbook contains macros, and you attempt to save it in a macro-free file format, Excel warns you.

Macro security is a key feature in Excel. The reason is that VBA is a powerful language — so powerful that it’s possible to create a macro that can do serious damage to your computer. A macro can delete files, send information to other computers, and even destroy Windows so that you can’t start your system.