Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2173

# Problem Running Excel Linest for Polynomial Fits Using VBA

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
0
jrpotts
• 2
1 Solution

Commented:
Hello jrpotts,

you could take a look at this recent thread there are some links in it that could help
http://www.experts-exchange.com/Applications/MS_Office/Q_20561341.html

HAGD:O)Bruintje
0

Author Commented:
Thanks For Responding HAGD:O)Bruintje,

I followed your suggestions and discovered that I had already reviewed many of the threads and links listed.  In fact the test data formula(2*X^3+3*X^2+(-6*X)+8)shown in my question was taken from one of those links.

I have not yet found any source of information that either explains or addresses the issue(s) associated with my vba problem.

jrpotts
0

Author Commented:
I re-read the recommended threads and links recommended by HAGD:O)Bruintje, and actually found the solution to my problem in one of them. Thanks very much HAGD:O)Bruintje for pointing me in the right direction!The problem was that I needed to format the array of independent variables (powers of x) as a zero-based 'array of arrays' (with single index), for the linest function to work correctly.

Sub polyfit(LeftCell As String, DPM1 As Integer, OM1 As Integer)
'least squares fit using Excel LINEST function
Dim i, j As Integer
Dim regorder, dptotal As Integer
Dim BSignif(), b() As Variant
Dim x(), y(), LinestOut(), sum As Variant
regorder = OM1 + 1
dptotal = DPM1 + 1
ReDim allx(DPM1)
ReDim ally(DPM1)
ReDim allxj(DPM1)
ReDim allxeq(OM1)
ReDim LinestOut(1 To 5, 1 To regorder + 1)
Range(LeftCell).Select

j = 0
For i = 1 To dptotal
allx(j) = i
ally(j) = ActiveCell(i, 1).Value
j = j + 1
Next i

'fill the 0 based subarrays for each exponent
For j = 1 To regorder
For i = 0 To DPM1
allxj(i) = allx(i) ^ j
Next i
'assign to the 0 based array of arrays
allxeq(j - 1) = allxj
Next j

ReDim b(0 To regorder)
ReDim BSignif(0 To regorder)
ReDim sigmab(0 To regorder)

LinestOut = Application.LinEst(ally, allxeq, 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

R2 = LinestOut(3, 1)
sigma = LinestOut(3, 2)
f = LinestOut(4, 1)
df = LinestOut(4, 2)
ssr = LinestOut(5, 1)
sse = LinestOut(5, 2)
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.