Excel macros

A macro ‘memorises’ a certain routine or set of steps taken within an Excel financial model. To create a macro, you set Excel to record as you complete a set of actions within your spreadsheet.  This record is retained within the spreadsheet and can be played back at any time.  Effectively, if you have recorded and then play back a macro, you have created a short cut that will automatically perform a sequence of actions within your spreadsheet.

We can help you with macros!

We can really help with macros – we have a lot more material available for you on Excel VBA macros. But if you’re in a hurry to get started, we’ve given you the briefest of introductions just right here.

Financial modelling with macros: getting started

These instructions are for Excel 2007 and 2010.  If you are using Excel 2003, the process is very similar.  Please download the macros spreadsheet for detailed instructions for different versions of Excel.  In Excel 2007 & 2010, the first thing you need to do is get the developer tab showing, if it is not showing already.  In Excel 2007, go to the Office button at the top LHS of Excel.  Select “Excel Options”, “Popular” and select “Show Developer tab in the Ribbon”.  You will only need to do this once.   In Excel 2010 you can achieve the same thing from the “File” menu (under the “File” tab click “Options”, “Customize Ribbon” and tick “Developer” from the list on the RHS).

Financial modelling with macros: a simple example

In this example we’re going to pretend that we often need to format certain cells to a particular house style. Perhaps we find ourselves regularly formatting certain spreadsheet cells with a specific font type, font style, border style and colour – to the point where all that time spent formatting is becoming boring.  What we want to do is automate the formatting routine and link it to a button right at the top of Excel.  When we click on the button, we want the formatting to happen automatically.  To keep all this simple, let’s imagine that we’re going to create a macro that formats cells a specific colour e.g. blue or orange.

Find the macros section on the developer tab

Go to the “Developer” tab.  Under “Code” click on “Use Relative References”.  Be very careful at this next stage.  You are about to start recording every action you take within your spreadsheet.  Everything you do from this point forward will be remembered.  If you don’t stop the macro recording at the next stage, you could end up corrupting your spreadsheet.

Record the macro

Click on “Record Macro”.  Name the macro e.g. “Format_cells” without any spaces.  At this stage, if you wish, you can assign a shortcut key to the macro e.g. Ctrl “f”.  Go (for example) to the “Home” tab.  Under the “Font” section, format the current cell say e.g. orange or blue.  Select the developer tab again.

Stop the macro recording

As mentioned above, it is very important that you stop the macro recording.  At the bottom of your spreadsheet, click on the (very) small square button “stop recording”. If you have assigned a shortcut key e.g. Ctrl “f” you can now use your own shortcut key to format cells in your financial model.

Link the macro to a quick reference button

Alternatively, if you prefer, you can set the Macro to run from a button at the top of Excel.  In Excel 2007, from the Office button (at the top LHS) click “Excel Options”, and select “Customize”.  In Excel 2010 you can select the same items from the “File” menu (“File”, “Options”, “Quick Access Toolbar”).  Under the “Choose commands from:” drop down menu, choose “Macros”.  Click on the macro you have just created.  “Add” it across to the RHS.  Click on “Modify” to allocate an incon you prefer – one that makes sense to you.  The new button will appear in Excel right at the top of your financial model.  Now you can click on the button and format to your heart’s content!

Top financial modelling course tips: macros

Macros are a great way of automating mundane financial modelling tasks that otherwise might drive you mad.  You often find them used to automate routine tasks such as formatting (as per the example above), or to automate certain print routines (automating specific print settings).  Occasionally you’ll see macros used for something a little more serious in a financial model.  For example, something like copying and pasting key outputs from a run of the model into the area of the model used to graph those outputs.

Financial modelling: when not to use macros

Unless you really have to, please don’t use macros for automating serious analysis within your model (for example, we have seen users create macros to ‘try’ different values within a spreadsheet until the model delivers a particular desired result such as a balancing balance sheet).  Macros are recorded as computer code behind the model and are otherwise invisible.  If you really wish to, you can access the code behind a macro by going to the “Developer” tab.  Under “Code”, click on “Macros”.  Select the macro you are interested in and click “Step Into”.  For most users, and certainly for a macro of any substance, this “Visual Basic” code is impenetrable.  Even if you know how to use it, chances are the next analyst won’t, and you will have successfully created a financial model that is unusable to most other people within your organisation. For that, you’ll get no brownie points from us!

Remember we’ve got a lot more comprehensive material waiting on macros!

Please sign up to our Excel VBA macros course if you think you’d like to know more about macros.

About this course material

You’re looking at free online training on advanced Excel functions – it’s an extract from our financial modelling course training.

 

 

...join the conversation...

Your email address will not be published. Required fields are marked *