range to vba array and use in formula

[dave322]

Hello,
i am currently using HoadleyOptions addin but i am struggling to use on of his formulas in vba, don't know if anyone has experience with this but the setup is as follows;
i have 252 daily prices in column A
in cell D1 the result of the following formula should be displayed;
=HoadleyGARCH("V";A1:$A$252;;252;;2)
if i use the formula in excell all works fine but i cant get it to work in vba
hoadley gives following advice with the formula: "Important note: If this function is being called from a VBA module then you must define (or use redim) the number of items in the array to be exactly equal to the number of prices. This principle applies to dividends as well."
right now i have in vba:

Sub Garch()
Dim i As Integer
Dim MyArray()
MyArray = Range("Garch").Value
'check!
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
Range("D1") = HoadleyGarch("V", MyArray, , 252, , 2)
Next i
End Sub

when i run it i get compile error argument not optional.

If anyone has any ideas i would be happy to hear them
thanks

 

[JoeMo]

I'm unfamiliar with this, so just a thought. MyArray as you are using it is a 2-D array. Perhaps Garch wants only a 1-D array, in which case try something like this:

Sub Garch()
Dim j as integer
Dim MyArray()
redim MyArray(1 to Range("Garch").Rows.Count)
For j= 1 to Range("Garch").Rows.Count
MyArray(j) = Range("Garch").Rows(j).value
next j
'check!
Range("D1").Resize( UBound(MyArray)-LBound(MyArray)+1) = HoadleyGarch("V", MyArray, , 252, , 2)
End Sub

Alternatively, perhaps all that's needed is to change:

For i = LBound(MyArray, 1) To UBound(MyArray, 1)
Range("D1") = HoadleyGarch("V", MyArray, , 252, , 2)
Next i

to this

Range("D1").Resize( UBound(MyArray)-LBound(MyArray)+1) = HoadleyGarch("V", MyArray, , 252, , 2)