Section 25.3. Creating Practical Macros | Excel 2010: The Missing Manual

25.3. Creating Practical Macros

If you're new to the idea of macros, it helps to look at a few straightforward examples to get a better idea of what a macro can do for you. In this section, you'll learn about some helpful macros that you can create for practice and profit.

25.3.1. Inserting a Header

A really simple macro to start out with is one that inserts some boilerplate information into a group of cells . For example, consider the header at the top of a company spreadsheet, which typically includes a company's name , the spreadsheet's author, the date the spreadsheet was created, and a title. If you're often adding this same information to many different worksheets, you can automate the work with a macro.

Here's what you need to do:

Fire up the macro recorder (select Tools Macro Record New Macro).

The Record Macro dialog box appears.
Name your macro.

Choose a memorable name (like InsertHeader) and click OK to get started with the actual macro recording.
Make sure you're in absolute reference mode (the Relative Reference button on the Stop Recording toolbar should not be selected).

You want absolute reference mode because you always want Excel to place the header in the same few cells at the top of the worksheet.
Move to cell A1 and insert a generic title (like "Sales Report").

You can of course pick whatever title you want to use.
Select cells A1 to C1, right-click the selection, and choose Format Cells.

In the next step, you're going to create a little more breathing room for your title by increasing its size .
Change the font to something large and dramatic.

In the Alignment tab, turn on the "Merge cells" checkbox to group these three cells into one larger cell that can accommodate the title. Click OK once you're done.
Move to cell A2 and enter the text "Created." Move to cell B2 and type the formula =TODAY( ) .

The TODAY( ) function inserts the current date. However, you want this cell to reflect the creation date, so that Excel doesn't update the date every time someone opens up the spreadsheet. To make sure the date can't change, you need to replace the formula with the calculated date value. Press F2 to put the cell in edit mode, and then F9 to replace the formula with the result. Press Enter to commit the change.
Optionally, add any extra text you'd like to include (like a company slogan or a copyright message).

Feel free to tweak column sizes as well.
Click Stop Recording to save your macro.

To try out your macro (Figure 25-4), replay it in a different worksheet in the same workbook.

Figure 25-4. The InsertHeader macro (which you can download from the "Missing CD" page at MissingManuals.com) inserts a generic header at the top of a worksheet and automatically grabs the current date, and inserts it as text.

25.3.2. Alternating Row Formatting

Excel provides a bunch of convenient features to help you repeatedly apply your formatting choices. For example, you can use the Format Painter to copy formatting from cell to cell or define specific formatting designs using the styles tool (both of these options are described in Chapter 4).

Another useful tool for complex formatting tasks is a custom macro. Macros are particularly useful if you want to apply a pattern of formatting to a large block of cells. For example, if you want to format every third row with a different background color , a macro can come in quite handy. In this case, the easiest approach is to create a macro that formats just a few rows; you can then replay this macro multiple times to format a whole table of data.

Here's how to build a macro for applying alternating row formatting:

Move to the cell where you want to start before you begin recording.

In this example, it makes sense to use relative reference mode. Therefore it doesn't matter which cell you start from; Excel doesn't record the cell address as part of the macro.
Select Tools Macro Record New Macro and choose a name you'll remember (like FormatRow).

You can also specify a shortcut key (like Ctrl+Shift+F). The FormatRow macro described here is much more practical with a shortcut key, because you'll want to use it multiple times in quick succession. When you're finished, click OK.
Make sure you're in relative reference mode.

Click the Relative Reference button on the Stop Recording toolbar so that it's highlighted. You want this turned on since you're going to repeatedly apply this macro across your worksheet.
Click the row number button at the left of the current row to select the entire row. Right-click the selection and choose Format Cells.

The Format Cells dialog box appears.
Select the Patterns tab, choose a new color, and click OK.
Press the down arrow key twice to move down two rows.

For example, if you're in row 1, move down to the third row.
Click Stop Recording to save your macro.

