多项式函数的 LinEst vba

我正在尝试使用 vba 计算多项式回归。首先，我尝试了 y=x^2+b：

OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,2),True,True)

其中 A 和 C 是数组 和OUTPUT很好。我会读r2 from OUTPUT using Application.Index(OUTPUT,3)

然而当我想尝试的时候y=x+x^2+b通过将 Array 添加到 Array 的参数：

OUTPUT = WorksheetFunction.Application.LinEst (A,Application.Power(C,Array(1,2)),True,True)

我无法读取r2 from OUTPUT using Application.Index(OUTPUT,3)

有什么解决办法吗？我究竟做错了什么？

解决方案： R_SQUARE = Application.Index(WorksheetFunction.LinEst(yVal, Application.Power(xVal, Application.Transponse(Array(1, 2))), True, True), 3,1)

尝试以下..

Sub LinEst()
Dim yVal As Range, xVal As Range
Set yVal = Range("C5:C14")
Set xVal = Range("B5:B14")

'You tried following formula which gives incorrect results for polynomial order 2
Range("B17") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, 2), True, True), 3)

'For linear
Range("B18") = Application.Index(WorksheetFunction.LinEst(yVal, _
                xVal, True, True), 3)
'For polynomial order 2
Range("B19") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2)), True, True), 3)
'For polynomial order 3
Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

End Sub

EDIT

我尝试过=INDEX(LINEST({3,2,5,7,4,2,1,-2,-5,-1},{0,1,2,3,4,5,6,7,8,9},TRUE,TRUE),3)在工作表中。但在 VBA 中，我无法将值作为双精度数组分配给数组。但是，当在下面的评论中尝试 @Domenic 的建议而没有数据类型时，它起作用了。

后续作品。

Sub LinEst()
'Dim xVal(1 To 10) As Double, yVal(1 To 10) As Double 'This fails

xVal = Application.Transpose(Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9))
yVal = Application.Transpose(Array(3, 2, 5, 7, 4, 2, 1, -2, -5, -1))

'For polynomial order 3
Range("B20") = Application.Index(WorksheetFunction.LinEst(yVal, _
                Application.Power(xVal, Array(1, 2, 3)), True, True), 3)

End Sub