How to create a Trendline Chart using Excel VBA

Excel VBA - Trendline Charts

Here are some snippets useful to create a TrendLine Chart in Excel

Have used the entire data from the given sheet to create the chart. Have used the UsedRange function to get that.

If you want to have a specified range you can pass that also

Sub Create_TrendLine_Chart_Excel_2003(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String, ByRef oSource As Range)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)
   
    oCht.Chart.SetSourceData oSource, PlotBy:=xlColumns
    oCht.Chart.ChartType = xlLineMarkers
   
    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle
   
    oCht.Chart.Legend.Position = xlLegendPositionRight
   
    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
   
    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1
   
    oCht.Chart.Axes(xlCategory).TickLabelSpacing = 1
    oCht.Chart.Axes(xlCategory).TickLabels.Font.Size = 8
   
    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
    If Not oCht Is Nothing Then Set oCht = Nothing
    If Not oChts Is Nothing Then Set oChts = Nothing

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then  'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

For some reason the above was creating a problem in Excel 2007 and above. Hence created a separate snippet for it

Sub Create_TrendLine_Chart_Excel_2007(ByRef oRep As Worksheet, ByVal iLeft As Double, ByVal iTop As Double, ByVal sChartTitle As String)
Dim oChts As ChartObjects           '* Chart Object Collection
Dim oCht As ChartObject             '* Chart Object
On Error GoTo Err_Chart
    Set oChts = oRep.ChartObjects
    Set oCht = oChts.Add(iLeft, iTop, 400, 450)
   
    oCht.Chart.ChartWizard Source:=oRep.UsedRange
    oCht.Chart.ChartType = xlLineMarkers
   
    oCht.Chart.HasTitle = True
    oCht.Chart.ChartTitle.Text = sChartTitle
   
    oCht.Chart.Legend.Position = xlLegendPositionRight
   
   
    oCht.Chart.HasAxis(XlAxisType.xlCategory) = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).HasTitle = True
    oCht.Chart.Axes(XlAxisType.xlCategory, xlPrimary).AxisTitle.Characters.Text = ""
   
    oCht.Chart.HasAxis(XlAxisType.xlValue) = True
    oCht.Chart.Axes(XlAxisType.xlValue, xlPrimary).HasTitle = True
    oCht.Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percentage Done" '.Axes(Type:=XlAxisType.xlValue).AxisTitle.Text = "% Done"
    oCht.Chart.Axes(xlValue).MaximumScale = 1
    'oCht.Chart.SetElement (msoElementPrimaryCategoryGridLinesMajor)

Err_Chart:
If Err <> 0 Then
   Debug.Assert Err = 0
   Debug.Print Err.Description
   If Err.Number = 94 Then  'Invalid Use of Null Error
        Err.Clear
        Resume Next
   Else
        Err.Clear
        Resume Next
   End If
End If

End Sub

ChartType = xlLineMarkers makes this Chart a TrendLine. You can try your luck by selecting other types