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.

Public Sub BuildYieldRegressionFunction(bondDataArray As Variant, outputRangeAddress As String)

    Dim regressionVariables As Variant
    ReDim xValues(1 To UBound(bondDataArray, 2)) As Variant
    ReDim yValues(1 To UBound(bondDataArray, 2)) As Variant
    Dim coefficents As Variant
    Dim iCnt As Integer
    Dim rowCnt As Integer
    Dim colCnt As Integer
    
    xValues = StripSingleDimensionArray(bondDataArray, COL_ID_TIME_UNTIL_MATURITY)
    yValues = StripSingleDimensionArray(bondDataArray, COL_ID_YTM_TRACE_250K)
    
    ActiveWorkbook.Names.Add Name:="xValues", RefersTo:=Range(Cells(1000, 50), Cells(1000 + UBound(xValues) - 1, 50))
    For iCnt = 1 To UBound(xValues)
        Range("xValues").Cells(iCnt) = xValues(iCnt)
    Next
    
    ActiveWorkbook.Names.Add Name:="yValues", RefersTo:=Range(Cells(1000, 51), Cells(1000 + UBound(yValues) - 1, 51))
    For iCnt = 1 To UBound(yValues)
        Range("yValues").Cells(iCnt) = yValues(iCnt)
    Next
        
    coefficents = Evaluate("LINEST(" & Range("xValues").Address & "," & Range("yValues").Address & "^{1,2},TRUE, TRUE)")
    
    Range("xValues").Clear
    Range("yValues").Clear
    
    ActiveWorkbook.Names.Item("xValues").Delete
    ActiveWorkbook.Names.Item("yValues").Delete
    
    Range(outputRangeAddress).ClearContents
    
    For rowCnt = 1 To UBound(coefficents, 1)
        For colCnt = 1 To UBound(coefficents, 2)
            Range(outputRangeAddress).Cells(rowCnt, colCnt) = coefficents(rowCnt, colCnt)
        Next
    Next

End Sub

removed duplicate post