CODEWITHSUNDEEP
excelvbadynamicdynamically-generatedexcel-charts
dougl
dougl

Reputation: 23

Dynamically add series to a chart

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

Upvotes: 2

Views: 1016

Answers (2)

dougl
dougl

Reputation: 23

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

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 2

Related Questions