It is a common request among those that aspire to be great modellers, that they want to "learn" VBA. But just as knowing your debits and credits doesn't make you good at business, knowing VBA doesn’t make you good at modelling. It is more difficult, but more useful, to learn how to structure your models to reduce or eliminate the need for VBA altogether.
Excel (the worksheets, the cells, the functions) is a very transparent and widely understood medium. VBA on the other hand is clunky, hidden away and neglected. VBA goes against the very notion that models should be easy to read. This is a common tenet of financial modelling standards, including ICAEW’s very own. It is only by placing numerical calculations in the Excel worksheets – and not hidden in VBA – that the user can easily understand the financial model. In Excel, the bonnet is always open and the engine available for all to see.
If we dig a little more about why people aspire to learn VBA they usually say that they want to do the fancy optimisation- such as sculpting debt - that often happens in project finance transactions, and which usually involves a bit of VBA.
Very often the optimisation process involves a “copy and paste” macro, which resolves the circularities that often occur in funding calculations. For instance, a debt size affects the commitment fee occurred, which affects the funding requirement, which affects the debt size. These types of macro are by far the most common in project finance models. However, they do not always require a macro.
In my work as a financial modeller I have seen many models where a copy and paste macro was used but where, with a bit of maths, a formulaic alternative could be implemented in Excel. What we are doing in many Excel forecast models are a series of linear equations – a lot of them, yes, but nothing inherently complicated. A bit of school level algebra such as simultaneous equations for instance can often resolve a circularity without VBA. It is sad for me – as a mathematician – to report that unfortunately few think back to that important life skill they were taught when they were 14 (maths!) and instead most reach for more technology – in this case VBA – as the answer.
Having said that, the reality is that circularities often have far too many elements to solve algebraically in a concise manner, so copy and paste macros are an efficient alternative, for example by creating a button to avoid having to manually repeat an action many times.
An attendee at a training course said to me the other day: “If you have to use VBA then Excel is the wrong solution.” I don’t totally agree. Copy and paste macros are useful. But I do concur entirely when it comes to numerical calculations, such as User Defined Functions. If you ever find yourself doing numerical calculations in VBA (perhaps you need the speed ;?) then Excel and VBA are probably not the answer. There are other programming languages that are faster and easier to maintain - use them.
At the risk of doing myself out of business, Excel is sometimes not the right answer!
- Intro to Financial Modelling - Part 19: Wrap-up
- Intro to Financial Modelling - Part 18: Sensitivities and Scenarios
- Intro to Financial Modelling - Part 17: Calculation Techniques - Loan Calculations
- Intro to Financial Modelling - Part 16: Calculation Techniques - Flags and Masks
- Intro to Financial Modelling - Part 15: Error reduction - Common pitfalls
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.