Converting a polynomial LINEST formula to VBA

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.

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.

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.

removed duplicate post