Spreadsheets : Macros

Spreadsheets : Macros

A macro is a short sequence of instructions that will automate a task. Using macros can save you a lot of work when you are using a spreadsheet by carrying out repetitive tasks for you. Not all spreadsheet programs will let you use macros.

Consider a spreadsheet which shows the league positions of some football teams in a league. Whenever a team plays a match the team's points score and position in the league may change. You will need to sort the data in the spreadsheet to put the teams into points order after each match. To sort data in most spreadsheets you will need to follow these three steps :

Select the area of the spreadsheet that contains the data to be sorted (the league table).
Choose which piece of information (e.g. points) in the table you want to use to order the data.
Select whether you want to put the data in ascending or descending order.

Manually completing this process every time a team played a match would be very time consuming. Instead a macro can be created that will automatically carry out the three steps for you. Once a macro is created you can carry out the entire process by simply executing (or running) the macro.

Creating A Macro

A macro can be created by either programming or recording it.

Programming A Macro

A macro is stored as a sequence of instructions in a macro language. Programming a macro is like writing a program in a high level language. The user writes the instructions that will carry out the actions required of the macro. The macro instructions to sort the football league table might look something like this :

SELECT A1:F6
SORT USING COLUMN F DESCENDING

Most computer users do not have time to learn how to program macros, so a simpler method of creating basic macros is available.

Recording a Macro

To record a macro you simply show the computer what you want the macro to do. A macro can be recorded like this :

Turn on the macro record feature.
Carry out the actions that the macro must follow manually using a mouse and keyboard.
Turn off the macro record feature.

When this process is followed the spreadsheet will automatically generate the macro instructions as if the macro had been programmed.

When a macro is being designed a flowchart is often used to show the sequence of actions that the macro should carry out.

Executing A Macro

Every macro must have a name to identify it. When a macro is selected and its macro instructions are carried out the macro is said to be executing or running. There are two ways that you can execute a macro :

From A Menu : If you choose the macro's name from the spreadsheet's macro menu it will automatically be executed.
From A Button : Macros can usually be assigned (linked) to buttons which you can create on the spreadsheet. If you press a button it will run the macro that has been associated with it.

GCSE ICT Companion 04 - (C) P Meakin 2004