如果需要OLS最佳拟合系数（即线性回归）而不是插值，则可以对二阶多项式执行以下操作：

Sub test()

    Dim X(0 To 9, 0 To 1) As Integer
    Dim Y(0 To 9, 0 To 0) As Integer

    i = 0
    For n = 0 To 9
       X(n, 0) = i
       X(n, 1) = i * i
       Y(n, 0) = i * i + 3 * i - 7
       i = i + 1
    Next

    B = WorksheetFunction.LinEst(Y, X)
    B2 = B(1)
    B1 = B(2)
    B0 = B(3)

End Sub

对于B0，它正确地返回-7;对于B1，它正确地返回3;对于B2，它正确地返回1。https://stackoverflow.com/a/27137957/1011724
你可以把它变得更一般，比如k阶多项式，如下所示：

Function f(X) As Integer ' This function is replaced by your data
    f = -2 * WorksheetFunction.Power(X, 3) + 3 * X - 7
End Function

Sub test2()

    Order = 3

    ReDim X(0 To 9, 0 To Order - 1)
    Dim Y(0 To 9, 0 To 0) As Integer

    'Note i is only to generate dummy data
    i = 1
    For n = 0 To 9 'Replace 9 with the length of your data
       X(n, 0) = i 'This line is replaced by your actual data
       ' Create the higher order features:
       For k = 1 To Order
           X(n, k-1) = Application.WorksheetFunction.Power(i, k)
       Next
       Y(n, 0) = f(i) 'This line is replaced by your actual data
       i = i + 1
    Next

    B = WorksheetFunction.LinEst(Y, X)

End Sub

我发现了另一种使用数组进行多项式回归的方法。代码在这个链接中。https://rosettacode.org/wiki/Polynomial_regression。因为网站上的代码由于数组不正确而无法工作，我做了一个小的更正。现在它可以工作了。

Function polynomial_regression(y As Variant, x As Variant, degree As Integer) As Variant
    Dim a() As Double
    ReDim a(1 To UBound(x), 1 To degree)
    For i = 1 To UBound(x)
        For j = 1 To degree
            a(i, j) = x(i) ^ j
        Next j
    Next i
    polynomial_regression = WorksheetFunction.LinEst(WorksheetFunction.Transpose(y), a, True, True)

End Function

我在上面的函数中添加了一个函数，它使用范围变量作为输入，相当于Excel函数linest（）

Public Function RangeToArray(ByRef myRange As Variant) As Variant

    Dim individualCell As Range
    Dim i As Integer
    
    ReDim myArray(1 To myRange.Count)
    i = 1
    For Each individualCell In myRange
        myArray(i) = individualCell.Value     
        i = i + 1
    Next
    RangeToArray = myArray
    
End Function

Function PolyFit(yRange As Variant, xRange As Variant, degree As Integer) As Variant
    Dim xAry() As Variant
    Dim yAry() As Variant
    Dim a() As Double
    
    xAry() = RangeToArray(xRange)
    yAry() = RangeToArray(yRange)
    
    ReDim a(1 To (UBound(xAry)), 1 To (degree))
    For i = 1 To (UBound(xAry))
        For j = 1 To (degree)
            a(i, j) = xAry(i) ^ (j)
        Next j
    Next i
    PolyFit = WorksheetFunction.LinEst(WorksheetFunction.Transpose(yAry), a, True, False)
    
End Function