Formula Array VBA

[irisqueiroz]

Hi,

I'm tryng to write a code that uses the outputs of a UDF that's a Formula Array.

Function GARCHparams(rets, startParams)
GARCHparams = NelderMead("GARCHMLE", rets, startParams)
End Function

When I record the macro, the code is:

Range("O1:O4").Select
Selection.FormulaArray = _
        "=GARCHparams(R1C8:R200C8 , R2C12:R4C12)"

That is, the output is in the 4 cells range ("O1:O4"). How can I get this 4 outputs in the VBA code? For example, x = output1; y=output2; w=output3 and z=output4 ... without needing to use the sheet to calculate it.

Thanks!

 

[Hui]

Irisqueiroz

You may need to add another parameter to the function
eg: Function GARCHparams(rets, startParams, Return_Parameter)

Can you post a sample file with an example of the formula and function and what you want to extract

 

[Luke M]

Here's an example array function and the calling macro.

Function myTest(x As Integer)
Dim y() As Integer
For i = 1 To x
    ReDim Preserve y(1 To i)
    y(i) = i
Next i
myTest = y()
End Function
 
Sub CallingMacro()
x = myTest(4)
 
'x now contains 4 array values
 
For i = LBound(x) To UBound(x)
    Debug.Print x(i)
Next
 
End Sub

For you code, you should be able to do something like

x = GARCHparams(Range("O1:O4"),Range("O1:O4"))

 

[irisqueiroz]

Irisqueiroz

You may need to add another parameter to the function
eg: Function GARCHparams(rets, startParams, Return_Parameter)

Can you post a sample file with an example of the formula and function and what you want to extract

Hui, that´s the worksheet. The output is in range M13:M16.

Thanks!

 

[irisqueiroz]

Here's an example array function and the calling macro.

Function myTest(x As Integer)
Dim y() As Integer
For i = 1 To x
    ReDim Preserve y(1 To i)
    y(i) = i
Next i
myTest = y()
End Function
 
Sub CallingMacro()
x = myTest(4)
 
'x now contains 4 array values
 
For i = LBound(x) To UBound(x)
    Debug.Print x(i)
Next
 
End Sub

For you code, you should be able to do something like

x = GARCHparams(Range("O1:O4"),Range("O1:O4"))

Thanks, Luke!