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.

Who is Participating?
calacucciaConnect With a Mentor 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)
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,

All Courses

From novice to tech pro — start learning today.