Recording A Macro
If you have a simple set of actions that you want to carry out again and again, you can make Excel record these actions and produce a macro, containing the code to repeat these actions. Once you have recorded the macro, you can repeat the set of actions as many times as you like, by simply running the recorded macro. This is much more efficient than repeating the same set of actions manually each time.
In order to record a macro, you need to initially start the recording process. This option resides in the Macros menu, which, in Excel 2007 or Excel 2010 is located in the View tab, and in Excel 2003 is located in the Tools drop-down menu. These options are shown in the images below :
You will then be presented with the options box shown on the right. You can enter a name and description for the macro if you wish. It is a good idea to give the macro a meaningful name, so that when you come back to the macro at a later date, this will assist you in remembering what it does. However, if you do not supply a name, Excel will automatically assign a macro name (macro1, macro2, etc.).
While in ‘Record Macro’ mode, Excel remembers every action that you perform and stores it as VBA code. When you have completed the actions that you want to record, you can stop the macro recording by clicking on the stop button (as illustrated below).
You are also given the option of assigning a keyboard shortcut to your macro. This will make the macro much easier to run. However, you should be careful not to assign one of Excel’s predefined key combinations (eg. CTRL-C) to the macro. If you do select an existing Excel key combination, this will be overwritten by your macro, and you, or other users, may end up accidentally executing your macro code.
The ‘Use Relative References’ Option
If you select the Use Relative References option before you begin to record a macro, then cell references within the macro are all relative. This means that if, for example, you move from active cell A1 to cell C1, this will be recorded, within your recorded macro, as “Select the cell that is two cells to the right of the current active cell”. However, if the Use Relative References option has not been selected, this action will recorded as “Select cell C1”, regardless of which cell is initially the active cell.
For recording a macro do the following as described:
- On the Developer tab, in the Code group, click Record Macro.
- In the Macro name box, enter a name for the macro.
- To assign a CTRL combination shortcut key (shortcut key: A function key or key combination, such as F5 or CTRL+A, that you use to carry out a menu command. In contrast, an access key is a key combination, such as ALT+F, that moves the focus to a menu, command, or control.) to run the macro, in the Shortcut key box, type any lowercase letter or uppercase letter that you want to use. Note:The shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open.
- To include a description of the macro, in the Description box, type the text that you want.
- Click OK to start recording.
- Perform the actions that you want to record.
- On the Developer tab, in the Code group, click Stop Recording.
- Save your workbook as macro enabled workbook
Running Excel Recorded Macros
When recording macros, Excel always produces a subroutine (rather than a function). If you have assigned a keyboard shortcut to the macro, then this shortcut will be the simplest way of running the macro.
Otherwise, the macro can be run by performing the following steps :
- Press ALT-F8
- Select the macro you wish to run
- Click Run