Taking Advantage of Macros

Problem

You find yourself repeating the same actions over and over and would like to automate that process.

Solution

Use Excel's macro-recording feature to record your actions, which can then be executed again using a keyboard shortcut.

Discussion

Let's say you find yourself repeatedly applying the same format settings to cells; for example, you select a cell, set the font style to bold, set justification to center, and apply a pattern and a border. You could define a custom style reflecting these format settings and use the style as discussed in Recipe 1.12, or you could record a macro to automate the process of setting these formats.

Although I'm using formats as an example, you should be aware that the macro-recording feature lets you record any sequence of actions taken in Excel, thus allowing you to automate almost anything. In Chapter 2, I discuss macros and other programming tasks in much greater detail in the context of using Visual Basic for Applications. That said, you can record simple macros to automate common tasks as discussed here, without using Visual Basic.

Take these steps to record a macro:

1. Select Tools Macro images/U2192.jpg border=0> Record New Macro... from the main menu bar to open the Record Macro Dialog box.
2. Execute the actions you want recorded.
3. When you've finished your actions, select Tools Macro images/U2192.jpg border=0> Stop Recording from the main menu bar.
4.  

   You may also press the Stop Recording button to stop recording a macro (instead of using the Stop Recording menu item as mentioned earlier).

   To manage macros, you can access the Macro dialog box via the Tools images/U2192.jpg border=0> Macro images/U2192.jpg border=0> Macros... menu or the Alt-F8 shortcut. The Macro dialog box allows you to run selected macros, delete macros, or redefine their shortcut keys.

   See Also

   See Excel's "Create a Macro" help topic or open the Excel Help task pane and search for the phrase "recording macros" to view a list of relevant topics. Or open the Excel Help task pane, click the "Table of Contents" link, and then click on the "Automating Tasks and Programmability" topic to reveal a list of help topics related to macros.