Excel Recording the macro
To see how easy it is to create a macro by recording your actions in Excel, follow along with these steps for creating a macro that enters the company name in a worksheet in 12-point, bold type and centers the company name across rows A through E with the Merge and Center feature:
- Open the Excel workbook that contains the worksheet data or chart you want your macro to work with.
If you're building a macro that adds new data to a worksheet (as in this example), open a worksheet with plenty of blank cells in which to add the data. If you're building a macro that needs to be in a particular cell when its steps are played back, put the cell pointer in that cell.
- Choose Tools → Macro → Record New Macro to open the Record Macro box.
- Replace the Macro1 temporary macro name by entering your name for the macro in the Macro Name text box.
Remember that when naming a macro, you can't use spaces in the macro name, and it must begin with a letter and not a number or punctuation symbol. For this example macro, replace Macro1 in the Macro Name text box with the name Company_Name.
- If you want to be able to play back the macro by pressing a keystroke shortcut, select the Shortcut Key text box and then enter the letter of the
alphabet that you want to assign to the macro.
In the Shortcut Key text box, you can enter a lowercase or uppercase letter between A and Z that acts like a shortcut key for running the macro when you press Ctrl followed by that letter key.
Just remember that Excel has already assigned a number of Ctrl+letter keystroke shortcuts for doing common tasks, such as Ctrl+C for copying an item to the Clipboard and Ctrl+V for pasting an item from the Clipboard into the worksheet. (Look up Keyboard Shortcuts in the Assistant task pane opened by pressing F1 for a complete list.) If you assign the same keystrokes to the macro that you're building, your macro's shortcut keys override and, therefore, disable Excel's ready-made shortcut keystrokes.
For this example macro, enter C (uppercase) to assign Ctrl+Shift+C as the shortcut keystroke (so as not to disable the ready-made Ctrl+C shortcut).
- On the Store Macro In drop-down list, select one of the following options for storing the new macro:
- Personal Macro Workbook: Select this option to be able to run the macro anytime you like.
- This Workbook (the default): Select this option when you need to run the macro only when the current workbook is open.
- New Workbook: Select this option if you want to open a new workbook in which to record and save the new macro.
- Select the Description list box and then insert a brief description of the macro's purpose in front of the information indicating the date and
who recorded the macro.
In the Description list box, you should document the purpose and functioning of your macro. Although this step is optional, it's a good idea to get in the habit of recording this information every time you build a new macro so that you and your coworkers can always know what to expect from the macro when running it.
- Click OK to close the Record Macro dialog box and automatically open the Stop Recording toolbar.
The floating Stop Recording toolbar appears as soon as you turn the macro recorder on (although all you can see is St and the first part of the o in the title bar because this toolbar is so short). Also, the message Recording now appears on the status bar to remind you that the results of all the actions you take (including selecting cells, entering data, and choosing commands) will now be recorded as part of your macro.
The Stop Recording toolbar contains a Stop Recording button that you can click to turn off the macro recorder and a Relative Reference button that you click when you want the macro recorder to record the macro relative to the position of the current cell.
- Click the Relative Reference button if you want to be able to play back the macro anywhere in the worksheet.
For the example macro that enters the company name and formats it in the worksheet, you definitely need to click the Relative Reference button before you start recording commands. Otherwise, you can use the macro only to enter the company name starting in cell A1 of a worksheet.
- Select the cells, enter the data, and choose the Excel commands required to perform the tasks that you want recorded just as you normally
would in creating or editing the current worksheet. You can use the keyboard or the mouse or a combination of the two.
For the example macro, all you do is type the company name and click the Enter button on the Formula bar to complete the entry in the current cell. Next, click the Bold button and then select 12 on the Font Size drop-down list on the Formatting toolbar. Finally, drag through cells A1:E1 to select this range and then click the Merge and Center button, again on the Formatting toolbar.
When you finish taking all the actions in Excel that you want recorded, you're ready to shut off the macro recorder.
- Click the Stop Recording button on the floating Stop Recording toolbar (or you can choose Tools → Macro → Stop Recording).
When you shut off the macro recorder, the Recording message on the status bar immediately disappears, letting you know that no further actions will be recorded.