Dynamically add series to a chart

Question

I'm trying to create an excel which, using a couple of data validation toggle lists and a button, allows a user to display a specific plot for a specific stage of a model build. To do this, I need to be able to add series dynamically to a chart. I have found lots of material about this online, but in spite of my best efforts, I can't get my script to work. The lines within the if condition always seem to generate the following error "run time error '1004': application-defined or object-defined error". Any help would be greatly appreciated.

Sub UpdateChart()

'declaring variables'
Dim chrt As ChartObject
Dim chrtsercoll As SeriesCollection
Dim chtser As Series

'create the series collection from the chart'
Set chrt = ActiveSheet.ChartObjects(1)
'Get the series collection from the chart'
Set chrtsercoll = chrt.Chart.SeriesCollection

'delete all existing series in chart'
For Each chtser In chrtsercoll
        chtser.Delete
    Next chtser

'set up series in case of residual plot'
If Range("C21").Value = "residual series" Then
    With chrtsercoll.NewSeries()
    .Name = "=" & ActiveSheet.Name & "!B15"
    .Values = "=" & ActiveSheet.Name & "!" & Evaluate(ActiveSheet.Names("RSr").Value)
    End With
    ActiveSheet.ChartObjects(1).Chart.ChartType = xlLine
End If

End Sub

If you're getting an error then it helps to let us know on which line. — Tim Williams, Mar 07 '21 at 19:37

score 2 · Accepted Answer · answered Mar 07 '21 at 19:39

Something like this should work.

always qualify range references etc with a worksheet
use quotes around sheet names in range references in case the sheet name has spaces
you're not assigning anything to the series XValues
unclear if there's any issue with Evaluate(ActiveSheet.Names("RSr").Value) - maybe you can explain what you're doing there?

Sub UpdateChart()

    Dim ws As Worksheet

    Set ws = ActiveSheet
    With ws.ChartObjects(1).Chart
        'remove existing data
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        'set up series in case of residual plot'
        If ws.Range("C21").Value = "residual series" Then
            With .SeriesCollection.NewSeries
                .Name = "='" & ws.Name & "'!B15" 'use quotes in case name has spaces
                .XValues = 1 'you need to add something here....
                .Values = "='" & ws.Name & "'!" & Evaluate(ActiveSheet.Names("RSr").Value)
            End With
            .ChartType = xlLine
        End If
    End With

End Sub

Hi Tim, that was incredibly helpful so thank you. You're also very prescient in seeing that my evaluate bit was going to cause problems. The purpose of Evaluate(...) is to try mirror the indirect function in VBA. I have a couple of cells that contain the range of the data for the chart, which update automatically depending on what options are selected in two data validation drop down menus. If I could figure out how to do that, I'd be sitting pretty! — dougl, Mar 08 '21 at 08:13

score 0 · Answer 2 · answered Mar 08 '21 at 08:55

Just figured out the answer to my comment thanks by actually paying attention to what I'd put in my excel, and VBA telling you what your function equals when you hover over it. Just thought I'd put the properly working code here for the sakes of completeness. Massive thanks to Tim once again!

Sub UpdateChart()

Dim ws As Worksheet

Set ws = ActiveSheet
With ws.ChartObjects(1).Chart
    'remove existing data'
    Do While .SeriesCollection.Count > 0
        .SeriesCollection(1).Delete
    Loop
    'set up series in case of residual plot'
    If ws.Range("C21").Value = "residual series" Then
        With .SeriesCollection.NewSeries
            .Name = "='" & ws.Name & "'!B15" 'use quotes in case sheet name has spaces'
            .XValues = "=" & ws.Range("AC22").Value
            .Values = "=" & ws.Range("AC15").Value
        End With
        .ChartType = xlLine
    End If
End With
End Sub

Dynamically add series to a chart

2 Answers2