Home » Writing-Excel-Macros » Record-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 off the recording process. This option resides in the Macros menu, which, in Excel 2007 is located in the View tab, and in Excel 2003 is located in the Tools drop-down menu. Alternatively, in Excel 2007, you can set off Macro recording by clicking on the icon in the bottom left hand corner of the Excel workbook. These options are shown in the images below :

In Excel 2007:

OR

In Excel 2003:

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).

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 have started the macro recording, in Excel 2003, a floating toolbar containing a stop button pops up on the screen (see right), whereas is Excel 2007, the icon on the bottom left of the Excel workbook changes to a stop button (see below).


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 above).

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.

The Use Relative References option resides in the Macros menu in Excel 2007, or is presented on the Macro Toolbar in Excel 2003. This is illustrated in the images below.

Excel 2007 Use Relative References Option:

Excel 2003 Use Relative References Button:

Running Excel Recorded Macros

When recording macros, Excel always produces a subroutine (rather than a function). 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 (ie. press the ALT key and while this is pressed down, press F8)
• From the list of macros that appear, select the macro you wish to run
• Click Run

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 up by pressing ALT-F11 (ie. press the ALT key, and while this is pressed down, press F11). The code for an Excel recorded can be accessed by clicking on one of the modules in the project window, which is positioned to the left of your visual basic editor. A simple project window is shown in the image on the left.

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 any of the VBA features that store information in memory, such as :

• Defined Constants, Variables and Arrays
  
  
• If Statements
  
  
• Loops
  
  
• Calls to Built-In Functions or Other Procedures

Also, the recording feature can only produce subroutines (not functions), as it cannot return a value. These subroutines 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 start you off with code that you can adapt or insert into your more complex macros.