Coding a Scatter plot with smooth lines in VBA

Question

I am trying to code for a Scatter Plot using smooth lines with VBA. I am trying to take data off of a worksheet and create a scatter plot with lines and no markers in the same workbook different sheet using VBA.

This is part snapshot of my worksheet

The values below 247 and between 263 to 455 in column A will have corresponding -1.75 in column B.

The x values are in range A1:A401

y-values are in range B1:B401

Also I want to have title to my graph and X and Y axis labelled. I am having trouble figuring how to get the y-values to plot with the x-values instead of excel making two seperate lines on the chart.

This is graph I need

This is the code I have used

Set xData = ThisWorkbook.Worksheets(2).Range("A1:A" & LastRow_this)
Set yData = ThisWorkbook.Worksheets(2).Range("B1:B" & LastRow_this)

Set GraphRange = Union(xData, yData)

'Create a chart
  Set cht = ThisWorkbook.Worksheets(1).Shapes.AddChart2

'Give chart some data
  cht.Chart.SetSourceData Source:=GraphRange

'Determine the chart type
  cht.Chart.ChartType = xlXYScatterLines

This is what it gives me in Excel.

How can I get the desired result ?

Also what can I do if the range is dynamic ?

Answer 1

You may try something like this...

Sub CreateChart()
Dim wsData As Worksheet, wsChart As Worksheet
Dim LastRow As Long
Dim xData As Range, yData As Range, GraphRange As Range
Dim cht As Shape

Application.ScreenUpdating = False

Set wsChart = Sheets(1)
Set wsData = Sheets(2)
LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
Set xData = ThisWorkbook.Worksheets(2).Range("A1:A" & LastRow)
Set yData = ThisWorkbook.Worksheets(2).Range("B1:B" & LastRow)

Set GraphRange = Union(xData, yData)

'Create a chart
Set cht = ThisWorkbook.Worksheets(1).Shapes.AddChart2(, xlXYScatterLinesNoMarkers)

'Give chart some data
cht.Chart.SetSourceData Source:=GraphRange
cht.Chart.FullSeriesCollection(1).Format.Line.Weight = 5
Application.ScreenUpdating = True
End Sub

Answer 2

With a scatter chart you don't want to use "GraphRange" for the whole chart. Depending on the data and the phase of the moon, Excel will try to plot the X and the Y data as individual series, which is not what you want.

Instead, edit or insert each series separately and set the range for the X and the Y values. Also you need xlXYScatterSmoothNoMarkers as the chart type.

Try using the macro recorder to select the range, add a scatter chart with smoothed lines. Then inspect the code. This will give you valuable pointers about the changes you need to make to your code.

Answer 3

My code is

Sub setChart()
Dim LastRow_this As Long
Dim Ws As Worksheet, chtWs As Worksheet
Dim xData As Range, yData As Range
Dim Cht As Chart

    Set Ws = ThisWorkbook.Worksheets(2)
    Set chtWs = ThisWorkbook.Worksheets(1)
    With Ws
        LastRow_this = .Range("a" & Rows.Count).End(xlUp).Row
        Set xData = .Range("A1:A" & LastRow_this)
        Set yData = .Range("B1:B" & LastRow_this)
    End With

    Set Cht = chtWs.Shapes.AddChart.Chart

    With Cht
        .ChartType = xlXYScatterLinesNoMarkers
        .HasLegend = False
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .XValues = xData
            .Values = yData
        End With
        .Axes(xlCategory).MajorUnit = 50
        .Axes(xlCategory).HasMajorGridlines = True
        .Axes(xlValue).HasMajorGridlines = True
        .Axes(xlCategory).MaximumScale = 460
        .Axes(xlCategory).MinimumScale = 50
    End With

End Sub