0

I am new with VBA and trying to do a trinomial option pricing tree with parameters from -n to n defined. How can I rewrite my VBA function with a double loop to provide all output as it seems to only show the value of S. In the end I want to sum all values and discount them but I cannot get past my double loop providing only one output. If I put the MsgBox between k and i loop it returns nothing. Please can someone assist getting this to work as I don't have the hang of VBA yet.

Public Function LoopTest(S As Double, u As Double, n As Integer)

    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    Dim St() As Double
    ReDim St(2 * n + 1)

    For k = 0 To n Step 1
        For i = -k To k Step 1
            St(i, k) = S * u ^ i
            Debug.Print St(i, k)
            MsgBox ("DebugToPrint")
            ' Payoff(i,k) = Application.WorksheetFunction.Max((St(i,k) - 20), 0)
        Next i
    Next k

    ' For i from n to 2 step -1
    ' For j from 1 to n - 2
    '     Payoff(i - 1, j) = Payoff(i, j) + Payoff(i, j + 1) + Payoff(i, j + 2)
    ' Next i
    ' Next k

    LoopTest = St()

End Function

How do I get output

k=0   St = S*u^0 
k=1   St = S*u^-1 , St = S*u^0 , St = S*u^1 
k=2   St = S*u^-2 , St = S*u^-1 , St = S*u^0 , St = S*u^1 , St = S*u^2

etc and then sum everything afterwards as output?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Emilene
  • 1
  • 1
    It looks like you're on the right track, but `ReDim St(2 * n + 1)` only gives you a 1 dimensional array. Shouldn't that be something more like `ReDim St(2 * n + 1, n)`? – Comintern Oct 07 '18 at 18:11
  • 2
    Should be `Redim St(-n To n, 0 to n)` – chris neilsen Oct 07 '18 at 18:29
  • Thank you so much Chris! This works well. Makes sense now but I didn't really understand ReDim before but this works and makes it clear to me now. I really appreciate this – Emilene Oct 08 '18 at 09:34

1 Answers1

0

I'm not totally following the example but from the description it sounds like you're interested in a recursive process. It can be tricky and will leave you in an endless loop if you're not careful. Does this previous post help you? VBA recursive "For loops" Permutation?

HeavyDNickell
  • 56
  • 4