Creating Excel simulation in VBA

[Drews]

Greetings VBA experts!

I need to make a macro that will generate 1000 simulations for 60
periods (i.e. a total of 60 000 simulations). The simulation is based
on a historic price, a std.dev., and a mean. In Excel I would use the
following formula for period 1:

=$'historic price' + norminv(rand();mean;std.dev.)

.... and copy it to 1000 rows.

For period 2 to 60 I would use the following formula:

='price simulation n for period 1' +
norminv(rand();mean;std.dev.)

.... and copy it to 1000 rows for each period 2 to 60.

My motivation for making the vba macro instead of using Excel formula
is that I don't want the simulation to update unless I request it -
e.g. by running the macro.

Hopefully someone can kindly help me with the code as my VBA skills
are obviously very limited. Thank you!

 

[Guest]

Not sure you want to get into this - the general form could look like:

Dim darrMySimulationData() As Double
Dim i As Long, j As Long

ReDim darrMySimulationData(1 To 1000, 1 To 60)

j = 1
For i = LBound(darrMySimulationData) To UBound(darrMySimulationData)
darrMySimulationData(i, j) = 1 ' Your func here
Next i

For j = LBound(darrMySimulationData, 2) + 1 To UBound(darrMySimulationData, 2)
For i = LBound(darrMySimulationData) To UBound(darrMySimulationData)
darrMySimulationData(i, j) = darrMySimulationData(i, 1) ' Your
func here
Next i
Next j


However, as far as I know you cant use the rand function in VBA (but there
are several VBA implementations if you google for them).