Looking at Other Macro Recording Concepts
At this point, you should feel comfortable recording your own Excel Macros. Now here are some of
the other important concepts you’ll need to keep in mind when working with macros.
Macro-enabled file extensions
Beginning with Excel 2007, Excel has a separate file extension for workbooks that contain macros.
You see, Excel 2010 workbooks have the standard file extension .xlsx. Files with the .xlsx extension
cannot contain macros. If your workbook contains macros and you then save that workbook as an .
xlsx file, your macros are removed automatically. Excel warns you that macro content will be disabled
when saving a workbook with macros as an .xlsx file.
If you want to retain the macros, you must save your file as an Excel Macro-Enabled Workbook. This
gives your file an .xlsm extension. The idea is that all workbooks with an .xlsx file extension are auto-
matically known to be safe, whereas you can recognize .xlsm files as a potential threat.
Macro security in Excel 2010
With the release of Office 2010, Microsoft introduced significant changes to its Office security model.
One of the most significant changes is the concept of trusted documents. Without getting into the
technical minutia, a trusted document is essentially a workbook you have deemed safe by enabling
macros.
If you open a workbook that contains macros in Excel 2010, you see a yellow bar message under the
Ribbon stating that macros (active content) have in effect, been disabled.
If you click Enable, it automatically becomes a trusted document. This means you no longer are
prompted to enable the content as long as you open that file on your computer. The basic idea is
that if you told Excel that you “trust” a particular workbook by enabling macros, it is highly likely that
you will enable macros each time you open it. Thus, Excel remembers that you’ve enabled macros
before and inhibits any further messages about macros for that workbook.
This is great news for you and your clients. After enabling your macros just one time, they won’t
be annoyed at the constant messages about macros, and you won’t have to worry that your
macro-enabled dashboard will fall flat because macros have been disabled.
Trusted locations
If the thought of any macro message coming up (even one time) unnerves you, you can set up a
trusted location for your files. A trusted location is a directory that is deemed a safe zone where only
trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled
workbook with no security restrictions as long as the workbook is in that location.
To set up a trusted location, follow these steps:
1. Select the Macro Security button on the Developer tab.
This activates the Trust Center dialog box.
2. Click the Trusted Locations button. This opens the Trusted Locations menu (see Figure 1-6),
which shows you all the directories that are considered trusted.
3. Click the Add New Location button.
4. Click Browse to find and specify the directory that will be considered a trusted location.
After you specify a trusted location, any Excel file that is opened from this location will have macros
automatically enabled.
Figure 1-6: The Trusted Locations menu allows you to add directories that are considered trusted.
Storing macros in your Personal Macro Workbook
Most user-created macros are designed for use in a specific workbook, but you may want to use
some macros in all your work. You can store these general-purpose macros in the Personal Macro
Workbook so that they’re always available to you. The Personal Macro Workbook is loaded whenever
you start Excel. This file, named personal.xlsb, doesn’t exist until you record a macro using Personal
Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-
down list.
If you store macros in the Personal Macro Workbook, you don’t have to remember to open the
Personal Macro Workbook when you load a workbook that uses macros. When you want to exit,
Excel asks whether you want to save changes to the Personal Macro Workbook.
Assigning a macro to a button and other form controls
When you create macros, you may want to have a clear and easy way to run each macro. A basic
button can provide a simple but effective user interface.
As luck would have it, Excel offers a set of form controls designed specifically for creating user inter-
faces directly on spreadsheets. There are several different types of form controls, from buttons (the
most commonly used control) to scrollbars.
The idea behind using a form control is simple. You place a form control on a spreadsheet and then
assign a macro to it — that is, a macro you’ve already recorded. When a macro is assigned to the con-
trol, that macro is executed, or played, when the control is clicked.
Take a moment to create a button for the AddTotalRelative macro you created earlier. Here’s how:
1. Click the Insert button under the Developer tab. (See Figure 1-7.)
2. Select the Button Form Control from the drop-down list that appears.
3. Click the location where you want to place your button. When you drop the button control
onto your spreadsheet, the Assign Macro dialog box, as shown in Figure 1-8, activates and
asks you to assign a macro to this button.
4. Select the macro you want to assign to the button and then click OK.
Figure 1-7: You can find the form controls in the Developer tab.
Figure 1-8: Assign a macro to the newly-added button.
Form controls versus ActiveX controls
Notice the form controls and ActiveX controls in Figure 1-7. Although they look similar, they’re quite
different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are
typically used on Excel user forms. As a general rule, you should always use form controls when work-
ing on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring
form controls is far easier than configuring their ActiveX counterparts.
At this point, you have a button that runs your macro when you click it! Keep in mind that all the con-
trols in the Form Controls group (shown in Figure 1-7) work in the same way as the command but-
ton, in that you assign a macro to run when the control is selected.
Placing a macro on the Quick Access toolbar
You can also assign a macro to a button in Excel’s Quick Access toolbar:
1. Right-click your Quick Access toolbar and select Customize Quick Access Toolbar.
2. Click the Quick Access Toolbar button on the left of the Excel Options dialog box.
3. Select Macros from the drop-down list on the left.
4. Select the macro you want to add and click the Add button.
5. Change the icon by clicking the Modify button.
