Solved

# Problem Running Excel Linest for Polynomial Fits Using VBA

Posted on 2003-03-26

The Problem Stated:

The problem is that my simple VBA program for Least Squares fitting polynomials to data(see below), which uses EXCEL LINEST, doesn't work for polynomial orders higher than three, with over a thousand raw data values generated using such formulae such as 2*X^3+3*X^2+(-6*X)+8. The X values are integers which run from 1 to the maximum number of data values desired. The VBA program generates a run-time error 13 (type mismatch), for polynomial orders higher than three.

The Excel LINEST worksheetfunction works fine when run manually (shift+control+enter)on this data set, for polynomial orders 3 and higher, however, generates a run-time error 13 (type mismatch) when run from the VBA program.

Sub polyfit(StartCell As String, DPM1 As Integer, OM1 As Integer)

'least squares fit using Excel LINEST function

'StartCell refers to cell with data generated using X=1

'DPM1 (Data Points Minus 1)

'OM1 (Order Minus 1)

Dim i, j As Integer

Dim regorder, dptotal As Integer

Dim BSignif(), b() As Variant

Dim x(), y(), LinestOut()As Variant

regorder = OM1 + 1

dptotal = DPM1 + 1

ReDim x(1 To dptotal, 1 To regorder)

ReDim y(1 To dptotal, 0 To 0)

ReDim LinestOut(1 To 5, 1 To regorder + 1)

Range(StartCell).Select

For i = 1 To dptotal

For j = 1 To regorder

x(i, j) = i ^ j

Next j

y(i, 0) = ActiveCell(i, 1).Value

Next i

ReDim b(0 To regorder)

ReDim BSignif(0 To regorder)

ReDim sigmab(0 To regorder)

LinestOut = Application.LinEst(y(), x(), True, True)

For i = 0 To regorder

b(regorder - i) = LinestOut(1, i + 1)

BSignif(regorder - i) = LinestOut(2, i + 1)

Debug.Print "b(" & regorder - i & ")", b(regorder - i)

Debug.Print "bsignif(" & regorder - i & ")", BSignif(regorder - i)

Next

End Sub

I would be most grateful for any help/advice/guidance that anyone might be willing to offer.

Thanks

jrpotts