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)
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
jrpottsAuthor 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)
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