Here is my code in VBA. I found each time the randomnum is same.

NumberOfTrials = 500
RiskAMP_BeginSteppedSimulation (NumberOfTrials)
On Error Resume Next

sigma = Sheets("After-Tax Planner and Summary").Cells(39, 4).Value
meanvalue = Sheets("After-Tax Planner and Summary").Cells(38, 4).Value

For j = 1 To NumberOfTrials

randomnum = RiskAMP_NormalValue(0.0467, 0.1) * te_ratio

RiskAMP_IterateSimulationStep
Next j

RiskAMP_FinishSteppedSimulation

Hi,

If all you want to do is generate samples, you don't need to use the stepped simulation method. You can call that function directly.

What specifically are you doing? Maybe we can suggest a better alternative.

Alex
alex@riskamp.com

Hi, Alex,

but why each iteration it return same random number from RiskAMP_NormalValue(0.0467, 0.1)?
I replaced it with excel RND funtion, and it returned different random number for each iteration.

Can be some thing related to 'seed' (of pseudo random number generator) or not?

No, actually the problem has to do with identifying the cell. During a simulation, (particularly a VBA simulation), we have to check whether we've already updated the value for a given cell - and if so, to retain the last value.

The reason for this is that VBA may cause the spreadsheet to recalculate and we can't update the values. For example, if you use Goalseek within a VBA simulation it may recalculate the spreadsheet many times to converge on a solution. While this is happening we have to ensure that the random values we generate are consistent.

To do that, we need to know what cell the value is in. When the function is called from VBA like this, we can't properly identify a cell and as a result the value never updates.

There are two ways to resolve this: either (1) don't use a VBA simulation (it's not needed in the above code), or (2) put the random function into a spreadsheet cell and get the value from there.

This is not ideal but it will solve the problem.

I hope that's helpful,

Alex
alex@riskamp.com