Excel macros: what they are and how to use them

If you have a simple series of actions that you need to repeat multiple times, you can have Excel record these actions and produce a macro containing the code to repeat them.

Once you have recorded the macro, you can repeat the series of actions as many times as you want, simply by running the recorded macro. 

This is much more efficient than manually repeating the same series of actions each time.

To record a macro you must initially start the recording process. This option is found in the menu Macro , which is located on the tab Immagine in the Excel ribbon (or in the menu a downhill Tools in Excel 2003). These options are shown in the images below:

Record macros in current versions of Excel (2007 and later):

You will then be presented with the “Record Macro” dialog box. 

This box allows you to enter a name and description for your macro, if desired. It's a good idea to give the macro a meaningful name, so that when you return to the macro later, this will help you remember what it does. However, if you do not provide a name, Excel will automatically name the macro (e.g. Macro1, Macro2, etc.).

The “Record Macro” dialog box also gives you the option to assign a keyboard shortcut to your macro. This will make the macro much easier to run. However, you must be careful not to assign one of the pre key combinations to the macrodefinite of Excel (e.g. CTRL-C). If you select an existing Excel key combination, it will be overwritten by your macro, and you or other users may end up accidentally running the macro code.

Once you're happy with the macro name and (if necessary) keyboard shortcut, select OK to start recording the macro.

Once you start recording your macro, every action you perform (data entry, cell selection, cell formatting, worksheet scrolling, etc.) will be recorded in the new macro, as VBA code.

Additionally, as you record your macro, you will see a stop button at the bottom left of your workbook (or in Excel 2003, the stop button will be presented on a floating toolbar), as shown below:

Once you have completed the actions you want to record, you can stop recording the macro by clicking the Stop button. The macro code will now be stored in a module within the Visual Basic editor.

The 'Use relative references' option

If you select the option Use relative references When recording a macro, all cell references within the macro will be relative. However, if the option Use relative references is not selected, all cell references displayed in the code will be absolute (see our post on reference operators).

The option Use relative references It's in the menu Macro (and is found on the Macro toolbar in Excel 2003). 

Running recorded macros

When recording macros, Excel always produces a Sub procedure (rather than a Function procedure). If you have assigned a keyboard shortcut to the macro, this shortcut will be the easiest way to run the macro. Otherwise, the macro can be run by performing the following steps:

• Press Alt + F8 (i.e. press the ALT key and while it is pressed, press F8) to display the 'Macros' dialog box;
• In the “Macro” dialog box, select the macro you want to run;
• Click su Run .

Ercole Palmeri