CODEWITHSUNDEEP
excelvbaexcel-formula
user8666372
user8666372

Reputation:

How can I get constant from formula at Excel VBA?

I added a trendline and get its linear equation value as y = ax + b form. In excel VBA how can I get constant a from this formula?

Upvotes: 1

Views: 322

Answers (2)

miroxlav
miroxlav

Reputation: 12194

In formulas

The following equations assume that your sheet has two named ranges: x and y. Then:

a = SLOPE(y,x)
b = INTERCEPT(y,x)

Source: Chart Trendline Formulas

Without named ranges, you use for example:

a = SLOPE(B2:B22, A2:A22)
b = INTERCEPT(B2:B22, A2:A22)

In VBA

rangeX = Range(Cells(2, 1), Cells(22, 1))) ' OR:  rangeX = Range("A2:A22")
rangeY = Range(Cells(2, 2), Cells(22, 2))) ' OR:  rangeY = Range("B2:B22")
a = Application.WorksheetFunction.Slope(rangeY, rangeX)
b = Application.WorksheetFunction.Intercept(rangeY, rangeX)

Upvotes: 2

jsotola
jsotola

Reputation: 2278

you can read the actual formula by turning on the label

then read the label itself and parse it

Sub readFormula()

    Dim ttt As Trendline
    Set ttt = ActiveChart.FullSeriesCollection(1).Trendlines(1)

    ttt.DisplayEquation = True

    Debug.Print ttt.DataLabel.Caption
    Debug.Print ttt.DataLabel.Formula
    Debug.Print ttt.DataLabel.FormulaLocal
    Debug.Print ttt.DataLabel.FormulaR1C1
    Debug.Print ttt.DataLabel.FormulaR1C1Local
    Debug.Print ttt.DataLabel.Text

    ttt.DisplayEquation = False
end sub

Upvotes: 2

Related Questions