Class Trendlines (Excel VBA)
A collection of all the Trendline objects for the specified series. To use a Trendlines class variable it first needs to be instantiated, for example
Dim trns as Trendlines
Set trns = ActiveChart.FullSeriesCollection(1).Trendlines()
For Each
Here is an example of processing the Trendlines items in a collection.
Dim trnln As Trendline
For Each trnln In ActiveChart.FullSeriesCollection(1).Trendlines()
Next trnln
Add
Creates a new trendline.
Add (Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)
ActiveWorkbook.Charts("Chart1").SeriesCollection(1).Trendlines.Add
Arguments
Optional arguments
The following arguments are optional
Type (XlTrendlineType) - The trendline type.
Possible values are
| xlExponential | Uses an equation to calculate the least squares fit through points, for example, y=ab^x . |
| xlLinear | Uses the linear equation y = mx + b to calculate the least squares fit through points. |
| xlLogarithmic | Uses the equation y = c ln x + b to calculate the least squares fit through points. |
| xlMovingAvg | Uses a sequence of averages computed from parts of the data series. The number of points equals the total number of points in the series less the number specified for the period. |
| xlPolynomial | Uses an equation to calculate the least squares fit through points, for example, y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g. |
| xlPower | Uses an equation to calculate the least squares fit through points, for example, y = ax^b. |
Order (XlTrendlineType) - if Type is xlPolynomial. The trendline order. Must be an integer from 2 to 6, inclusive.
Possible values are
| xlExponential | Uses an equation to calculate the least squares fit through points, for example, y=ab^x . |
| xlLinear | Uses the linear equation y = mx + b to calculate the least squares fit through points. |
| xlLogarithmic | Uses the equation y = c ln x + b to calculate the least squares fit through points. |
| xlMovingAvg | Uses a sequence of averages computed from parts of the data series. The number of points equals the total number of points in the series less the number specified for the period. |
| xlPolynomial | Uses an equation to calculate the least squares fit through points, for example, y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g. |
| xlPower | Uses an equation to calculate the least squares fit through points, for example, y = ax^b. |
Period (XlTrendlineType) - if Type is xlMovingAvg. The trendline period. Must be an integer greater than 1 and less than the number of data points in the series you are adding a trendline to.
Possible values are
| xlExponential | Uses an equation to calculate the least squares fit through points, for example, y=ab^x . |
| xlLinear | Uses the linear equation y = mx + b to calculate the least squares fit through points. |
| xlLogarithmic | Uses the equation y = c ln x + b to calculate the least squares fit through points. |
| xlMovingAvg | Uses a sequence of averages computed from parts of the data series. The number of points equals the total number of points in the series less the number specified for the period. |
| xlPolynomial | Uses an equation to calculate the least squares fit through points, for example, y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g. |
| xlPower | Uses an equation to calculate the least squares fit through points, for example, y = ax^b. |
Forward - The number of periods (or units on a scatter chart) that the trendline extends forward
Backward - The number of periods (or units on a scatter chart) that the trendline extends backward
Intercept (Trendline) - The trendline intercept. If this argument is omitted, the intercept is automatically set by the regression.
DisplayEquation (Boolean) - True to display the equation of the trendline on the chart (in the same data label as the R-squared value). The default value is False.
DisplayRSquared (Boolean) - True to display the R-squared value of the trendline on the chart (in the same data label as the equation). The default value is False.
Name (String) - The name of the trendline as text. If this argument is omitted, Microsoft Excel generates a name.
Count
Returns a Long value that represents the number of objects in the collection.
Dim lngCount As Long
lngCount = ActiveChart.FullSeriesCollection(1).Trendlines.Count
Item
Returns a single Trendline object from the collection.
Item (Index)
Index: The index number for the object.
Dim trnln As Trendline
Set trnln = ActiveChart.FullSeriesCollection(1).Trendlines(Index:=1)