CODEWITHSUNDEEP
vba
Bach Pham
Bach Pham

Reputation: 69

"Out of memory" error for very large tridimensional array

I am trying to populate and then perform calculations based on a rather large array. This is to populate a binomial tree to calculate option price, so after populating the array I would need to perform repeated calculation so I'd prefer having 3 dimensions in my array for easy reference further down in the code. First dimension is the number of periods that have passed, second dimension is the number of price increases and the third is the number of price decreases. 

Dim arr() As Double
Dim periods As Integer
Dim p As Long, i As Long
Dim u As Double, d As Double
Dim iniprice As Double

Let periods = 400
Let iniprice = 100
Let u = 1.1
Let d = 0.9

ReDim arr(0 To periods, 0 To periods, 0 To periods)

Let arr(0, 0, 0) = iniprice

For p = 1 To UBound(arr, 1)
  For i = 0 To p
    arr(p, i, p - i) = WorksheetFunction.RoundDown(arr(0, 0, 0) * u ^ i * d ^ (p - i), 2)
  Next i
Next p

Is this a limitation stemming from the amount of RAM available on my PC (currently having 8Gb) or is this a limitation of VBA itself? Since one period is usually one day, a periods value of 1000 is normal (252 days = 1 trading year).

I also noticed that I have a lot of unused values, because I want to populate only values that have this format arr(p, i, p-i), values such as arr(10,10,10) will be 0. I'd greatly appreciate a workaround to this.

Upvotes: 0

Views: 69

Answers (1)

Kubie
Kubie

Reputation: 1571

You can try using a single lookup column to represent the 3D array (not the best way but helps with the memory errors):

Dim periods As Integer
Dim p As Long, i As Long
Dim u As Double, d As Double
Dim iniprice As Double
Dim d1 As Long, d2 As Long, d3 As Long, someRow As Long
Dim fndRange As Range

Let periods = 400
Let iniprice = 100
Let u = 1.1
Let d = 0.9

' Col A will be your lookup in format "X-X-X", Col B will hold values
With Sheets("some sheet")
    .Range("B1") = iniprice
    For d1 = 0 To periods
        For d2 = 0 To periods
            For d3 = 0 To periods
                .Range("A" & someRow).Value = d1 & "-" & d2 & "-" & d3
                someRow = someRow + 1
            Next
        Next
    Next
End With

For p = 1 To periods
    For i = 0 To p
        Set fndRange = Sheets("some sheet").Columns(1).Find(p & "-" & i & "-" & (p-1))
        fndRange.Offset(0,1).Value = WorksheetFunction.RoundDown(iniprice * u ^ i * d ^ (p - i), 2)
    Next i
Next p

Upvotes: 1

Related Questions