Third-Party Research & Development
Technical Discourse

A VBA Program For Least Squares Polynomial Fits: Using Excel Linest Function

John R. Potts, Senior Scientist/Engineering Manager

BRIEF

Microsoft's Visual Basic for Applications (VBA) is not quite as straightforward and easy to use as Microsoft would have you believe. The Visual Basic Editor is provided free with Microsoft Office tools and accessible with the keystroke ALT+F11. Learning the basics of VBA doesn't take too long, especially if you selectively read an excellent reference like Excel 2002 Power Programming with VBA by John Walkenbach (ISBN: 0-7645-4799-2). Problems arise when you try more advanced things like making an Excel Add-in, which is a program attached to Excel to get more functionality, or try to run some of the more useful and powerful Excel Functions like LINEST. This paper shows explicit VBA code, a program called Polyfit2, which I developed for my projects, that does Least Squares Polynomial Fits for Excel Data, using the Excel LINEST function. Feel free to use the code as you see fit (no pun intended)! I don't assume any liability for its use (read the disclaimer attached).

I think VBA and VB are well worthwhile learning, however, feel obligated to mention a serious shortcoming: a general lack of code security for VBA. Microsoft needs to rethink and revise its approach to VBA code security because the single password protection offered as a means of protecting VBA code with Add-Ins is absolutely NO PROTECTION AT ALL. Make up any 32 mixed character password and it will be broken in less than a second with a $45 commercial "password recovery tool". For reasonable VBA code protection, one has to spend at least $500 and a considerable amount of time learning how to make compiled VBA COM Add-ins.

Figure 1 shows some data, generated with the polynomial formula shown on the spreadsheet, which was used to test Polyfit2. Notice that the coefficients from the least squares polynomial fit closely agree with those of the polynomial used to generate the data..

Figure 1. Results of Running Polyfit2.

Please feel free to email me any questions or comments that you may have regarding program operation or design.

THE PROGRAM

Sub polyfit2 (Startcell As String, DPM1 As Integer, OM1 As Integer)
'Using VBA to Access Excel LINEST function For Least Squares Polynomial Fits
'Subroutine Developed by John R.Potts, Third-Party Research & Development
'THE USER IS ADVISED TO TEST THIS VBA PROGRAM THOROUGHLY BEFORE RELYING ON IT.
'THE USER MAY FREELY USE THIS PROGRAM, HOWEVER, MUST ASSUME THE ENTIRE USAGE RISK.
'JOHN R.POTTS AND THIRD-PARTY RESEARCH AND DEVELOPMENT ASSUME NO LIABILITY FOR ITS APPLICATION.
'X and Y data couples are assumed to be row aligned in adjacent columns on an Excel Spreadsheet.
'Startcell is the cell which contains the first value in the X column...e.g."A1"
'DPM1 is the number of data pairs minus 1
'OM1 is the Fit Polynomial Order minus 1
'The program may be run in the Immediate Window of the Microsoft Visual Basic Editor.
'An example of the statement syntax to fit 20 data pairs with a polynomial of order 3 is CALL POLYFIT2("A1",19,2)
'The Polynomial Coefficients and Fit Statistics are Printed in An Immediate Window For Viewing
'Consult Help on Excel Linest Function for Further Information Regarding Its Features
Dim i As Integer, j As Integer
Dim regorder As Integer, dptotal As Long
Dim BSignif() As Variant
Dim x() As Variant, y() As Variant, LinestOut() As Variant, 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(Startcell).Select
j = 0
For i = 1 To dptotal
allx(j) = ActiveCell(i, 1).Value
ally(j) = ActiveCell(i, 2).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)

Debug.Print "R2", R2
Debug.Print "sigma", sigma
Debug.Print "f", f
Debug.Print "df", df
Debug.Print "ssr", ssr
Debug.Print "sse", sse

End Sub