VBA create xy scatter plot using multiple data series

Question

I am trying to create an xy scatter plot on its own worksheet using VBA. I have two sets of data I'd like to use. When I run the code shown below for the first time (i.e. when there is no "Power Chart" present), it plots the data correctly. However, when I rerun the code with a "Power Chart" present, I get 3 additional series showing up, two with blank data and one with only y values, corresponding to the last column in my worksheet. Then, when I run the code a third time, I get the correct plot once again. Continuing to rerun my code repeats the cycle of good plot -> bad plot -> good plot. Any ideas on whats causing this?

Sub CreatingChartOnChartSheet()

Dim ch As Chart

Dim xrng As Range
Dim yrng1 As Range
Dim yrng2 As Range

Set ch = Charts.Add
Set xrng = Sheets("Power").Range("A2:A65536")
Set yrng1 = Sheets("Power").Range("D2:D65536")
Set yrng2 = Sheets("Power").Range("E2:E65536")

With ch

    ' If there is a previous chart, delete it
    For Each Chart In ActiveWorkbook.Charts
    If Chart.Name = "Power Chart" Then
        Application.DisplayAlerts = False
        Charts("Power Chart").Delete
        Application.DisplayAlerts = True
    End If
    Next Chart        
    
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "=""Series 1"""
    .SeriesCollection(1).XValues = xrng    
    .SeriesCollection(1).Values = yrng1
    
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = "=""Series 2"""
    .SeriesCollection(2).XValues = xrng
    .SeriesCollection(2).Values = yrng2
    
    .SetElement (msoElementChartTitleAboveChart)
    .Name = "Power Chart"
    .ChartTitle.Text = "Power"
    .SetElement (msoElementLegendRight)
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
    '.Axes(xlCategory).MajorUnit = 1
    '.Axes(xlCategory).MinorUnit = 1
    
End With

End Sub

Tim Williams · Accepted Answer · 2020-08-19 20:34:06Z

1

If you insert a chart sheet or chartobject while there's data selected on a worksheet, the chart will auto-plot the selected data. Whenever adding a chart via VBA it's a good practice to first delete any series which got auto-added.

Sub CreatingChartOnChartSheet()

    Dim xrng As Range
    Dim yrng1 As Range
    Dim yrng2 As Range
    
    With Sheets("Power")
        Set xrng = .Range("A2:A65536")
        Set yrng1 = .Range("D2:D65536")
        Set yrng2 = .Range("E2:E65536")
    End With

    Application.DisplayAlerts = False
    On Error Resume Next
    Charts("Power Chart").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    With Charts.Add
        'remove any auto-plotted data
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        With .SeriesCollection.NewSeries
            .Name = "Series 1"
            .XValues = xrng
            .Values = yrng1
        End With
        
        With .SeriesCollection.NewSeries
            .Name = "=""Series 2"""
            .XValues = xrng
            .Values = yrng2
        End With
        
        .SetElement msoElementChartTitleAboveChart
        .Name = "Power Chart"
        .ChartTitle.Text = "Power"
        .SetElement (msoElementLegendRight)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
        '.Axes(xlCategory).MajorUnit = 1
        '.Axes(xlCategory).MinorUnit = 1
        
    End With

End Sub

answered Aug 19, 2020 at 20:34

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Patrick Mirek Over a year ago

Thank you! Just so that I understand, the Do While loop you've set up will delete each SeriesCollection until there are none left (i.e., .SeriesCollection.Count = 0)? Also, why doesn't this work if I get rid of the (1) in .SeriesCollection(1).Delete?

Patrick Mirek Over a year ago

Also, I see you made some other changes to the code. Were these functional or just to organize things better (i.e. using With Charts.Add and With.SeriesCollection.NewSeries)?

Tim Williams Over a year ago

Yes - it removes the first series as long as there is one. SeriesCollection(1) is a specific series, and you can delete that. SeriesCollection is not deletable.

Tim Williams Over a year ago

Re -edits. I like to write in a certain style so can't resist making edits as I go - feel free to ignore any/all... Using With .SeriesCollection.NewSeries is less prone to maintenace issues, because you don't have to worry about how many series are already there. Also tend to remove "single-use" variables if it doesn't cause lines to get too cluttered/long.

Collectives™ on Stack Overflow

VBA create xy scatter plot using multiple data series

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related