LinEst for polynomial regression in VBA - error...

Hello everyone,

I am trying to use the LinEst function in VBA to get the best fit coefficients for some data fitted by a polynomial curve. I can get this for a single variable, but not multi-variable.

My code is:

Sub IWishThisSubWorked()


Dim LinestArray(1 To 250, 1 To 4) As Double
Dim DependentVariable(1 To 250) As Variant

' some code inputting the dependent variable data into the DependentVariable array


For i = 1 To 250
    LinestArray(i, 1) = i
    LinestArray(i, 2) = i ^ 2
    LinestArray(i, 3) = i ^ 3
    LinestArray(i, 4) = i ^ 4
Next i

coefficient1 = WorksheetFunction.Index(WorksheetFunction.LinEst(DependentVariable, LinestArray, 0), 1, 1)

This approach seems to work when I do it via array formulas directly in Excel, but for some reason it is not working in VBA.

The error message is: "Run-time error '1004': Unable to get the LinEst property of the WorksheetFunction class"

(p.s. this is not just because 250^4 is a large number: I have tried this with a shorter set of arrays, but the issue does not go away...)


Thanks in advance for any help or suggestions. I've tried all of Google and am completely stuck.