Excel VBA Programming For Dummies. Dick Kusleika
3 covers 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.
HEY, I DIDN’T RECORD THAT!
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.
Modifying the 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 = 16 and 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.
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.
Saving Workbooks That Contain Macros
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.
Understanding Macro Security
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.
Figure 2-6 shows the Macro Settings section of the Trust Center dialog box. To display this dialog box, choose Developer ⇒ Code ⇒ Macro Security.
FIGURE 2-6: The Macro Settings section of the Trust Center dialog box.
By default, Excel uses the Disable All Macros with Notification option. With this setting in effect, if you open a workbook that contains macros (and the file is not digitally “signed” or stored in a trusted location), Excel displays a warning like the one shown in Figure 2-7. If you are certain that the workbook comes from a trusted source, click Enable Macros, and Excel enables the macros.
FIGURE 2-7: Excel’s warning that the file to be opened contains macros.
Excel remembers when you designate a workbook to be safe. So the next time you open the workbook, you won’t see the Security Warning.
FIGURE 2-8: Excel's warning that the workbook just opened contains macros. You see this warning when the VBE isn’t open.
Perhaps the best way to handle macro security is to designate one or more folders as trusted locations. All the workbooks in a trusted location are opened without a macro warning. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.
If you want to find out what the other macro security