Create Scatter Plot with Trendline with Excel VBA

I’ll break down the code and explain each step:

VBA Code to Create a Scatter Plot with a Trendline:

Sub CreateScatterPlotWithTrendline()
    ' Declare variables
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    Dim xRange As Range
    Dim yRange As Range
    ' Set the worksheet where your data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Modify to your sheet name
   ' Define the ranges for your X and Y data
    Set xRange = ws.Range("A2:A10") ' Modify to the range of your X data
    Set yRange = ws.Range("B2:B10") ' Modify to the range of your Y data
    ' Create a chart object
    Set chartObj = ws.ChartObjects.Add
    With chartObj
        ' Set the chart type to scatter plot
        .Chart.ChartType = xlXYScatterLines       
        ' Set the data for the chart
        .Chart.SetSourceData Source:=Union(xRange, yRange)       
        ' Add a trendline
        Dim trendline As Trendline
        Set trendline = .Chart.SeriesCollection(1).Trendlines.Add
        trendline.Type = xlLinear ' Set the trendline to linear      
        ' Optional: Customize the trendline appearance (e.g., color, thickness)
        trendline.Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red color
        trendline.Format.Line.Weight = 2 ' Thickness of the line       
        ' Optional: Customize chart title and axis titles
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = "Scatter Plot with Trendline"       
        .Chart.Axes(xlCategory, xlPrimary).HasTitle = True
        .Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Axis Title"       
        .Chart.Axes(xlValue, xlPrimary).HasTitle = True
        .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Y Axis Title"
    End With
End Sub

Detailed Explanation:

1. Declare Variables:
   • ws: This represents the worksheet where your data is located. Modify this to your actual sheet name.
   • chartObj: This variable holds the ChartObject that we will create.
   • dataRange: Holds the combined range for the data points (not directly used here but can be helpful in other cases).
   • xRange and yRange: These hold the actual data for the X and Y axes respectively.
2. Set the Worksheet and Data Ranges:
   • We use Set ws = ThisWorkbook.Sheets(« Sheet1 ») to specify the worksheet. You should modify « Sheet1 » to match the name of your worksheet.
   • xRange is set to ws.Range(« A2:A10 »), representing the X-axis values (adjust this to match your data range).
   • Similarly, yRange is set to ws.Range(« B2:B10 »), representing the Y-axis values.
3. Create Chart Object:
   • Set chartObj = ws.ChartObjects.Add creates a new chart object in the worksheet.
   • With chartObj initiates the chart formatting process.
4. Set the Chart Type and Data Source:
   • .Chart.ChartType = xlXYScatterLines sets the chart type to a scatter plot with lines connecting the points.
   • .Chart.SetSourceData Source:=Union(xRange, yRange) specifies the data source for the chart, combining both X and Y ranges.
5. Add and Customize Trendline:
   • Set trendline = .Chart.SeriesCollection(1).Trendlines.Add adds a trendline to the first series in the chart.
   • trendline.Type = xlLinear sets the trendline to a linear type (you can change this to xlExponential, xlLogarithmic, etc., depending on your needs).
   • trendline.Format.Line.ForeColor.RGB = RGB(255, 0, 0) changes the color of the trendline to red (you can adjust the RGB values for a different color).
   • trendline.Format.Line.Weight = 2 adjusts the thickness of the trendline.
6. Chart Customization:
   • .Chart.HasTitle = True ensures that the chart has a title.
   • .Chart.ChartTitle.Text = « Scatter Plot with Trendline » sets the chart’s title.
   • .Chart.Axes(xlCategory, xlPrimary).HasTitle = True adds a title to the X-axis, and .Chart.Axes(xlValue, xlPrimary).HasTitle = True adds a title to the Y-axis.
   • You can customize the axis titles by setting .AxisTitle.Text.

Customization Tips:

• Chart Type: You can change the chart type (e.g., xlXYScatterLinesNoMarkers, xlXYScatterSmooth, etc.) depending on how you want to present your scatter plot.
• Trendline Type: The trendline can be customized to be linear, exponential, polynomial, etc.
• Formatting: You can modify other properties such as the chart background color, gridlines, and more for visual enhancement.