Section 27.4. Creating Practical Macros

27.4. Creating Practical Macros

If you're still wondering what macros can do for you, it helps to look at a few straightforward examples. In this section, you'll learn about some helpful macros that you can create for practice and profit.

27.4.1. Inserting a Header

A really simple macro to start out with is one that inserts some boilerplate information into a group of cells . 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:

1. Fire up the macro recorder (select View Macros Record Macro) .

   The Record Macro dialog box appears.

2. Name your macro .

   Choose a memorable name (like InsertHeader) and click OK to get started with the actual macro recording.

3. Make sure you're in absolute reference mode (the View Macros Use Relative References button 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.

4. Move to cell A1 and insert a generic title (like "Sales Report") .

   You can of course pick whatever title you want to use.

5. 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 .

6. 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.

7. 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.

8. 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.

9. Choose View Macros Stop Recording to save your macro .

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

27.4.2. Alternating Row Formatting

Excel provides a bunch of convenient features to help you repeatedly apply your formatting choices. You can use the Format Painter to copy formatting from cell to cell or define specific formatting using the styles tool. (Both these options are described in Chapter 6.) You can also use tables (Chapter 14).

A custom macro is another useful tool for complex formatting tasks . Macros are particularly useful if you want to apply a pattern of formatting to a large block of cells. 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.

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

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

1. 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.

2. Choose View Macros Record Macro and choose a name youll 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.

3. Make sure you're in relative reference mode .

   Click the View Macros Use Relative References button so that its highlighted. You want this button on since you're going to repeatedly apply this macro across your worksheet.

4. 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.

5. Select the Patterns tab, choose a new color, and click OK .

6. Press the down arrow key twice to move down two rows .

   If you're in row 1, move down to the third row.

7. Choose View Macros 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 trick 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 View Macros View 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 youll have created a table where every second row is highlighted with a different background color.

27.4.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 .

1. Before you start recording the macro, create a list of numbers in an Excel spreadsheet. These numbers are the ones 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 9.

2. 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.

3. Choose View Macros Record Macro .

   Choose a suitable name (like AnalyzeSelectedCells) and then click OK.

4. Before you take any other steps, define a new name for the selected cells .

   In the formula bar's Name box, type AnalyzedCells. (The name box is at the extreme left side of the formula bar, and it usually displays the reference for the active cell.)

5. Press the right arrow key once or twice to move to a free column .

6. Enter the formula =AVERAGE(AnalyzedCells) to calculate the average of the named range you created in step 4 .

7. In a cell underneath, enter the formula =MEDIAN(AnalyzedCells) .

   This formula calculates the median value of the named range you created in step 4.

8. Add labels next to the cells with the two formulas, if you want .

9. Chose View Macros 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.

27.4.4. Placing a Macro on the Quick Access Toolbar

Once you've created a useful macro, you may want to attach it to the Quick Access toolbar at the top of the Excel window so that it's conveniently available when you need it. You'll especially want easy access 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 that you can use that macro from any workbook, as explained in Section 27.2.2).

---

Note: If you attach a macro to the Quick Access toolbar and the macro isn't in your personal macro workbook, you could run into trouble. If you rename or move the workbook that contains the macro later on, the button won't work anymore.

---

Adding a Quick Access toolbar button that activates macros isn't difficult. Just follow these steps:

1. If the macro isn't 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 Section 28.1.3 in the next chapter.)

2. Right-click the Quick Access toolbar, and then choose Customize Quick Access Toolbar .

   The Excel Options dialog box appears, with the Customize section chosen (see Figure 27-10).

3. In the "Choose commands from" drop-down list, choose Macros .

   Underneath, a list appears with all the macros available in the personal macro workbook and any other currently open workbooks.

4. Choose the macro you want in the list, and then click Add to place it in the Quick Access toolbar .

   The item appears in the box on the right, which shows the current list of Quick Access commands.

5. Choose the newly added macro (at the bottom of the list), and then click Modify .

   The Modify Button dialog box appears.

   

   Figure 27-10. Choose the category of commands you want to see by picking from the "Choose commands from" pull-down list. On the right side of the dialog box, the Quick Access toolbar's current contents are shown. You can move items on or off the toolbar using Add and Remove, and you can change the order of items using the arrow buttons .

   
   

6. Choose a new icon and display name .

   Initially, all macros use a hideously bizarre icon and a tongue-twisting display name that includes the name of the workbook where they're stored. You can do better.

   The icon's the miniature picture that appears in the Quick Access toolbar. Pick one of the ready-made icons, all of which look better than the stock macro icon.

   The display name is the pop-up text that appears when you hover over the icon in the Quick Access toolbar. Try using a more understandable name. Instead of SuperMacroWorkbook.xlsm!MySuperMacro, consider "My Super Macro" or "MySuperMacro from SuperMacroWorkbook" or "Formats Alternating Rows".

7. If you don't like your macro button's positioning, select it, and then use the up and down arrow buttons .

   When you add a new item to the Quick Access toolbar, it heads straight to the end of the list (which means it appears at the toolbar's right edge). If you want, you can move it to a more prominent position.

8. Click OK .

   Your macro appears in the Quick Access toolbar. If you tire of it, right-click the button, and then choose "Remove from Quick Access Toolbar".

---

Tip: When you customize Excel's Quick Access toolbar, Excel stores your customizations on your computer, but not in any workbook file. People who use your workbook files on other computers don'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 by attaching a macro to a worksheet button instead, as described in the next section.

---

27.4.5. Attaching a Macro to a Button Inside a Worksheet

Only a few extremely useful macros will ever be worth space on the Quick Access toolbar. But if you create a macro that you use frequently with a specific work-booksay, a macro that produces a special printout or performs a complex calculationyou may want it easily available all the time, but for that workbook only.

The solution? 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 property is particularly useful if a lot of different people use the worksheet; not everyone can remember a specific macro name or shortcut key, but nobody has any trouble clicking a large inviting button.

To add a button to a worksheet, you need the Developer tab (Figure 27-11). The Developer tab doesn't appear unless you ask Excel to show it. To do so, choose Office button Excel Options, and then choose the Popular section. Under the "Top options for working with Excel heading, switch on the "Show Developer tab in the ribbon" setting. The Developer tab has buttons for recording and playing macros (which duplicate the options from the View Macros section of the ribbon), along with more advanced programming commands, most of which youll never use unless you become a hard- core code jockey.

Figure 27-11. If you don't spot the button icon right away, just move your cursor over all the icons until the tooltip text Button appears. Then click it.

Although you can record and play macros with Status bar buttons, it's a good idea to show the Developer tab before you continue any further. The Developer tab includes a few more options, like the ability to control whether the macro records relative or absolute references (Section 27.2.3), and a way to manage Excel's macro security settings (Section 27.3).

Once you've shown the Developer tab, you're ready to add a button to your worksheet and attach a macro to it. Just follow these steps:

1. 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.

2. Choose Developer Controls Insert Button .

   When you choose Developer Controls Insert, a list of different controls appears (Figure 27-11).

3. .

   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.

4. 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.

5. Right-click the button, and then choose Edit Text. Replace the standard text (Button 1) with something more descriptive (like Update Totals) .

6. 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 27-12).

   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 27-12. 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 the button to unleash the macro.