Record A Macro in Excel

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.

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 Excel View tab, (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):

Excel 2010 Record Macro Option

Record Macro in Excel 2003:

Excel 2003 Record Macro Option

Excel Record Macro Options Box

You will then be presented with the options box shown on the rightabove. You can 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).

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.

Once you are satisfied with your macro name and (if required) keyboard shortcut, select OK to start the macro recording.

As soon as your macro starts to record, every action that you perform (entering data, selecting cells, formatting cells, scrolling down the worksheet, etc) will be captured by the new macro and stored 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:

Macro Stop Button in Excel 2007 & Later Versions of Excel:
Excel 2010 Record Macro Stop Button
Record Macro Toolbar
in Excel 2003:
     Excel 2003 Record Macro Toolbar

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 before you begin to record a macro, then cell references within the macro are all 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 resides 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:

Excel 2010 Use Relative References Option
Use Relative References
Button in Excel 2003:

Excel 2003 Use Relative References Button

Viewing the VBA Code

Visual Basic Editor Project Window

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 (ie. 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 your 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 :


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 :

Also, the recording feature 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 will need 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.