Introducing the Visual Basic Editor
Learning the Basics of Visual Basic
Adding Code to or Editing Recorded Macros
Using the Personal Macro Workbook
A MACRO is a set of instructions that tells Microsoft Excel 2010 (or another application) to perform one or more actions for you. Macros in Excel 2010 are like computer programs, but they run completely within Excel. You can use them to automate tedious or frequently repeated tasks.
Macros can carry out sequences of actions much more quickly than you can yourself. For example, you can create a macro that enters a series of dates across one row of a worksheet, centers the date in each cell, and then applies a border format to the row. Or you can create a macro that defines a combination of print settings—margins, orientation, scaling, headers, and footers—that you want to use in many documents. Macros are great for any task you do repeatedly.
You can create a macro in two ways: You can record it, or you can build it by typing instructions in a module. Either way, your instructions are encoded in the programming language Microsoft Visual Basic for Applications (VBA). (You can also combine the two approaches.)
Even if you’re not a programmer and have no intention of becoming one, macros can be a useful addition to your Excel toolkit. Thanks to the macro recorder, you don’t have to understand all the ins and outs of VBA to create effective and timesaving macros. And if you’re curious about VBA and want to learn to do more with macros than is possible with the recorder alone, you will find the recorder to be an excellent learning tool. You can get a great start on acquiring VBA expertise by examining the code that the recorder generates.
Macros execute code, and code can serve evil ends as well as good ones. If your system is permitted to run all macros, regardless of their source, you might inadvertently run a macro that damages your system in some way. Because VBA macros included in Microsoft Office documents (typically attached to e-mail messages) have occasionally served as virus vectors in recent years, Microsoft no longer permits VBA code to run by default. You have to take steps to enable macro execution.
Like other security configuration settings, the settings that permit or deny macro execution are in the Trust Center. To get there, click File, Options. In the Excel Options dialog box, select the Trust Center category, and then click Trust Center Settings.
Note
For more information about the Trust Center, see The Trust Center on page 111.
How you configure macro security depends on how you expect to use macros, the degree to which you are concerned about potentially malicious macro code, and perhaps the security policies of your organization. If your organization’s IT staff has disabled access to the Trust Center and also macro execution, this discussion is moot (unless you can convince someone to relax the rules). Assuming that’s not the case, your first stop in the Trust Center should be the Macro Settings category, where you will see something like the following:
The four options in the Macro Settings area of this dialog box determine how Excel handles macro code in files that are not stored in a trusted location. The default setting disallows such macros but causes a notification bar to appear whenever you open a file that contains a proscribed macro. The notification bar looks like this:
If you know for sure that whatever macros the file might contain are benign, you can overrule the security cop by clicking Enable Content. If you use macros regularly, however, you probably don’t want to deal with the notification bar every time you open a workbook containing macros. You might instead be tempted to change the Macro Settings option in the Trust Center to Enable All Macros (Not Recommended; Potentially Dangerous Code Can Run). As the parenthetical comment suggests, however, this is not an ideal approach to macro security.
A better approach is to designate the folders you use regularly—as well as those from which you are likely to open macro-laden files created by trustworthy others—as trusted locations. Excel permits all macro content to run in files stored in such locations. To configure a trusted location, return to the Trust Center, and select the Trusted Locations category. You see a list comparable to the following:
The top area in this dialog box lists trusted locations you create, as well as those provided as defaults by the Microsoft Office Setup program. Below that list, under the heading Policy Locations, you might see additional trusted locations established by your IT staff. To set up a new trusted location, click Add New Location. In the dialog box that appears, you can specify the path of the new location, indicate whether you also want to trust subfolders in that location, and add a description of the location. Excel provides a date and time stamp for you.
By using the trusted-locations mechanism, you can create no-questions-asked zones for the macros you create and use, without disabling the defenses Excel uses against external threats. If you leave in place the default Macro Settings option—Disable All Macros With Notification—Excel will inform you if you happen to open a file from a nontrusted location that contains a macro. When that occurs, you can make a judgment call about whether to allow the banned content.
Having configured the security options to your satisfaction, you still have one more decision to make before you can begin creating your own macros. The Excel default workbook format (.xlsx) does not support macros. To save a workbook containing one or more macros, you need to use one of the following formats:
If you plan to use macros regularly, or even occasionally, you should consider changing the default to one of these macro-supporting formats. To do this, return to the Excel Options dialog box, select the Save category, and select a format in the Save Files In This Format drop-down list.
Note
For more information about file format options, see “Understanding the ‘XL’ Formats” on page 54.
If you prefer not to change the default file format to Excel Macro-Enabled Workbook, you can always save in that format on a case-by-case basis when you create a file that uses a macro. Excel warns you when you try to save a file with macros in a non-macro-enabled format.