Having trouble using LINEST in Excel 2007 VBA

[Chef Scottie]

I'm trying to run regressions in VBA using the code

ols = Application.WorksheetFunction.LinEst(Y, x, True, True)

As I understand it the second TRUE generates the stats for the regression,
but I only get the coefficients. (It shows NA for the stats).

Help?!

 

[Peter T]

In this usage of Linest I get the same in Excel and VBA, eg

Sub test2()
Dim i As Long
Dim Y(1 To 5) As Double
Dim x(1 To 5) As Double
Dim ols

For i = 1 To 5
Y(i) = 2 * (i ^ 2) + (3 * i) + 4
x(i) = i
Next

ols = Application.WorksheetFunction.LinEst(Y, x, True, True)
Range("G1:H5") = ols

Range("A1:A5") = Application.Transpose(x)
Range("B1:B5") = Application.Transpose(Y)
Range("D1:E5").FormulaArray = "=LINEST($B$1:$B$5,$A$1:$A$5,TRUE,TRUE)"

End Sub

Regards,
Peter T