Hi,
I was wondering how to add simulation fucntions when writing a vba code.
For example if I want to take the standard error of 20 trails how do I do it? What will the function look like. I took a look at the vba addin, but no simulation functions were in there.
To illustrate my problem better. An example is given from the goal seek code you posted a while ago. To differentiate, my questions are in CAPITALS


Private Sub CommandButton1_Click()

NumberOfTrials = 20
RiskAMP_BeginSteppedSimulation (NumberOfTrials)

For i = 1 To NumberOfTrials

' call goalseek here. I got this code by recording a
' macro, then just copying the macro into this function.

Range("D4").GoalSeek Goal:=0, ChangingCell:=Range("C4")

' advance the simulation

RiskAMP_IterateSimulationStep

Next i
WILL THE SIMULATION FUNCTION GO IN HERE? IF SO, WHAT WILL IT LOOK LIKE
RiskAMP_FinishSteppedSimulation

End Sub

Hi,

If you haven't seen it, have a look at our page on VBA, at [www.riskamp.com]. This explains how to run a simple simulation in VBA.

There are a couple of ways to do what you want. However let me first ask if you're trying to get an intermediate std.err. value, or if you want to just run 20 trials and then get it?

Cheers,

Alex Edwards
alex@riskamp.com

Yes I have seen it, but it really doesn't tell me how to use simulationfunctions. I agree I might be the missing the picture. I would like to get the standard deviation of a population after running a Monte Carlo simulation. I can do this using excel easily. Also the function to generate a random value looks like:
Function RiskAMP_NormalValue(0,1)
What will the function to get a standard deviation after running the simulation look like? What will be the function to get the mean of the simulation look like? At what step will it go in the code?
Thanks and God bless.

Hi,

I asked the question because you can get the standard deviation, mean, and so on, from VBA, but it requires a complicated syntax. This can be useful if you are trying to do it in the middle of a simulation - using the stepped simulation function, as above - but it's much easier if you only need the values at the end of a simulation.

To run a simulation from VBA, using the VBA library, you can do it in one line of code:

RiskAMP_RunSimulation( 1000 )

for 1000 iterations. To get values for standard error, standard deviation, and so on, I would suggest putting the functions into spreadsheet cells and fetching the results directly from those cells. So if you have a value in cell A1, you can put the standard error function in A2, like

=SimulationStandardError( A1 )

and then fetch the result from VBA with code like

Range( "A2" ).value

---

If you don't want to use the spreadsheet to do this, you can do it from VBA, but it 's more complicated. There are a few things you need to do:

First, you need to have at least one cell in the spreadsheet reference the cell you want to look at. For example, if you want to look at the value in cell A1, somewhere else in the spreadsheet you have to have something - maybe a mean function - like

=SimulationMean( A1 )

The reason is because we only store results for cells that are referenced by our statistics functions. We do this to save memory; otherwise we would have to store every result for every cell, which would require much more available memory. Once you do that, in order to refer to a cell specifically, you have to use the Excel INDIRECT method with the cell's address.

Finally, because these functions aren't in the VBA module, you have to use the full syntax. To put that all together, if you want to look at the standard error for cell A1, the function in VBA would look like

value = Application.ExecuteExcel4Macro("SimulationStandardError(INDIRECT(" & Chr(34) & "A1" & Chr(34) & "))")

The other functions work the same way; you would use SimulationStandardDeviation or SimulationMean to get the deviation and mean, respectively. I'm sorry this is so complicated. Please let me know if that helps.

Cheers,

Alex Edwards
alex@riskamp.com