If you haven’t spent much time playing with macros before, here we jump right in and show you how to get started straight away. Here we’ve created a series of lessons that show you how to:
- 1. Start and open Excel’s developer tab
2. Record a macro
3. View macro code – recording macros creates computer code – you’ll probably want to take a look at it
4. Play a macro manually so you can check it’s working OK
5. Run a macro automatically: so that you can play a macro back super fast e.g. using a button and save yourself time in the future
6. Safely copy a macro e.g. from an online source like this
7. Deal with Excel’s macro security settings
8. Make a macro available to all worksheets in Excel – not just the spreadsheet your first recorded it in
9. Simplify our macro code (the code produced by the macro recorder can be pretty ugly)
10. Loop a macro: set your macro working until it gets to the bottom of a list
11. Use different kinds of macro loops
12. Macro variables: assign variables within a macro
o Discover more about different kinds of macro variable
13. Perform basic mathematical operations on variables
14. Use Excel functions on VBA variables
15. Manipulate variables with If Then statements
16. Reroute and control program flow with GoTo statements
17. Understand more about VBA code: the macro object-property-method structure
o Perform a method on an Excel object
o Set macro object properties
18. Perform multiple actions on an object by using a With statement
19. Use and create macro dialog boxes: message boxes, input boxes and your own user forms. It’s a big lesson this one and you might need to allow a bit more time for it than some of the others. We show you how to:
o Add option buttons, command buttons or other buttons and input boxes to a custom user form
o View your user form macro code (created when you add something to a user form)
o Structure the user form macro code – get each piece of your user form code doing exactly what you want it to
o Get the user form working by getting the right code in the right place, calling the user form and being careful how we declare our macro variables
20. Tap into other helpful sources of information on macros, test your knowledge using our (not that small but almost perfectly formed) macros glossary and browse our collection of handy VBA code examples from this course. This is another ‘chunky’ set of material and is designed to help you review and cement the learning from the previous lessons.
What's a macro?
What is a macro?
A macro works like a tape recorder, storing every step you take in Excel as computer code (“Visual Basic for Applications” or “VBA”) and making it available for later play back.
How could a macro be useful?
Good uses for macros
You might be tempted to record/ create a macro when you find yourself carrying out the same repetitive actions over and over in Excel. For example:
- Imagine once a month you are required to access another spreadsheet held within your company containing management information that is continuously updated. Each month you a required to access that spreadsheet, copy and paste some of the data from it, and reformat it. Every month you find yourself copying and pasting the data and formatting it in exactly the same way. The whole exercise bores you stupid – so suddenly you start thinking about whether you could set the whole process to run at the click of a button. Suddenly you’re thinking about recording a macro.
- Imagine that you find yourself regularly creating a spreadsheet for use within your organisation. Each month you have to go through each tab and set each tab to print just the way you like it – for each tab there are a lot of settings involved. Wouldn’t it be nice if you could automate that whole process using a macro?
- Imagine you are regularly importing a long list of data into your spreadsheet. The list is long but every week or month the length varies – you can never be sure how deep the data will run in your spreadsheet. You have to manipulate the numbers in complex ways to get the exact outputs you want. Want you want to avoid is having to create a series of extra long columns in your spreadsheet that step through the calculations. Wouldn’t be nice if you could automate the whole process to get the output you want? You’re starting to think that it might be helpful to have a macro that scrolls through the whole long list of data, performs the calculation and provides you with the output you want.
Start thinking about whether a macro might save you time when you find yourself regularly performing the same repetitive action in a spreadsheet – something that’s just boring you stupid.
Three warnings about macros - they're pretty harsh warnings
Here are three warnings about macros. We’re know you’re not going to be put off easily (that’s why you’re here). They’re just something to keep in the back of your mind.
Warnings about macros 1: they can be unfathomable to all but the person who created them
Excel’s functionality is incredibly powerful, so we’d say that it’s very rare that you’d feel that you need to write a macro to solve a financial modelling problem.
We’re going to play with macros very shortly and you’ll see that every step you take is recorded as VBA computer code. We’ll look at the computer code you create. Most computer programmers would agree that the code that results is pretty ugly code. It includes lots of line items that don’t need to be there. It doesn’t include any notes that explain what’s going on. If you’re very smart you might just be able to understand what’s going on in the code you have created, but it’s going to be very hard for anyone picking up your spreadsheet to work out what the macro is doing.
This is the warning: macros are pretty much unfathomable unauditable and un-checkable for all but the original user who created them. If you want to build a model that your organisation is totally reliant on and only you can operate and make sure you can never get sacked, then maybe that’s a reason why you’d want to stuff your spreadsheets full of macros. If you want to build models that can be passed around your organisation and used by other people it’s probably best to avoid macros.
Warnings about macros 2: they break
We’ve had plenty of people on our financial modelling courses who have been struggling to make macros they’ve inherited from another user work. The news is not good. Firstly (point 1 above) it’s very hard to determine what the original user was trying to achieve with the macro. Secondly: macros break. If you think about what macros are doing (recording a set of step by step actions) if one part of that sequence changes or needs to change, e.g. because source data has changed or changed position or the underlying model has changed, the macro is bound to break. That might explain why the macro you’re trying to use is broken. It’s linked to the underlying data or the underlying model and something there has changed, meaning the macro no longer works.
If you are having trouble working with a previous modeller’s macros, and that user is not around to talk to any more, it’s likely that the best solution is to ignore the macros. We’re just trying to give you the most practical advice we can here. It’s going to be very hard to work out what the original macro was supposed to do, and harder still to work out where the macro is broken. It’s too much to expect – that you might be able to pick up another user’s macros and start working with them, manipulating them or fixing them.
Our advice (and it’s practical advice this) is to ignore the existing macros. Think about what the spreadsheet was supposed to be achieving. Come up with your own solution to the problem. Make careful use of Excel’s standard and deep functionality in solving the problem – thereby leaving a clear step by step record in your model of what you’ve done. Try and avoid using a macro to solve the problem, reducing the risk that a subsequent user picks up your model and can’t work out what’s going on or change what’s going on.
Warnings about macros 3: viruses are made of computer code too
If you start messing around with macros you’ll notice that Excel gives you lots of security warnings around macros. If you create or import a macro it’s likely that you’ll have to change your Excel settings just to save the spreadsheet. If you send that spreadsheet to someone else within your organisation it’s likely that Excel will warn them about the spreadsheet and ask them to change their settings before they open it. You should be able to see that even Excel is trying to make you nervous about macros.
If you think about what a macro does (attaches computer code to your spreadsheets that goes away and automatically performs a series of actions when you push a button) it’s not too hard to imagine how a nasty someone could maybe include malicious computer code in a macro. That’s why Excel gives you so many warnings around them. If you get into the habit of downloading random macro enabled spreadsheets from dubious sources on the internet you’re bound to run into trouble eventually.
Shall we tell you the apocryphal story we heard from a bank we went in to train? This one was about a trainer (not an FTA trainer we hasten to add) who came in, opened up a macro enabled spreadsheet and managed to bring down the bank’s systems. Whether the story is true or not we can’t be completely sure (the bank analysts we spoke to were sure it was) but the idea of having to explain how you managed to bring down your company’s systems might put you off playing fast and loose with macros.
We don’t want to put you off completely though. If you build your own macros from scratch you’re not going to risk importing a computer virus. Even if do import code what you could do is look at the steps in the computer code in advance and make sure you can see what’s going on there before copying and pasting the code into what you are doing. You could work in a ‘sterilised’ environment to start (spare computer, offline and unconnected to company systems). You could talk to your IT department about what you are thinking of trying and get their advice. We don’t want to put you off macros for life. We’d be worried though if you got into the habit of downloading and automatically running macro enabled spreadsheets from sources you are unsure about. With its inbuilt warnings Excel itself is trying to tell you that’s a bad idea: it’s asking for trouble.
The benefits of avoiding macros altogether
Good modelling practice guideline: no macros
Because other users will have problems understanding your macros, if we were able to issue a good practice guideline around macros it would probably be this: avoid them for anything that’s core to your analysis. You don’t need macros if you want to be successful in financial modelling. By all means use them for automating routine mundane tasks that are just boring you stupid. Feel free to have some fun with them. But don’t use them in any analysis that results in key outputs in your modelling. There’s a good chance no-one else will be able to work out what you have done.
Ready to get started with macros? Let’s jump in
If you’ve read this far, and made it through our macro health warnings, it’s likely that you’re made of stern stuff and you’re not going to be put off easily. You’d probably like to have a play with macros (maybe because you’re a little bit curious, maybe you’re thinking that they might be fun, maybe you’ve seen someone else using them and you think they look interesting). We’re happy to help with all that. Now we’re going to show you how to jump right in: we’re going to help you open Excel’s developer tab.
What’s this all about?
You’re looking at a free online financial modelling course extract.