Get started with Excel VBA
Learn Excel VBA with our free macros course.
Learn VBA for free with 20 bite-sized lessons over four weeks
We’ll send you 20 “bite-sized” lessons. One each day over four weeks, delivered direct to your inbox. All totally for free.
We promise the lessons will start off very short and very simple. If you stick with it, and practise just a little bit, we think we can get you up the curve on Excel VBA macros.
Free Excel VBA course content
What the Excel VBA course covers
With VBA macros you can automate routine repetitive tasks in Excel. If you’re performing the same tasks in Excel regularly (and it’s boring you a bit to do that) you should start thinking about using a macro. You’ll learn how to record a routine set of actions in Excel and play that routine back at will.
This course gets you started with VBA and very quickly gets you to the point where you can create a first macro of your own – recording routine Excel tasks and playing them back automatically.
The course then moves on to help you harness more of the power of VBA, looping macros, establishing variables, creating input and output/ message boxes, controlling program flow and creating your own custom user forms/ dialog boxes.
What's this course material doing here for free?
Why we are doing this – all for free
Live course training is what we do. But we want as many people as possible to know something about us. That’s why we make some of our training material available for free.
We figure that before you went to a rock concert you might want to hear some of the music first. There really is no more to it than that. There really is no catch.
If you’d rather have this material (and more) covered live, with assistance provided by a friendly expert trainer, this is the course you need: financial modelling masterclass.
Get started now
To get started just use the sign up form and we’ll send you your first Excel VBA lesson straight away.
Could macros ever be right for you?
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 are 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 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 you could say it’s probably best to avoid macros (or make sure you create well structured and well commented macros like we’ll show you here).
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) unless the macro has been structured and annotated well 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 dodgy macros and start working with them, manipulating them or fixing them.
Our advice (and it’s practical advice) 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. First see whether you can 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 (we show you how to do that here) you’re not going to risk importing a computer virus. Even if you 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 though. Feel free to have some fun with them.
Jumping into macros
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. Sign up and we’ll show you how to jump right in. With your first lesson we’re going to help you open Excel’s developer tab and in the second lesson we’re going to get you recording a macro.