Overview of techniques to accelerate Excel Models and custom applications.... I have included a few fun user defined functions as examples.
Currently I am working on a tool that dynamically generate a stochastic (simulation version) project plan in Excel from an MS Project file. In the initial version of the model builder, it could take up to 20 minutes to run the script. Often, there are techniques that we can use to make our models run faster under VBA or during simulations with tools like Crystal Ball or ModelRisk. Using the techniques covered below, it now takes less than 30 seconds to run!
Vose ModelRisk was actually designed with this performance concern in mind. They have a few unique features that enable your Excel models to run faster… which is a big concern when a model become complex. Some of these techniques are not unique to ModelRisk and can be re-used… they are good modeling practice. I will also cover some VBA trick to make your stochastic models run faster.
When modeling in Excel, remember to:
- Use formulas and function libraries: these are readily available and will greatly improve model performance. They will also increase functionality resulting in cleaner and less cluttered spreadsheets models.
- Avoid Lookups: When possible use formulas to calculate values rather than look them up in a table.
- Arrays: Arrays enable fast calculations in series/lists of values and can be called on easily in VBA
- Range Names: Using rage anames instead of cell references makes build, auditing modelas easier as well as simplifies VBA coding considerably.
- Turn-Off Recalc when doing formatting in Excel or VBA in large models because each time something changes…the model recalcs…very tedious
- Use User Defined Functions: Packaging calculations into functions also improves performance considerably… here are some simple examples that I find useful. To use them, just open the VBA editor in Excel. Insert a module using the right-click button. Select the module and paste the code below:
' Business Functions
Function TP_Saving(Value1 As Double, Value2 As Double) As Double
TP_Saving = Value1 - Value2
End Function
Function TP_Margin(Cost As Double, SellPrice As Double) As Double
TP_Margin = (SellPrice - Cost) / SellPrice
End Function
Function TP_Markup(Cost As Double, SellPrice As Double) As Double
TP_Markup = (SellPrice - Cost) / Cost
End Function
The results are presented in the table below.
If you have any questions or comments on how to accelerate models and applications with Crystal Ball VBA, please drop me a line at [email protected]