How to Enable and Use the Macro Recorder in Excel 2007
Excel’s macro recorder is a useful tool that “records” user actions for “playback at a later time”. The main advantage of using a macro recorder in Excel is that it allows a user to easily perform complex operations over and over that may not be feasible without custom computer programming / scripting. It’s usage can be likened to that of a telephone recorder and they both involve 3 major steps. Rehearsal (Knowing what you want to be recorded), Recording (Carrying out your Excel operations) and Playback (putting your recorder actions to use).
If you are going to use the Macro Recorder for the first time, you probably don’t have the Developer tab enabled. Here are the steps to enable it:
How to enable the Developer tab in Excel 2007
- Right click anywhere on the Excel toolbar (where it say Home, Insert, etc..) and then Click on Customize Quick Access Toolbar.
- Click on Popular in the Listbox to the left and then make sure that the Show Developer tab in the Ribbon is checked.
- Click Ok.
Now that we got that out of the way, the Developer tab should now be displayed in your toolbar. Before you begin however, you should first think about how you are going to carry out the operation that you want stored in a macro. For our example, we want to create a macro that types Monday, Tuesday, Wednesday, Thursday, Friday across the top of the worksheet beginning at Cell A1. In this case, to carry out the operation is easy as all we have to do is just type the data across the worksheet. Of course, the more complex the macro is, the more thought you will have to put in to how you will carry out the operation, but the concept remains the same.
Next, think about when you want to start recording. In our example, since we want the Macro to begin at Cell A1, we would have to record typing into Cell A1 as by default, Excel macros are recorded with an absolute reference.
Next, think about when you want to stop recording. Don’t forget that you can add formatting to your cells such as bold and italics if you like. You can also set where you want the active cell to be after you run the macro, so when you are finished you can click on the cell that you want to be active before stopping the recorder.
Now we are ready to record. To record a Macro, click on the Developer tab then Click on the Record Macro button. Here, you can give it a name, a shortcut key if you like (we will use CTRL+g), as well as a description. All Excel macros are stored in workbooks. You are given a choice regarding where the recorded macro will be stored. The Store macro in: combo box lists three possibilities. If you choose New Workbook, the recorder will open a new empty workbook for the macro. Personal Macro Workbook refers to a special hidden workbook, which is discussed in a moment. Choose This Workbook to store the macro in the currently active workbook.
When you have filled the entries in the dialog box, click the OK button. You will see a new Stop Recording button appear on the left side of the status bar at the bottom of the screen. The Start Recording button in the Ribbon has been replaced by a new Stop Recording button.
In our example, we will begin type in our values at cell A1, so we’ll type Monday, Tuesday, etc.. When we’re done, we’ll also bold the cells by Select the cells then pressing CTRL+B or you can simply access it from the home tab on the Excel 2007 Ribbon. To finish it up, we will click on Cell A2 to make it our active cell and then stop the recorder. You can stop the recorder by clicking the Stop Recording button on the Ribbon or
by clicking the Stop Recording button on the status bar.
Please note that once the workbook contains a macro, you can only save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) type, which is in XLM format or as an Excel Binary Workbook (*.xlsb) type, which is in a binary format. None of these formats are compatible with older versions of Excel. You can also save it as an Excel 97-2003 Workbook (*.xls) type but will strip the workbook of some of its features.
You can only run macros that are in open workbooks, but they can be run from within any other open workbook. You can run the macro by pressing Ctrl+g, the shortcut that was assigned earlier in our example.
You can also run the macro by clicking the Macros button in the View tab of the Ribbon or by
clicking the Macros button in the Developer tab of the Ribbon. You can then run the macro by double-clicking it’s name, or by selecting the macro name and then clicking Run.
