Creating a Project with a Trendline with Excel VBA

Quite often, it is desirable to see certain trends in the data presented on a chart. In such cases, you can add a trendline, which makes it possible to forecast values.

If you want to add a trendline using Microsoft Excel’s built-in tools, activate the chart, go to the Layout contextual tab on the ribbon, and in the Analysis group click the Trendline drop-down list, then choose the desired type of trendline. Remember that a trendline is always constructed for the selected data series; therefore, if the chart contains multiple series, you need to specify for which series the trendline should be built. Alternatively, you can select the desired series directly on the chart and, from the context menu, choose Add Trendline.

From the VBA perspective, all trendlines corresponding to a given data series form the Trendlines collection, whose elements are Trendline objects. The Trendlines collection has only two methods:

• Add — adds a new element to the collection,
• Item — returns a specific element from the collection.

The Trendline object has the same properties as the parameters of the Add method (see Microsoft VBA Help).

Description of the Add method of the Trendlines collection

Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

• Type — sets the type of trendline. Valid values:
  • xlLinear (linear),
  • xlLogarithmic (logarithmic),
  • xlExponential (exponential),
  • xlPolynomial (polynomial),
  • xlMovingAvg (moving average),
  • xlPower (power).
• Order — sets the order of the polynomial trendline (valid integers 2 to 6; used only if Type = xlPolynomial).
• Period — trend period (valid integers 1 to the number of data points; used only if Type = xlMovingAvg).
• Forward — number of points forward (future) to forecast.
• Backward — number of points backward (past) to forecast.
• Intercept — intercept on the y-axis.
• DisplayEquation — Boolean, whether to display the trendline equation on the chart.
• DisplayRSquared — Boolean, whether to display the R² (coefficient of determination) value.
• Name — string specifying the name of the trendline.

Example: Trendline in the Computer Club Project

In our last project, we will add a group of CheckBox controls that allow the user to manage the display of the trendline on the chart (Fig. 6.10; see also file 7-Building a Trendline.xlsm on the CD).

On the Vedomost worksheet, add three checkboxes and, in the Properties window, set their properties as shown in Table.

Table. Values of properties set in the Properties window

Open the previous project related to computer clubs and make the following additions. In the Vedomost worksheet module, add the code for the three Click event procedures of the checkboxes:

• The Trendline checkbox builds or removes the trendline. If the trendline is removed, the Equation and R-squared checkboxes are disabled.
• The Equation checkbox controls whether the equation of the trendline is displayed.
• The R-squared checkbox controls whether the coefficient of determination is displayed.

Trendline. Vedomost worksheet module

Private Sub Trend_Click()
    Dim c As Chart
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    ActiveSheet.ChartObjects(1).Activate
    Set c = ActiveChart
    If Trend.Value Then
        Equation.Enabled = True
        Coef.Enabled = True
        c.SeriesCollection(1).Trendlines.Add _
            Type:=xlLinear, Forward:=0, Backward:=0, _
            DisplayEquation:=False, DisplayRSquared:=False
        If Trend.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        End If
    Else
        c.SeriesCollection(1).Trendlines(1).Delete
        Equation.Enabled = False
        Coef.Enabled = False
    End If
End Sub

Private Sub Equation_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Equation.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
    End If
End Sub

Private Sub Coef_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = False
        End If
    End If
End Sub

In the ThisWorkbook module, add the following procedure to set the initial states of the checkboxes . Naturally, the call to InitTrend must also be added to the Workbook_Open procedure in the same module.

Trendline. ThisWorkbook module

Private Sub InitTrend()
    With Worksheets("Vedomost")
        .Trend.Value = False
        .Equation.Value = False
        .Coef.Value = False
    End With
End Sub