MACRO RECORDER
Rather than typing macros character by character, you can have Excel create a macro by recording
the menu commands, keystrokes, and other actions needed to accomplish a task.
After you’ve recorded a series of actions, you can run the macro to perform the task again.
This playback capability is most useful with macros that automate long or
repetitive processes.
The overall process for recording a macro consists of three steps. First, you start the macro
recorder and supply a name for the macro. Next, you perform the actions you want to record,
such as choosing menu commands, selecting cells, and entering data. Finally, you stop the
macro recorder.
Let’s investigate this process by creating a simple macro that inserts a company name and
address in a worksheet. Begin by saving and closing all open workbooks, and then open a new
workbook. Then follow these steps:
- Choose Tools, Macro, Record New Macro. Excel displays the Record Macro dialog box.
- Assign a name to the macro. You can accept Excel’s suggestion (Macro1) or enter your
own name. Let’s use MyPolyAddress. Note that this name cannot have any spaces.
- You have the option of assigning a key combination to the macro by entering a letter — in this case, uppercase A—in
the Shortcut Key edit box.
- Store the macro in the currently active workbook by making sure the "This Workbook"
option is selected.
- Enter a description for the macro in the Description box; in this case, type Enter
my Poly address.
- To begin recording, click OK. Excel displays the message Recording in the status bar,
and the Stop Recording toolbar. Use the Stop Recording toolbar if you need to stop the recording
process.
- Select A2, and enter "Prof. Ming Leung". In A3, enter "Polytechnic University". In A4, enter
"Six Metrotech Center", and in A5 enter "Brooklyn, NY 11201".
- Click the Stop Recording Macro button on the Stop Recording toolbar. If the toolbar
isn’t visible, choose Tools, Macro, Stop Recording. This step is important; if you don’t
stop the macro recorder, Excel continues to record your actions indefinitely.
To test the new macro, clear the worksheet and then press Ctrl+Shift+A (the keyboard shortcut for this macro).
Excel runs the macro and perform the sequence of actions in the same way you recorded them
Note that keyboard shortcuts in VBA are case sensitive.