Project finance modelling skills in other sectors
I should say before you read this that I am not a professional programmer, I never wanted to be one and to write good VBA you do not need to be one!
Poor VBA (Visual Basic for Applications) is a great way of confusing all users of a financial model and many auditors I have worked with actually carve it out from the scope, or certainly charge additional time for reviewing even simple print macros. Its not rocket science so lets here are a five ways VBA script writing can be improved.
Consider it a high level Best Practice Methodology for VBA for financiers.
1. Write down what you are trying to achieve before opening VBA
This sounds obvious but like good financial models, good VBA comes from having a true understanding of
- What you are trying to achieve
- How you are going to go about it
…before doing it. VBA is a tool you use to arrive at a solution, it is not the solution in itself.
2. Annotate your code
As a rule of thumb I say 1 line of annotation for each 1-5 lines of text. Do this for two reasons
- So a user can understand what is intended to happen when the code is run
- So that you as a developer can debug quicker and when you come to use the code again it will jog your memory.
So by putting an apostrophe in front of the text you will see that it ignored when it is run (and turns green)
3. Indent code according to hierarchy
If code is executed within say a Do While loop then consider indenting it. This makes it easier to read and work out what is being run within the loop rather than just before or just after.
4. Use range names not absolute cell references
Unlike Excel, when a cell, row, column or range is moved to a different location, VBA does not keep track of what that affects. The result of this is that if you had a reference to Sheet1!A3 and you cut it to Sheet1!A4 then the VBA would still be looking at A3… the way around this is to use Range Names which it does keep track of. The name of a range as defined in Excel can be used directly in VBA.
A classic example of this is when using a copy and paste routine, if the ranges to be copied and pasted to are not named (say, Interest_Copy and Interest_Paste) then if a line is inserted in the code above then the macro will over write what ever is in the ‘new’ lines.
5. Keep code in appropriately named modules
Group macros within modules in the same way that you group calculations into worksheets which you then name for ease of reference. For example
- Debt Sizing
- Print Macros
- IRR optimisation
- Tax solver
6. Do not over complicate the script
It is easy to record a macro…too easy in fact. The result is often a long winded, unbroken, page of code. If you are going to keep the code, work through it applying rules 1 to 4 above so that it as the bare minimum does what is needed and can be understood. However have a think about how a routine can be made more elegant. For example if a recorded macro performs a set routine 3 different times maybe it could be set up to be one routine within a loop that runs for i=zero to X where in this instance X=3 but could be user defined in an input cell.
Remember ….. “lines are free but complexity is very expensive..”
Summary
My closing words on this matter are do not use VBA unless you need it, even for circular interest problems its rarely truly needed. Follow the guidelines above which are independent of the problem you are trying to solve and you will see a dramatic improvement in the efficiency of your VBA. Remember when measuring modelling efficiency, your time as a developer, is a tiny proportion of the overal ‘efficiency’, think of the number of other people who will have to get their head around what you have written. Write the code with purpose and ensure it can be understood.
