如何将LinEst函数应用于行？

多年来，我一直在使用WorksheetFunction.LinEst进行二次回归而没有问题。我的数据始终存储在Excel工作表的列中。

现在，我正在按行而不是按列发送数据。我对WorksheetFunction.LinEst的调用失败。
如果我在工作表中处理与公式相同的命令，则它会起作用。

我没有选择转置数据的选项。我正在使用Windows 10和Microsoft Office 365的最新发行版本。

我在这里找不到用VBA编写的将数据存储在行中的任何示例。

这是我用来执行回归的子例程的干净副本。我删除了所有调试代码，以使其更具可读性。
完整版本则更进一步。
在此代码之后，我编写了一些代码来演示失败。

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit cooeficients of the the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
    
    rgCoeff = Application.WorksheetFunction.LinEst(Ys, Application.Power(Xs, Array(1, 2)))
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)
End Sub

接下来的代码创建一个简单的数据集，以计算y = x ^ 2函数的系数。使用相同的数据（先存储在列中，然后存储在行中），我的代码可以处理列中的数据，但不能处理行中的数据。

Sub TestGetPolynomialRegressionCoefficients()
    Dim rXs As Excel.Range  ' Range for the X values
    Dim rYs As Excel.Range  ' Range for the Y values
    Dim ws As Excel.Worksheet
    Dim iRow As Long
    Dim iCol As Long
    Dim x As Long
    Dim x1 As Double
    Dim x2 As Double
    Dim x3 As Double
    
    Set ws = ThisWorkbook.Worksheets("LinEstTest")
    '
    ' Works! - Test data y = x^2 with data in columns
    '
    ws.Cells.Clear
    For x = 0 To 9
        iRow = x + 1
        ws.Cells(iRow, 1) = x         ' these will be the domain (the Xs)
        ws.Cells(iRow, 2) = x * x     ' these will be the range (the Ys)
    Next x
    
    Set rXs = ws.Range(ws.Cells(1, 1), ws.Cells(10, 1))
    Set rYs = ws.Range(ws.Cells(1, 2), ws.Cells(10, 2))
    
    On Error Resume Next
    x1 = -1: x2 = -1: x3 = -1
    GetPolynomialRegressionCoefficients rXs, rYs, x1, x2, x3
    If Err <> 0 Then
        Debug.Print "Error using Columns "; Err; " "; Err.Description
    Else
        Debug.Print "With data in columns, x1 = "; x1; ", x2 = "; x2; ", x3 = "; x3
    End If
    '
    ' Fails! - Test data y = x^2 with data in rows
    '
    ws.Cells.Clear
    For x = 0 To 9
        iCol = x + 1
        ws.Cells(1, iCol) = x         ' these will be the domain (the Xs)
        ws.Cells(2, iCol) = x * x     ' these will be the range (the Ys)
    Next x
    
    Set rXs = ws.Range(ws.Cells(1, 1), ws.Cells(1, 10))
    Set rYs = ws.Range(ws.Cells(2, 1), ws.Cells(2, 10))
    
    On Error Resume Next
    x1 = -1: x2 = -1: x3 = -1
    GetPolynomialRegressionCoefficients rXs, rYs, x1, x2, x3
    '
    ' Get Error message dialog:
    '
    ' Microsoft Visual Basic
    ' Run-time error '1004':
    ' Unable to get the LinEst property of the WorksheetFunction class
    '
    If Err <> 0 Then
        Debug.Print "Error Using Rows "; Err; " "; Err.Description
    Else
        Debug.Print "With data in rows, x1 = "; x1; ", x2 = "; x2; ", x3 = "; x3
    End If
End Sub

这是我运行测试代码时在即时窗口中得到的输出：

With data in columns, x1 =  1 , x2 =  0 , x3 =  0 
Error Using Rows  1004  Unable to get the LinEst property of the WorksheetFunction class

最后，这是我的例程的完整版，其中包含调试和验证代码。仅供参考（请不要批评）：

Sub GetPolynomialRegressionCoefficients(Xs As Excel.Range, Ys As Excel.Range, ByRef x1 As Double, ByRef x2 As Double, ByRef x3 As Double)
    '
    ' Calculates the best fit cooeficients of the the data stored in ranges Xs and Ys
    '
    Dim rgCoeff  ' This will be a variant array of the coefficients calculated for the best fit quadratic curve
#If RELEASE = 0 Then
    Dim iRow As Long  ' Used only for debugging purposes.
    Dim iCol As Long  ' Used only for debugging purposes.
    '
    ' Confirm that the ranges are the same size.
    '
    If (Xs.Rows.Count <> Ys.Rows.Count) And (Xs.Columns.Count <> Ys.Columns.Count) Then Stop
    '
    ' Confirm that all the data in the ranges is numeric and not blank
    '
    For iRow = 1 To Ys.Rows.Count
        For iCol = 1 To Xs.Columns.Count
            If IsNumeric(Xs.Cells(iRow, iCol)) = False Or IsNumeric(Ys.Cells(iRow, iCol)) = False Or Trim(Xs.Cells(iRow, iCol)) = "" Or Trim(Ys.Cells(iRow, iCol)) = "" Then Stop
        Next iCol
    Next iRow
            
    DoEvents
#End If
    
    rgCoeff = Application.WorksheetFunction.LinEst(Ys, Application.Power(Xs, Array(1, 2)))
    
    x1 = rgCoeff(1)
    x2 = rgCoeff(2)
    x3 = rgCoeff(3)

End Sub