visual basic - i need help

Question

Hello! I'm really a beginner in vba and programming.

I have to write a macro that simulates a binomial distribution where the price of something can only go up or down with the probability of p that is 50% this time.

My variables:

S=100 (the basic value)

u=1,1 (how much the value goes up in each experiment if it goes up)

d=1/u (how much the value goes down in each experiment if it goes down)

p=0.5 (probability)

n=400 (number of experiments)

I did not declarated these variables, because i want the macro to read these values from specific cells.

My code (for the first step):

Sub BINOM()

  S = Range("L4").Value
  u = Range("M4").Value
  d = Range("N4").Value
  p = Range("O4").Value
  n = Range("P4").Value
  v = Rnd()

  If v > p Then
    Range("B2").Value = S * u
  Else
    Range("B2").Value = S * d
  End If

End Sub

The result of the second experiment (that should be written in the B3 cell) has to be calculated from the result of the first experiment and so on but not with using the same random number...

I hope someone helps me because i don't really know how to start with.

Answer 1

I'll try my best but I just removed Excel in favor of Calc which doesn't support the same type of language as far as I can tell.

Sub BINOM()
  Dim intCounter, v

  S = Range("L4").Value
  u = Range("M4").Value
  d = Range("N4").Value
  p = Range("O4").Value
  n = Range("P4").Value

  Range("B1").Value = s    

  For intCounter = 2 to n

    '//If this creates an error then just remove it.
    '//It should keep the same random number from appearing over and over.
    Randomize

    '//Create new Random number in v
    v = Rnd()

    If v > p Then
      Range("B" & intCounter).Value = Range("B" & (intCounter - 1)).Value * u
    Else
      Range("B" & intCounter).Value = Range("B" & (intCounter - 1)).Value * d
    End If

  Next intCounter
End Sub

Let me know if that works out for you or if any errors appear. Updated to base each cell from data given of previous cell.