.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 settings mean, press F1 while the Macro Settings section of the Trust Center dialog box is in view and review the Help screen that appears.
Part 2
Employing VBA with Excel
IN THIS PART …
See how to access the important parts of the Visual Basic Editor.
Discover VBA code modules (where you store your VBA code).
Obtain an overview of the Excel object model.
Get a crash course in using the Excel macro recorder.
Chapter 3
Working in the Visual Basic Editor
IN THIS CHAPTER
The Visual Basic Editor (VBE) is the main tool for programming in VBA. It’s where you view and edit recorded code and write code from scratch. In this chapter, you find out how to work with the VBE, and you get down to the nitty-gritty of writing some VBA code.
Getting to Know the Visual Basic Editor
The Visual Basic Editor (often referred to as the VBE) is a separate application where you write and edit your VBA macros. Beginning with Excel 2013, when you open multiple workbooks, each opens in a separate window. However, the VBE only ever has one window, and from this window
