Convert Excel Trendline to LINEST

trendline.bas

Sub ExtractTrendline()
    Dim outRng As Range
    Dim useB As String
    Dim poly As String
    Dim poly2 As String
    useB = "TRUE"
    poly = "{1"
        If TypeName(Selection) = "Trendline" Then
            If Not Selection.Type = xlMovingAvg Then
                Set outRng = Application.InputBox(Prompt:="Select uppler-left of location where coeffients are to be stored (2-6 cols, 7 rows):", _
                    Title:="Destination...", _
                    Type:=8)
                exes = Split(Selection.Parent.Formula, ",")(1)
                whys = Split(Selection.Parent.Formula, ",")(2)
                If Not Selection.InterceptIsAuto Then
                    B = Selection.Intercept
                    If B = 0 Then useB = "FALSE" Else MsgBox "LINEST does not support manual y-intercepts"
                End If
                Select Case Selection.Type
                    Case xlExponential
                        outRng.Resize(5, 2).FormulaArray = "=LINEST(LN(" & whys & ")," & exes & "," & useB & ",TRUE)"
                        outRng.Offset(0, 2).Resize(1, 1).Formula = "=Exp(" & outRng.Resize(1, 2).Cells(2).Address & ")"
                        outRng.Offset(6, 1).Cells(1).FormulaR1C1 = "=EXP(" & outRng.Offset(0, 1).Cells(1).Address(1, 1, xlR1C1) & ")*EXP(" & outRng.Cells(1).Address(1, 1, xlR1C1) & "*RC[-1])"
                    Case xlLinear
                        outRng.Resize(5, 2).FormulaArray = "=LINEST(" & whys & "," & exes & "," & useB & ",TRUE)"
                        outRng.Offset(6, 1).Cells(1).FormulaR1C1 = "=" & outRng.Cells(1).Address(1, 1, xlR1C1) & "*RC[-1]+" & outRng.Offset(0, 1).Cells(1).Address(1, 1, xlR1C1)
                    Case xlLogarithmic
                        outRng.Resize(5, 2).FormulaArray = "=LINEST(" & whys & ",LN(" & exes & ")," & useB & ",TRUE)"
                        outRng.Offset(6, 1).Cells(1).FormulaR1C1 = "=" & outRng.Cells(1).Address(1, 1, xlR1C1) & "*LOG(RC[-1])+" & outRng.Offset(0, 1).Cells(1).Address(1, 1, xlR1C1)
                    Case xlPolynomial
                        odr = Selection.Order
                        poly2 = "{" & odr
                        For x = 2 To odr
                            poly = poly & "," & x
                            poly2 = poly2 & "," & odr - x + 1
                        Next x
                        poly = poly & "}"
                        poly2 = poly2 & ",0}"
                        outRng.Resize(5, odr + 1).FormulaArray = "=LINEST(" & whys & "," & exes & "^" & poly & "," & useB & ",TRUE)"
                        outRng.Offset(6, 1).Cells(1).FormulaR1C1 = "=SUMPRODUCT(" & outRng.Resize(1, odr + 1).Address(1, 1, xlR1C1) & ",POWER(RC[-1]," & poly2 & "))"
                    Case xlPower
                        outRng.Resize(5, 2).FormulaArray = "=LINEST(LN(" & whys & "),LN(" & exes & ")," & useB & ",TRUE)"
                        outRng.Offset(0, 2).Resize(1, 1).Formula = "=Exp(" & outRng.Resize(1, 2).Cells(2).Address & ")"
                        outRng.Offset(6, 1).Cells(1).FormulaR1C1 = "=EXP(" & outRng.Offset(0, 1).Cells(1).Address(1, 1, xlR1C1) & ")*RC[-1]^" & outRng.Cells(1).Address(1, 1, xlR1C1)
                End Select
                outRng.Offset(5, 0).Cells(1) = "X"
                outRng.Offset(5, 1).Cells(1) = "Y"
                outRng.Offset(6, 0).Cells(1) = (WorksheetFunction.Min(Range(exes)) + WorksheetFunction.Max(Range(exes))) / 2
                outRng.Cells(1).AddComment "This row: Function coefficients"
                outRng.Offset(1, 0).Cells(1).AddComment "This row: Standard error for each coefficient"
                outRng.Offset(2, 0).Cells(1).AddComment "R² - The coefficient of determination. Compares estimated and actual y-values," & _
                    "and ranges in value from 0 to 1. If it is 1, there is a perfect correlation in the sample — there is no difference" & _
                    "between the estimated y-value and the actual y-value. At the other extreme, if the coefficient of determination is 0," & _
                    "the regression equation is not helpful in predicting a y-value."
                outRng.Offset(2, 1).Cells(1).AddComment "sey - The standard error for the y estimate."
                outRng.Offset(3, 0).Cells(1).AddComment "F - The F statistic, or the F-observed value. Use the F statistic to determine whether the" & _
                    "observed relationship between the dependent and independent variables occurs by chance."
                outRng.Offset(3, 1).Cells(1).AddComment "df - The degrees of freedom. Use the degrees of freedom to help you find F-critical values " & _
                    "in a statistical table. Compare the values you find in the table to the F statistic returned by LINEST to determine a confidence " & _
                    "level for the model."
                outRng.Offset(4, 0).Cells(1).AddComment "ssreg - The regression sum of squares"
                outRng.Offset(4, 1).Cells(1).AddComment "ssrid - The residual sum of squares."
                outRng.Offset(6, 1).Cells(1).AddComment "Sample application of regression equation"
            Else: MsgBox ("The LINEST function does not output a moving average function")
            End If
        Else: MsgBox ("You must have a trendline selected to use this button")
        End If
End Sub