How to use Trend function in VB macro to do polynomial curve fitting?

I would like to do a polynomial curve fitting to my data.  I found a Trend worksheetfunction can do the job.  However, I've never made it work in my VB macro.  I was passing two range values (range.value) into the function just like the example in MSDN.  The first range contains the y values, and the second range contains a nx3 matrix.  The first column of second range contains x values; second column contains x^2 values, and the third column contains x^3 values.  I expected to get the same result as I tried in cells.  However, I always got 'Type Mismatch' error back when executing this statement.  Could anyone tell me how to use this function in VB macro, if I want to do a polynomial fit with f(x^3)?  Or is there any other way to do curve fitting?  Thanks a lot.

Richard
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi CW43,

I've made two little examples which should explain how to use both TREND and LINEST function should be used in VBA.

For both examples, I suppose the y-values are on sheet1, column one, rows 1 to 11 (Range("\$A\$1:\$A\$11"), and the the X-values are on sheet 1, columns 2 to 4, lines 1 to 11 (of course X in column 2, X^2 in column 3 and X^3 in column 4)

First, the LINEST function, which gives the constants m, a, b and c for the polynomial trend curve
Y=c*X^3 + b*X^2 + c*X + m in an array (c,b,a,m). The parameters are returned on the sheet in column E, lines 1 to 4 respectively.

Sub PolyNomTest()
Dim Y, X, R
Y = Sheets("Sheet1").Range("\$A\$1:\$A\$11")
X = Sheets("Sheet1").Range("\$B\$1:\$D\$11")
R = WorksheetFunction.LinEst(Y, X)
Sheets("Sheet1").Range("\$E\$1")=R(1)
Sheets("Sheet1").Range("\$E\$2")=R(2)
Sheets("Sheet1").Range("\$E\$3")=R(3)
Sheets("Sheet1").Range("\$E\$4")=R(4)
End Sub

Second, the TREND function, pretty similar, where of course the X and Y arrays are built in the same way.
The S array contains the polynomial fit of 3rd degree for the X values in column B. It puts the result in column F on sheet 1.

Sub PolyNomTest2()
Dim Y, X, S
Y = Sheets("Sheet1").Range("\$A\$1:\$A\$11")
X = Sheets("Sheet1").Range("\$B\$1:\$D\$11")
S = WorksheetFunction.Trend(Y, X)
For i = 1 To 11
Sheets("sheet1").Range("\$F\$" & i) = S(i, 1)
Next i
End Sub

Hope this helps,

Calacuccia
0

Experts Exchange Solution brought to you by