Monte Carlo Macro - Optimized for Speed

[LactoseO.D.'d]

I am trying to create a Monte Carlo simulator from scratch with the computational speed of Crystal Ball or better. Currently, the solutions I've been able to come up with take something like 20 seconds to run a simple 500 trials by varying values within the a spreadsheet.

I am thinking that if I can write the macro without referencing cells until the very end, it will run faster. Is there a way to generate a list of randomized trial results given distribution types, means, and stdevs and then populate it to a range of cells at the very end? Would this speed things up? I am looking to get in to the 10-20k trials range and things must go faster. I have seen it done, I just don't know how

 

[dk]

You could do all of your calculations using arrays and then write the array at the end of the process. That would normally be faster than reading/writing to cells. Simple example:

Sub UsingArrays()

    Dim arrNums(99, 0) As Double
    Dim lIndex As Long

    For lIndex = LBound(arrNums) To UBound(arrNums)

        arrNums(lIndex, 0) = Rnd

    Next lIndex

    Range("A1:A100") = arrNums

End Sub

HTH
DK