Programming Excel with VBA and .NET

Add Trendlines

You can add trendlines to most types of 2-D charts. 3-D, pie, doughnut, and radar chart types don’t permit trendlines. Trendlines apply to a single series, so you add them using the Series object’s Trendlines property. For example, the following code adds a trendline to the first series in a chart:

Sub AddTrendline( )
    Dim chrt As Chart, tl As Trendline, tls As Trendlines
    ' Get the chart.
    Set chrt = ActiveChart
    chrt.ChartType = xlLine
    ' Get a series.
    Set tls = chrt.SeriesCollection(1).Trendlines
    Set tl = tls.Add(xlLogarithmic, , , 10, , , True, True, "Trend1") End Sub

Tip

I use variables with explicit types in the preceding code to enable Auto Complete for the Trendlines collection and Trendline object. The expression chrt.SeriesCollection(1).Trendlines doesn’t provide member lists or other Auto Complete features.

If a chart has a legend, Excel automatically adds a legend entry for each trendline using the trendline’s Name property as the caption. Excel automatically generates that name if you don’t provide it as part of the Add method.

You can use the Forward and Backward properties to project a trendline beyond the series in either direction. Excel automatically scales the axis to accommodate the new range. Projecting a trendline in this way is a sort of forecasting.