Help Required for a simple VB code.

[Suz1984]

Hi,

I am very new to visual basic and am using the code to create a monte carlo simulation. I have created a function, but do not know how to make the function repeat itself a specific number of times (which will be inputted by the user), and how to make these iterations output themselves onto the spreadsheet. The code i have created is:

Option Explicit

Public Function gBmProcess(FTSEinitial As Double, RiskFreeRate As Double, Dividend As Double, _
Volatility As Double, TimeLength As Double) As Double

'This function will allow the simulation of FTSE100 values.

Application.Volatile (True)

gBmProcess = FTSEinitial * Exp((RiskFreeRate - Dividend - 0.5 * (Volatility * Volatility)) _
* TimeLength + Volatility * Sqr(TimeLength) * StdNorm())

End Function


Public Function StdNorm() As Double

'Uses the Box-Muller method to generate normally distributed random variables


Application.Volatile (True)

Static s As Boolean
Static w As Double

Dim X1 As Double
Dim X2 As Double

'Box-Muller Algorithm.

If s Then
s = False
StdNorm = w
Else
s = True
Do
X1 = 2 * Rnd() - 1
X2 = 2 * Rnd() - 1
w = X1 ^ 2 + X2 ^ 2
Loop Until w <= 1


w = Sqr(-2 * Log(w) / w)
StdNorm = X1 * w
w = X2 * w

End If

End Function


Please help me find a way to repeat this function a number of times specified within, e.g. cell C10, and output the solutions into cells, e.g.d45 - d45000. Many thanks

 

[mhartman1]

Hello
A For, Next statement in your code would work

Public Function StdNorm() As Double
Dim Index as integer
For Index = 1 to 25
'Do something here 25 times
Next Index
'
End Function

Regards
Mark

 

[Bong]

I'm not sure what your function is outputting. You have: StdNorm = X1 * w, which returns StdNorm from your function. But then you have: w = X2 * w, which doesn't make sense to me because you're basically done.

What is it you want to iterate? If it's the entire function, that's one thing. If it's inside the function, say,

If s Then
  s = False
  StdNorm = w
Else
  s = True
  Do
    X1 = 2 * Rnd() - 1
    X2 = 2 * Rnd() - 1
    w = X1 ^ 2 + X2 ^ 2
  Loop Until w <= 1
  w = Sqr(-2 * Log(w) / w)
  StdNorm = X1 * w
  w = X2 * w
End If

, then that's another.

Anyway, to answer your specific question, let's say the user puts a number in c10. You retrieve it by explicit reference:
numItrns = cells("c10").value, or,
numItrns = cells(10,3).value,
or alternatively, you could query the user with a dialog:
numItrns = InputBox("Number of iterations", "[red]<window title>[/red]", 25), where 25 is the default.

Then, as mhartman1 says, you can use the For...Next construct but instead of explicitly looping, you use the variable, numItrns:
For Index = 1 to numItrns.

To output to the cells, you do it the same way:
cells("d45").value=StdNorm, or whatever you call the "answer". I would use a row number variable, r say, that is incremented inside the loop:
r=r+Index, and output: cells(r,4).value=StdNorm.

_________________
Bob Rashkin

 

[Bong]

that should be: r=r0+Index

_________________
Bob Rashkin

 

[PHV]

But then you have: w = X2 * w, which doesn't make sense to me because you're basically done
Bong, w is a Static variable ...

 

[Bong]

OK. I guess that means the iteration is on complete calls to the function, StdNorm?

_________________
Bob Rashkin

 

[Suz1984]

Thank you for your help. The function I actually want repeated is the gBmProcess function. You see, this function will allow the user to create various FTSE values. But i am unsure in the code, how to incorporate a repetition function, which allows the user to input into cell C10 a value, then the macro will ouput the specified number of FTSE values onto the worksheet.
The StdNorm function is an input of the gBm process. Please advise how I would write out the code.

Many thanks for your help.

 

[Bong]

The gBmProcess function outputs (naturally) gBmProcess. Is this what you're calling "FTSE values"?

You say you want to get the number of iterations from cell, c10. Where do the arguments of gBmProcess come from (FTSEinitial As Double, RiskFreeRate As Double, Dividend As Double,Volatility As Double, TimeLength As Double)?

_________________
Bob Rashkin