The same ten clicks, every Monday
Every Monday a clerk opens the weekly sheet. They remove blank rows, fix the codes, add a total and save a copy. Ten clicks, every week.
A macro can do all ten in one go. But only if the sheet is the same each week.
Key words
| Word | What it means |
|---|---|
| Macro | a saved set of steps that a program repeats |
| Automation | getting a computer to do a task by itself |
| Requirement | something the automation must do |
| Standardised data | data laid out the same way each time |
| Recording | saving your own clicks as a macro |
| Test | run the macro on sample data and check the result |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can plan a macro, check the data suits it and test the result.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Identify the requirements of the automation
Write down what the macro must do, in order, and what it starts with and ends with. Who runs it? How often? What must it never touch?
Good requirements are exact, such as "remove blank rows, fix codes, add a total".
The Monday job as recorded steps
| Step | Action | Needs the data to be |
|---|---|---|
| 1 | Delete rows where the code is empty | Codes always in column A |
| 2 | Trim spaces and use capitals in column A | Codes always text |
| 3 | Turn units into numbers | Units never words such as six |
| 4 | Add a total row at the bottom | No blank rows inside the data |
The cells students fill in are left blank in this preview.
Assess the standardisation first
A macro does the same steps every time. It cannot think. If a code sits in column B this week, or units say "six", the macro breaks or gives a wrong answer.
Check the sheet is standardised data: same columns, same order, same types.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.