The neat thing about this macro is that you can replay it multiple times to format unlimited expanses of data. To try this out, start at the top of a table of data. Press the macro shortcut key (if you've created one) to format the first row and automatically move down two rows. Then press the macro shortcut key again to format the current row and move down again. (If you haven't used a shortcut, you'll have to use the more awkward approach of selecting Tools Macro Macros and selecting the macro from the list of available macros.) You can continue this process for as long as you want. The end result is that you'll have created a table where every second row is highlighted with a different background color.

25.3.3. A Combined Task

Really sophisticated macros don't just add data or format cells. Instead, they perform a whole series of operations. To get a better feeling for these types of macros, you can try out the following example, which figures out the average and median values for a list of numbers .

Before you start recording the macro, create a list of numbers in an Excel spreadsheet. These are the numbers the macro will analyze.

If you don't want to type in your own numbers, you can open an existing worksheet, like the student grade worksheet from Chapter 8.
Select the numbers.

Note that you select the numbers before you create the macro. That's because the macro you're creating is designed to analyze a selection of cells. Before you replay the macro, you'll need to select a range of cells you want to use.
Select Tools Macro Record New Macro.

Choose a suitable name (like AnalyzeSelectedCells) and then click OK.
Before you take any other steps, define a new name for the selected cells.

For example, type AnalyzedCells in the Name box of the Formula bar. (The name box is at the extreme left side of the Formula bar, and it usually displays the reference for the active cell.)
Press the right arrow key once or twice to move to a free column.
Enter the formula =AVERAGE(AnalyzedCells) to calculate the average of the named range you created in step 4.
In a cell underneath, enter the formula =MEDIAN(AnalyzedCells) .

This calculates the median value of the named range you created in step 4.
Add labels next to the cells with the two formulas, if you want.
Click Stop Recording to save your macro.

To test this macro, select the group of cells you want to analyze before you play the macro. The neat thing about this macro is that you can use it to analyze any number of cells, just as long as you select them all. The only limitation is that you can have just one range with the same name in a worksheet, so you can't use this macro in more than one place on the same worksheet. If you wrote the same macro by hand in VBA code, you could circumvent this limitation.

25.3.4. Security and Macros

Excel's macro language is surprisingly powerful. In fact, the VBA language packs enough power for expert gurus and hackers who want to design malicious worksheet viruses.

On the whole, Excel viruses aren't nearly as dangerous as full-fledged computer viruses, which can delete files and lobotomize your operating system. But macros can be dangerous, too, and they're adept at spreadingthey work by copying themselves from an infected workbook to other currently open workbooks.

To keep your machine clean, the best solution is to avoid using macros in Excel spreadsheets that you don't trust. Every time you open an Excel file that contains one or more macros, Excel shows the Security Warning dialog box pictured in Figure 25-5. You can choose to turn on the macros in the worksheet, or turn them off. If the workbook contains dangerous macros, they can run automatically even if you don't explicitly use the Tools Macro Macros command. That's because it's possible to create macros that run automatically in response to certain Excel events (like opening a document). To further complicate life, nefarious macros can sometimes hide or password-protect their code, making it difficult to find out that a problem even exists. So play it safe and disable macros if you're not absolutely sure they're legit.

Figure 25-5. The Security Warning dialog box gives you the chance to turn off macros when you open a workbook. If you're opening a workbook that could be infected or isn't from a person you trust, or if you don't need to use the macros in a workbook, choose Disable.

POWER USERS' CLINIC
Authenticode Macro Signing

If your company frequently creates and distributes workbooks with macros, you might want to use an advanced option called Authenticode signing. With Authenticode, you sign all your macro with a digital signature. When someone opens the workbook, their computer checks the signature, and verifies that it's from a known, trusted source. If it's not from a trusted source, the Security Warning dialog box appears (if Excel is using the standard Medium security level) or the macro is disabled (if Excel is using High or Very High security).

The problem with Authenticode macro signing is that the person who opens the document needs a way to verify the digital signature is trusted. This means you need to create a digital certificate for the person who writes the macro, and register this digital certificate with everyone who needs to use macros created by this person.

This process can be complicated, and for that reason it's employed only by large organizations that use macros heavily. Authenticode signing is far outside the scope of this book, but you can download a document that explains this feature and the technology it uses from http://office.microsoft.com/home . Just search for macro security whitepaper .

Excel's standard behavior is to show the Security Warning message every time you open a workbook that contains macros. If this behavior annoys you, however, you can change it. Select Tools Macro Security to open Security dialog box, which gives you four levels of security that you can use, including Very High and High (both of which prevent most macros from running, unless they have a recognized digital signature) and Low (which automatically allows all macros and is not recommended). The standard option is Medium, which always gives you a choice of whether or not to enable macros when you open a workbook.

Note: In Excel 2002, you have three choices only (High, Medium, and Low). The slightly more stringent Very High security setting isn't available.

25.3.5. Attaching a Macro to a Toolbar or Menu

Once you've created a useful macro, you may want to attach it to a toolbar or put it on a menu so that it's conveniently available when you need it. This makes most sense if you're creating a macro that you're frequently going to use (and it also helps if you've saved the macro in the personal macro workbook, since that ensures the macro will be available from any workbook, as explained earlier in this chapter).

Note: If you attach a macro to a toolbar button or menu that isn't in your personal macro workbook, and then you rename or move the workbook later on, the button or menu item won't work anymore.

Adding a toolbar button or menu item that activates a macro takes a little bit of time, but it's not too hard to do. The procedure consists of two main processes: First you add the button or menu item (or both) to Excel, and then you link the macro to these objects. Just follow these steps:

If the macro is not in the current workbook, start by opening the workbook that contains it.

Ideally, the macro is stored in your personal macro workbook. (If you want to copy a macro from another workbook into your personal workbook, jump ahead to the Section 26.1.3 on Section 26.1.3 in the next chapter.)
Choose Tools Customize.

The Customize dialog box appears (see Figure 25-6).
Choose the Commands tab.

The Commands tab provides one-stop shopping for customizing Excel menus and toolbars . Appendix B discusses this customization process in more detail.
Scroll down through the Categories list to Macros, and select that option.

There are two commands in the macros category. The Custom Menu Item command lets you attach macros to a menu command , and the Custom Button command lets you attach macros to a toolbar button . They appear in the Categories list on the right side of the tab.

If you want to create a toolbar button that activates your macro, drag the Custom Button command to one of the existing toolbars.

You can also create a new toolbar for your macros. (You might want to create a new toolbar if you want one specifically dedicated to holding macro-launching buttons .) To do this, select the Toolbars tab and click New. Excel asks you to name your new toolbar. Once you specify a name and click OK, a new, empty, floating toolbar appears. Now go back to the Categories list and select Macros, so that you can see the Custom Button command. Drag the Custom Button command onto your shiny new toolbar.

Figure 25-6. Excel provides two options for linking custom macros to Excel menus or toolbars. You can attach macros to a menu command by choosing Custom Menu Item, or to a toolbar button by choosing Custom Button (the one with the cheery happy face icon next to it).

Regardless of whether you've created a new toolbar or placed the Custom Button command on an existing toolbar, this step only creates the button; step 7 will show you how to link the macro to this button.
If you want to create a new menu entry for your macro, drag the Custom Menu Item command onto any menu where you'd like to place it.

You can place the Custom Menu Item in any existing menu, but it probably makes sense to keep your menu items separate from the standard Excel menu options. One common approach is to create a custom menu for macros, which you can place right next to the Help menu. To add a macro-holding menu, head to the bottom of the Categories list, select New Menu, and drag the New Menu command (shown in the Commands text box) alongside the other menus at the top of the Excel window. (You'll learn how to edit this menu's name in step 9.)
Now you can drag the Custom Menu Item onto the new menu. (To do this, you first need to go back to the Categories list and select Macros, so that you can see the Custom Menu Item.) As with the previous step, the only thing you're accomplishing here is creating a new menu that contains one menu choice (currently named "Custom Menu Item"); in the next step, you'll link this menu item to your macro.

Tip: If at any point you decide you don't like the button or menu item that you've created, simply drag it off the toolbar or menu. The mouse pointer changes to show a small X, and when you release the mouse button, the item disappears.

Once you've created a new toolbar button or menu command, you need to link it to the macro you want to use. Right-click the button's icon or menu command and choose Assign Macro.

The Assign Macro dialog box appears, which looks the same as the Macro dialog box you see when you choose to playback a macro. The only difference is that it's missing a couple of extra buttons for macro editing and debugging (see Figure 25-7).

Figure 25-7. The Assign Macro dialog box gives you a chance to choose the macro that you want to attach to the menu item. You'll see all the currently accessible macros (any macros in currently open workbooks and the personal macro workbook).

Choose a macro from the list, and click OK.

If you've added a toolbar button and a menu command, you need to perform step 8 for both these objects.

To customize the text and icon for your newly added button or menu item so that it provides a clear description of what it does, right-click the button or menu item.

A pop-up menu appears with several options (see Figure 25-8). Select Name to write in a name for your macro. In a menu item, the text you type here is what Excel will display on your menu item; on a toolbar button, the text you insert is what will appear when you hover over the button.

Figure 25-8. The custom button pop-up menu's options include choices for changing the icon and changing the display style, which determines whether Excel displays the Name text alongside the icon on the toolbar. At the very bottom of the menu are options for attaching a hyperlink or a macro to the command.

Tip: You can use the ampersand (&) symbol to specify a quick access key in a menu item. (The quick access key is the letter that appears underlined in most menu commands; you can press that letter when the menu is open to quickly activate that menu command.) For example, by entering the name "Finan&ce Macro", the menu will display "Finan c e Macro" and the quick access key will be set to the "c" in "Finance."

If you'd like to choose another icon for the command, select the Change Button Image in the same pop-up menu that you were working in above. In a menu, this icon will appear next to the text. In a toolbar button, the icon will appear only.

In the Customize dialog box, click Close to complete the customization process.

You can now click your custom menu item or button to run your macro. If the macro is located inside a workbook that isn't currently open, Excel opens the workbook and displays a message asking if you want to enable macros for the workbook.

Note: When you customize Excel's menus and toolbars, your customizations are stored on your computer, but they aren't saved in any workbook file. That means that people who use your workbook files on other computers won't see your new buttons. They need to rely on the Macros dialog box, or the macro shortcut key. You can get around this limitation with toolbars by explicitly copying a custom toolbar into a workbook file. See Section B.1.5 for details on how to do this.

25.3.6. Attaching a Macro to a Button Inside a Worksheet

Only a few extremely useful macros will ever be worth space on your toolbars or menus. But if you create a macro that you use frequently with a specific workbooksay, a macro that produces a special printout or performs a complex calculationyou might want it easily available all the time, but for that workbook only.

The solution is to use a button control . You can place this button anywhere you want on your worksheet and configure its text. When the person using the spreadsheet clicks the button, the linked macro runs automatically. This is particularly useful if a lot of different people use the worksheet; not everyone will remember a specific macro name or shortcut key, but nobody will have any trouble clicking a large inviting button.

To add a button to your worksheet and attach a macro to it, follow these steps:

If the macro isn't in the current workbook, start by opening the workbook that contains it.

Ideally, the macro is stored in the same workbook where you're placing the button or the personal macro workbook. Otherwise, you're probably complicating your life unnecessarily.
Choose View Toolbars Forms.
On the Forms toolbar, click the button icon.

If you don't spot the button icon right away, just move your cursor over all the toolbar buttons until the tooltip text Button appears.
Drag to "draw" the button onto your worksheet.

There's no restriction as to where you can place a button, or how large it can be. However, you don't want to obscure important data in the cells underneath. Once you finish drawing the button, the Assign Macro dialog box appears, with a list of available macros.
Select a macro and click OK.

Ideally, you should choose a macro that's stored in the current workbook or the personal workbook. Otherwise, the button won't work if you move or rename the required workbook.
Click the button text, and replace the standard text (Button 1) with something more descriptive (like Update Totals).
Click the worksheet.

Click anywhere on the worksheet to finish this procedure. At this point, the button is fully functional, and you can click it to run the linked macro (see Figure 25-9).
If at any point you want to change the button text or move it somewhere else, start by right-clicking the button to select it. You can then drag it, resize it, delete it (by pressing the Delete key), or click to change the button text, without inadvertently triggering the linked macro.

Figure 25-9. This worksheet includes a custom button that runs a linked macro. When you move the mouse pointer over the button, it changes into a hand, indicating that you can click on the button to unleash the macro.