Speeding up simulations with arrays

[John Lynam]

Hi all,

I was wondering if anyone can help me.

I have a workbook that does some relatively simple financial modelling with one stochastic variable - annual equity returns (the model assumes they are normally distributed).

There are three outputs I want to capture for each simulation - values in cells H2, H3 and H4.

I currently run a Monte Carlo simulation on the model and copy the outputs onto a new page, one below the other (they get transposed when copied).

At the moment my code refreshes the calculation, then simply copies and pastes values and then repeats the process. It is a simple for i = 1 to 10,000 loop. Screen updating is off etc. It works fine but I would like to speed it up.

Here is my question. Is it more efficient to collect the outputs (H2, H3, H4 - perhaps transposed if necessary but I don't know how to do that in an array) in a VBA array for the 10,000 simulations and then spit them out onto the new sheet, or is what I am doing currently just as fast. I am guessing arrays can speed things up.

If anyone has any code lying around that could capture three pieces of data, over and over, in an array and then spit it out in three columns on a new sheet that would be great.

Any help would be much appreciated.

Cheers

 

[John Lynam]

Me again,

Any array experts out there?

Cheers

 

[ScottR]

Generally speaking, accessing the document less often will probably yield speed improvements although probably not massively unless your sheet recalculates a lot during the generation process and is otherwise complex. Only one way to find out for sure though...

 

[joelcourtheyn]

Below I give an example of a subroutine in which a matrix with 1000 rows and 3 rows is created.
Next, its contents is 'dumped' in a specified range.
I think this can inspire you to work out a solution for your application.
Succes !

Sub test()
Dim j As Integer
Dim D(1000, 3) As Single
For j = 1 To 1000
D(j, 1) = j: D(j, 2) = j ^ 2: D(j, 3) = j ^ 3
Next j
Worksheets(1).Range("A1:C1000") = D
End Sub