If you have a simple set of actions that you need to repeat several times over, you can make Excel record these actions and produce a macro, containing the code to repeat them.
If you select the Use Relative References option during the recording of a macro, then all cell references within the macro will be relative. However, if the Use Relative References option has not been selected, any cell references appearing in the code will be absolute (see the Excel Cell References page if you want to learn more about these.
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 off the recording process. This option resides in the Macros menu, which is located in the View tab of the Excel ribbon, (or in the Tools drop-down menu in Excel 2003). These options are shown in the images below:
Record Macro in Current Versions of Excel (2007 and Later):
Record Macro in Excel 2003:
You will then be presented with the 'Record Macro' dialog box shown on the rightabove. This box allow you to enter a name and description for your 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 help you to remember what it does. However, if you do not supply a name, Excel will automatically assign a macro name (e.g. Macro1, Macro2, etc).
The 'Record Macro' dialog box also gives you 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 (e.g. 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.
Once you are satisfied with your macro name and (if required) keyboard shortcut, select OK to start the macro recording.
Once the macro starts to record, every action that you perform (entering data, selecting cells, formatting cells, scrolling down the worksheet, etc.) will be recorded in the new macro, as VBA code.
Also, while the macro is recording, a stop button will be displayed at the bottom left of your workbook (or in Excel 2003, the stop button will be presented to you on a floating toolbar), as show below:
When you have completed the actions that you want to record, you can stop the macro recording by clicking on the stop button. Your macro code will now be stored in a module within the Visual Basic Editor.
The 'Use Relative References' Option
If you select the Use Relative References option during the recording of a macro, then all cell references within the macro will be relative. However, if the Use Relative References option has not been selected, any cell references appearing in the code will be absolute (see the Excel Cell References page if you want to learn more about these two types of cell references).
The Use Relative References option is found in the Macros menu (and is located on the Macro Toolbar in Excel 2003). This is illustrated in the images below:
Relative References Option in Current Versions of Excel: | Use Relative References Button in Excel 2003: |
Viewing the VBA Code
The VBA code produced by the macro is placed into a module, which can be viewed via the Visual Basic Editor. This can be opened by pressing Alt + F11 (i.e. press the ALT key, and while this is pressed down, press F11).
The code is located in one of the modules in the project window, which is positioned to the left of the visual basic editor. In the simple project window shown on the rightabove, you could view this code by double-clicking on 'Module 1'.
Running Excel Recorded Macros
When recording macros, Excel always produces a Sub procedure (rather than a Function procedure). If you have assigned a 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 (i.e. press the ALT key and while this is pressed down, press F8) to bring up the 'Macro' dialog box;
- Within the 'Macro' dialog box, select the macro you wish to run;
- Click Run.
Limitations
Although the Excel macro recording feature is a very simple way of creating VBA code, it can only be used for very basic macros. This is because it cannot make use of many VBA features, such as:
- Defined Constants, Variables and Arrays;
- If Statements;
- Loops;
- Calls to Built-In Functions or Other Procedures.
Also, the recording tool can only produce Sub procedures (not Function procedures), as it cannot return a value. These Sub procedures cannot be passed any arguments, although they are able to identify the current active cells, ranges or worksheets, and values stored in the cells of the workbook. It should also be noted that the code generated is not always the most efficient code possible for the required actions.
While Excel's automatically generated VBA code is fine for simple macros, if you want to produce more complex macros, you may wish to learn to write VBA code for yourself. However the Excel Macro Recording feature is an excellent tool to provide you with code that you can adapt or insert into more complex macros.
Return to the Writing Excel Macros page
Return to the ExcelFunctions.net Home Page
Return to the ExcelFunctions.net Home Page
When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) But quite often, this is the wrong recording mode. If you use absolute recording mode, Excel records actual cell references. If you use relative recording, Excel records relative references to cells. Keep reading to see the difference.
Recording in absolute mode
Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names in a worksheet:
- Make sure that the Developer → Code → Use Relative References button is not highlighted and then choose Developer → Code → Record Macro.
- Type Absolute as the name for this macro.
- Click OK to begin recording.
- Activate cell B1, and type Jan in that cell.
- Move to cell C1, and type Feb.
- Move to cell D1, and type Mar.
- Click cell B1 to activate it again.
- Stop the macro recorder.
- Press Alt+F11 to activate the VBE.
- Examine the Module1 module.Excel generates the following code:When executed, this macro selects cell B1 and inserts the three month names into the range B1:D1. Then the macro reactivates cell B1.
These same actions occur regardless of which cell is active when you execute the macro. A macro recorded by using absolute references always produces the same results when it is executed. In this case, the macro always enters the names of the first three months in the range B1:D1 on the active worksheet.
Recording in relative mode
In some cases, you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording.
You can change the manner in which Excel records your actions by clicking the Use Relative References button in the Code group of the Developer tab. This button is a toggle button. When the button appears highlighted in a different color, the recording mode is relative. When the button appears normally, you are recording in absolute mode.
You can change the recording method at any time, even in the middle of recording.
To see how relative mode recording works, delete the contents of range B1:D1 and then perform the following steps:
- Activate cell B1.
- Choose Developer → Code → Record Macro.
- Name this macro Relative.
- Click OK to begin recording.
- Click the Use Relative References button to change the recording mode to relative.When you click this button, it changes to a different color from the rest of the ribbon.
- Type Jan in cell B1.
- Move to cell C1, and type Feb.
- Move to cell D1, and type Mar.
- Select cell B1.
- Stop the macro recorder.
Notice that this procedure differs slightly from the previous example. In this example, you activate the beginning cell before you start recording. This is an important step when you record macros that use the active cell as a base.
This macro always starts entering text in the active cell. Try it. Move the cell pointer to any cell and then execute the Relative macro. The month names are always entered beginning at the active cell.
With the recording mode set to relative, the code that Excel generates is quite different from the code generated in absolute mode:
To test this macro, activate any cell except B1. The month names are entered in three cells, beginning with the cell that you activated.
Notice that the code generated by the macro recorder refers to cell A1. This may seem strange because you never used cell A1 during the recording of the macro. This is simply a byproduct of the way the macro recorder works.