Creating Excel simulation in VBA
 

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!

Creating Excel simulation in VBA
 

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).