Study smart | Excel superstars
Understanding macros and how to use them
Excel is well known for formulas, charts and PivotTables, but one of its most powerful time-saving features is macros
Words Traci Williams, excelace.co.uk
A macro automates repeated actions in Excel. Tasks that might take minutes manually, such as copying, cleaning and formatting, can be done by a macro in seconds.
Macros are ideal for processes that run frequently. They save time, reduce manual effort and improve accuracy by removing the risk of human error.
Why use macros?
Typical benefits:
- Save time (they can turn 30-minute tasks into three-second tasks)
- Reduce human error
- Improve consistency
- Automate large data sets
- Integrate workflows — importing, formatting, reporting, exporting
If you repeat a task more than once a week, it’s usually worth automating.
How to create a macro
There are two ways to create macros:
- Record a macro (Excel captures keys pressed / mouse clicks)
- Write VBA code (Visual Basic for Applications)
Record a macro
This is the simplest option and ideal for beginners:
- View ribbon >> Macros >> Record Macro
- Name the macro (no spaces), then click OK – the macro is now recording
- >> Perform the steps in Excel <<
- View ribbon >> Macros >> Stop Recording
Or, you can click this icon on the bottom left of the tray to start recording (step 1 above):
Follow steps 2 and 3. To stop recording, click on the square icon on the bottom left of the tray:
As you record, Excel automatically generates VBA code. Reviewing this code is a great way to start learning how VBA works.
1. Write VBA
Writing VBA gives far more flexibility than recording and is often easier than expected. You can also edit recorded code.
There are three options to access the VBA:
- Alt + F11 (Windows)
- Right-click Tab name and select View Code
- Developer ribbon >> Visual Basic
The VBA screen will appear as follows:
- Project pane (blue): All sheets and modules VBA can interact with
- Properties pane (green): Controls behaviour and appearance
- Code window (pink): Where the macro code lives
A commonly used property is visible, which has three options:
- -1 (xlSheetVisible) – sheet is visible
- 0 (xlSheetHidden) – sheet is hidden (but visible on Unhide list)
- 2 (xlSheetVeryHidden) – sheet is hidden (NOT visible on Unhide list and only controllable via VBA)
VeryHidden is useful for sheets you don’t want users accessing.
To write some VBA code, you would need to:
- Insert >> Module
- Start typing the code into the pink area (see above)
You can navigate between the VBA screen and a spreadsheet by selecting the relevant screen from the Excel icon (at the bottom of the sheet). You can freely toggle between the two as much as you like.
Macros will be saved as part of the Excel file; they don’t need to be saved separately.
Files containing macros MUST be saved as FileType: Macro Enabled Workbook (.xlsm).
Writing VBA Code is better for dynamic, flexible macros as you can make them do so much more than the recording is capable of.
e.g. A recorded macro can only hide specific columns you clicked on, but a written VBA macro can loop through all columns and hide anywhere the total = 0, even when the data size changes.
Example 1: Clean and format data
If a weekly report arrived like this:
We would need to apply formatting to the headers, numbers and include a Total row.
This is the perfect example to record the macro, which would appear in a module like this:
Note: the text coloured green starts with an apostrophe and Excel reads this as a ‘comment’ and NOT part of the code. It is a great function to include ‘comments’ throughout your code so you can refer to them when troubleshooting in future.
The data will then appear like this:
In future, when we receive similar data we can just run the macro, and all the formatting and totals will be applied automatically.
Run macro:
Select relevant sheet to apply macro to
- Select relevant sheet to apply macro to
- View ribbon >> Macros >> View Macros
Select required Macro, and click Run (could click Edit to enter VBA screen and edit)
The macro will be applied to the data on the current sheet.
Macro buttons:
The macro can be assigned to a ‘button’, which can then be used to run the macro instead of having to find it from the View ribbon (above). This is a much simpler way to manage macros, especially if they are to be used by non-technical users.
- Insert ribbon >> Illustrations >> Shapes >> choose a shape and drop it onto the sheet wherever you like (format and resize as required)
- Right-click the shape and select Edit Text to name the ‘button’
- Right-click the shape and select Assign Macro, then click OK
Now, when you click this button, the macro will be applied.
More examples
There are almost limitless purposes for macros, but here are a couple more examples:
Removing blank rows
This macro deletes rows where column A is blank:
The code loops from the last row up to avoid skipping rows and will delete any rows where column A is blank.
This is how the data will appear after the macro has been run.
Save as PDF
This macro will save the current sheet as a PDF to a user-chosen location (using the current print range and settings):
Now you have an automated file export in seconds.
Apply discount
With this macro, we can prompt for a discount % and apply it to the values in column B (this uses column Z as an ‘interim’ helper column, so you may need to adjust this if you have data in that column):
In this instance, a 50% discount has been applied without needing to use any formulas.
Macro security
Macros can control more than Excel, including files and Outlook. For safety, Microsoft blocks untrusted macros by default.
Key Risks
- Malware can come via emailed Excel files (make sure you trust where they are being received from)
- Code may run silently if macros are enabled
- Sensitive data can be overwritten or exported
Best practices
- Store files in trusted locations
- Use digital signatures
- Restrict access with passwords
- Include error handling
- Never enable macros from unknown sources
When macros aren’t ideal
- Heavy data processing: Power Query / Power Automate now exist and can do things that historically we relied on macros for, and can be far better.
- Cross-platform use (Mac/web apps): VBA support varies
- Excel Online: Macros not supported (they don’t exist in this platform)
- Despite this, macros remain essential in many organisations.
Conclusion
Macros make Excel smarter, faster and more reliable. With a few lines of VBA, you can:
- Automate formatting and reporting
- Standardise imports
- Interact with users
- Reduce errors
- Speed up recurring tasks massively
- Save significant time
Start simple using the macro recorder. Each new task builds skill and soon you’ll be automating workflows that once took hours.
QUIZ TIME
Test your knowledge
Roll over the buttons to find the green correct answer or click on the buttons to find the correct answer
1
What is the main purpose of a macro in Excel?
2
Which language does Excel use for macros?
3
Which of the following is a macro security best practice?
The Association of Accounting Technicians. 30 Churchill Place, London E14 5RE. Registered charity no.1050724. A company limited by guarantee (No. 1518983).