Step 1 – Recording Macros
What is a Macro? https://www.ziabis.com/
Imagine programming a robot to carry out a repetitive chore for you, such as washing the dishes. You might demonstrate all the actions involved so that the robot can ‘learn’ how to run a bowl of soapy water, how to clean a plate, etc. Then, once it has learnt all the tasks involved, each time the dishes need washing you simply have to command the robot to run its ‘WashDishes’ program. That is how a macro works in Excel: demonstrate all the tasks involved in some repetitive spreadsheet chore, then each time the chore needs repeating, just run the macro.
Macros can include any of the built-in features and functions of Excel in any series or combination. For example you might create a macro that applies some complex number format each time it is required. A more involved macro might open the daily sales spreadsheet, create a graph from today’s totals, add a header and footer, and print the graph. Macros are a powerful labour saving feature, which can accomplish all of the tasks within Excel.
Before Recording a Macro
Just like demonstrating washing-the-dishes, the tasks involved in a macro must be demonstrated to Excel so that it can register them in the appropriate sequence. This is called recording your macro, and Excel needs to be set up correctly before you start to record.
You wouldn’t record a TV programme without checking that the video was switched to the right channel, and that you weren’t about to record over your favourite film. In Excel too, there are a couple of things that need to be configured at the outset.
1) Choosing the starting position for recording
You must ensure that you are in the right place for the very first task of your macro; this is all too easy to forget. Let’s go back to the robot learning to wash dishes, the first task will be ‘go into the kitchen’, the second might be ‘squeeze the detergent bottle’. If the robot is already in the kitchen when you begin the demonstration, it will not be possible to demonstrate ‘go into the kitchen’; therefore the first task the robot will remember is ‘squeeze the detergent bottle’. This will result in a soapy carpet if you later, start the ‘WashDishes’ program when the robot is in the living room. For the purposes of the demonstration, you must start outside the kitchen, and include going to the kitchen sink as part of the robot’s programming.
To record a macro that enters a logo-type heading in the first cell of a spreadsheet, it is vital that when you begin recording, you are not already in that cell. That way, the first action of your demonstration or recording will be to go to the correct cell, before the text is entered.
To record a macro that enters this logo into A1, ensure that you begin elsewhere on the spreadsheet, so that Excel records the action of moving to A1 as a part of the macro.
If in doubt about where to start the recording from, remember that computers have no intelligence; that they will only carry out the instructions that they are told to. Ask yourself: What is the first thing that must happen in your macro?
Here are some examples to illustrate this point:
If the macro needs to open a particular workbook, and then perform some tasks, ensure that the workbook is not already open.
If the macro has to operate on a specific sheet, ensure that another sheet is selected when you start recording. That way you can record activating the sheet as part of the macro.
If the macro is going to apply some formatting to the selected cell or cells, ensure that the right cells are selected before starting to record. Otherwise your macro will select some different cells each time you use it.
2) Is movement on the sheet relative or absolute?
There are two distinct ways of recording movement. First – ‘Go to the kitchen’ is an absolute instruction, no matter from where it is carried out, the result is the same – you end up in the kitchen. Second – six feet forward and three to the left is a relative instruction, the outcome depends entirely on your starting position.
Programming the robot to wash dishes would start with an absolute instruction, whereas programming it to dance the foxtrot would be a series of relative instructions.
Just like the robot, some Excel macros need to be recorded with relative movement, and some absolute. Occasionally it is necessary to switch from one to the other during the process of recording. To record the ‘BBC Worldwide-logo’ macro, the first step is to go to A1, regardless of where it is started. This would, therefore, be recorded as an absolute macro.
Recording a Macro – The Developer Tab
To record macros in Excel 2007/10, you have a shortcut on the view tab, or… will need to display the ‘Developer tab’ in the ribbon. This is not visible as part of the standard install, but can be shown by clicking ‘Excel Options’ from the bottom of the ‘Office’ menu (which is the button in the top left corner of the screen). Then tick the box to ‘Show Developer tab in the Ribbon’.
(In 2010, you just right-click and customise the ribbon)
On the developer tab, you’ll find all the macro related stuff.
As detailed in the preceding section, the first step when recording any macro is to ensure that you are correctly positioned for the recording you are about to make. To record the macro which will insert the BBC Worldwide logo. Ensure that you are in a cell other than A1, that way the first procedure must be ‘going to the correct location’ for the logo. You will also need to ensure that the movement is recorded as absolute movement, so that it always goes to the same cell, regardless of starting position.
On the Developer Tab, click Record Macro… Excel will display the Record Macro dialog-box.
Enter a name for the macro, this is a unique title, which will identify this macro when you want to run it or amend it. Macro names can contain no spaces or punctuation, except the underscore “_” character. So multiple words must be compounded such as InsertWorldwideLogo, or separated by the underscore (Insert_Worldwide_Logo). Although macro names can contain both letters and numbers, they must begin with a letter. Additionally, there are certain macro keywords that are reserved for use solely by Excel, these include IF, SELECT, NEXT, TRUE, and a number of others. Excel will simply not record a macro with one of these words as a name. If you fall foul of any of these naming conventions, you will be alerted that the name is not valid.
Macro Names Must:
1. Begin with a letter
2. Contain no spaces or punctuation (except underscore _)
3. Not be one of Excel’s reserved words
There are also certain words that may cause problems in Excel; they include words like Sum, Format, Lookup, etc. The list of these words is long, and Excel will allow you to use them as names, but they may cause misunderstandings or ambiguity later on when running the macro. For that reason, it is wise to avoid any single (not compound) words unless they are evidently unconnected with computing such as ‘Walnut’ or ‘Arsenal’. The best names, however, clearly indicate the function of the macro, as this will assist the user in remembering the name or choosing it from a list. It is common, when using a single descriptive word to precede it with ‘My’, or add a set of initials such as ‘MyFormat’, or ‘LookupMJH’, in order to avoid using any of Excel’s reserved words.