?
Solved

Problem Running Excel Linest for Polynomial Fits Using VBA

Posted on 2003-03-26
3
Medium Priority
?
2,110 Views
Last Modified: 2007-12-19
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
Comment
Question by:jrpotts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 300 total points
ID: 8211779
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 Comment

by:jrpotts
ID: 8212113
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 Comment

by:jrpotts
ID: 8232806
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question