title: Monte Carlo simulation in VBA/Excel
body: <p>I am trying to create a «general/standardized» macro for Monte Carlo simulation in VBA/Excel. I have been able to write a rather efficient code which works well in simple cases where the entire calculation/formula is written in one cell (with inputs from other cells). However, I am struggling to find a way to write an efficient code for more complex calculations. </p>
  
  <p>Whenever I work with simulation in VBA/Excel, I always try to do all of the calculations in VBA and only get input/display output in Excel – in order to increase the speed of the simulations as much as possible. Unfortunately, for “complex” calculations (read: multiple calculations ultimately leading to one output), e.g. a discounted cash flow analysis where the output is the sum of the present value of projected cash flows for several years going forward and the cash flow for each year is based on several assumptions, it is quite time consuming and almost undoable to write all of the calculations in VBA. The only way to perform Monte Carlo simulations in such models that I have been able to think of is to write a loop where I change the desired input variable to a random draw from the appropriate distribution and recalculate the workbook for each run through the loop and store the result from each run in an output sheet.  Here is a very rough example code: </p>
  
  <pre><code>Sub MCDCF()
  
  Dim i As Long, iterations As Long
  Dim target As Range, variable As Range
  
  With Application
                  .ScreenUpdating = False
                  .EnableEvents = False
                  .Calculation = xlCalculationManual
  End With
  
  iterations = Cells(5, 17).Value
  
  Set target = Cells(28, 3)
  Set variable = Cells(47, 3)
  
  For i = 1 To iterations
      variable.Value = Application.WorksheetFunction.NormInv(Rnd(), Cells(7, 17).Value, Cells(8, 17).Value)
      ActiveSheet.Calculate
      Cells(5 + i, 19).Value = target.Value
  Next
  
  With Application
                  .ScreenUpdating = True
                  .EnableEvents = True
                  .Calculation = xlCalculationAutomatic
  End With
  
  End Sub
  </code></pre>
  
  <p>The above approach is way too slow. Does anyone have any suggestions to an alternative approach?</p>
  
  <p>Additional information: What I mean by “general/standardized” macro is that I will create a userform with RefEdit inputs for output cell, cell containing variable to be simulated and number of iterations – with the goal that the macro can be applied to several different Excel models. </p>
  
  <p>And yes, I know that VBA/Excel is far from the best tool when it comes to simulations. However, it is the only alternative for the people that will be using this. </p>