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:

Please Login or Register  to view this content.

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.

Still unsolved

This post was originally fixing a typo in my explanation that I now realise I could edit directly - so have done that instead now.

Originally Posted by IDidNotWantToRegiste

My code is:

Please Login or Register  to view this content.

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

That is just VBA's way of indicating, in this context, that LinEst returned an error.

The key to the error is in the help page for LinEst, to wit:

Please Login or Register  to view this content.

With the declaration DependentVariable(1 To 250), "known_y" is treated as a single row.

Therefore, the declaration for "known_x" must be LinestArray(1 to 4, 1 To 250), changing references in the For-loop accordingly.

Alternatively and perhaps easier, change "known_y" to DependentVariable(1 To 250, 1 to 1), changing references in the elided code accordingly.

But you might have another programming mistake.

Unless the x-axis values are 1 to 250, the values assigned to LinestArray in the For-loop should be:

Please Login or Register  to view this content.

where the array "x" constains the actual x-axis values, which must be input.

PS: You can avoid the VBA runtime error by using Application.Index and Application.LinEst (deprecated, I believe) and assigning the result to a type Variant variable; or better: by inserting the statement On Error Resume Next before the statement that uses WorksheetFunction.Index. In either case, you must handle the error condition.