Converting a polynomial LINEST formula to VBA

[AARONWEBSTER]

I have successfully implemented the appropriate =LINEST() formula for a polynomial regression on my spreadsheet, but when I try to implement the same calculation in VBA I can't figure out the correct syntax. Any help would be appreciated.

Formula on Sheet: =LINEST(M45:M59,J45:J59^{1,2},TRUE, TRUE)

Code in VBA: regressionVariables = WorksheetFunction.LinEst(yValues, xValues ^ Array(1, 2), True, True)

Arrays are included for type reference and this is not the order of code
ReDim xValues(1 To UBound(bondDataArray, 2)) As Variant
ReDim yValues(1 To UBound(bondDataArray, 2)) As Variant

Keep in mind that yValues and xValues are single dimension arrays and the error is a type mismatch error related to the LinEst function. I just can't find a good example of the correct syntax for a polynomial implementation, where straight line examples are all over.

Thanks.

[MrShorty]

VBA does not know how to handle this syntax the way Excel does xValues ^ Array(1, 2)
In Excel this syntax creates a two dimensional array where known_x(i,1)=xvalues(i) and known_x(i,2)=xvalues(i)^2. To make this work in VBA, you first must create the complete known_x matrix from the xvalues vector. This should be easy enough using a For Next or For Each Next loop.

In building the two dimensional matrix that will be known_x, you will probably run into some problem with known_y. I can't remember which way it goes, but a one dimensional array is either horizontal or vertical, and I can't remember which it is. When you build the 2D known_x array, if it's "orientation" is not the same as the known_y array, I believe it will give you an error or just crash. I usually dimension known_y with 2 indices (Dim yvalues(n,1) or yvalues(1,n)) depending on which way I orient known_x to make it unambiguous.

[AARONWEBSTER]

Thanks for the response...

I have noticed many examples using the WorsheetFunction.Transpose() function which looks to correct the issue you are referring to, but none of the examples actual compile for me. I keep getting a type mismatch error.

I chose to just build the formula as a string and pass my it through to the evaluate function in Excel and go on down the road. Maybe someone else will find the correct implementation, but I will post my workaround for those out there in the same situation.

Please Login or Register  to view this content.

[AARONWEBSTER]

removed duplicate post

[pmjef]

I have made a vba function ussing linest and handles emty cells in y range (sorry for a little danish in the function):

Function FitPoly(Range_y As Range, Range_x As Range, gr As Integer, Optional arg3 As Boolean = True, Optional arg4 As Boolean = False) As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim x() As Variant
Dim y() As Variant

Dim First_row As Integer
Dim Last_row As Integer
Dim Antal_Rows As Integer
Dim Antal_Blank_y As Integer

First_row = Range_x.Row
Last_row = First_row + Range_x.Rows.Count - 1
Antal_Rows = Last_row - First_row + 1

j = 0
For i = 1 To Antal_Rows
If ((Not IsEmpty(Range_y.Cells(i, 1))) And (Not IsEmpty(Range_x.Cells(i, 1)))) Then
j = j + 1
End If
Next i

ReDim x(1 To j)
ReDim y(1 To j)

Dim pj() As Variant
ReDim pj(1 To gr)

For k = 1 To gr
j = 0
For i = 1 To Antal_Rows
If ((Not IsEmpty(Range_y.Cells(i, 1))) And (Not IsEmpty(Range_x.Cells(i, 1)))) Then
j = j + 1
If k = 1 Then
y(j) = Range_y.Cells(i, 1)
End If
x(j) = Range_x.Cells(i, 1) ^ k
End If
Next i
pj(k) = x
Next k

Dim z() As Variant

z = WorksheetFunction.LinEst(y, pj, arg3, arg4)
FitPoly = z

End Function

[FDibbins]

I have made a vba function ussing linest and handles emty cells in y range (sorry for a little danish in the function):

.....

1. You may not have noticed but this thread is over 12 years old. Thanks for the input though.

2. please use code tags when posting code (see my foot note)