Excel VBA Updating Chart Series

Question

I have a code the copying the range to a certain cell and change the data source to the chart. the chart has combination chart type, which has stacked columns and xlLineMarker with last series(Grand Total). The number of the series collection varies based on user selections(2 or more).

It works, but sometimes the chart doesn't get fully updated like the image attached (displaying empty chart with series name as 'series1, series2, ...). When it happens, The right-click on the chart and click select data, and then simply selecting any series other than series1 update the chart. not refresh or re-assigning the data source. just selecting 'series2' or others in the data selection windows and click 'cancel', which I suspects that the macro doesn't fully executed...,

am I doing something wrong? or is there any way fully update the chart?

Sub chtupdate()

    Dim rng As Range
    Dim i As Integer
    Dim n As Integer

    Set rng = Range("G37").CurrentRegion

    ActiveSheet.ChartObjects("Chart1").Activate
    With ActiveChart
        .ChartArea.ClearContents
        .SetSourceData Source:=rng, PlotBy:=xlRows

        n = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.Count                
        For i = 1 To n - 1
            .SeriesCollection(i).ChartType = xlColumnStacked
        Next i    

        .SeriesCollection(n).ChartType = xlLineMarkers
    End With

End Sub

"which I suspects that the macro doesn't fully executed" -- Why don't you verifiy that by stepping in with the debugger? atm the only advice I have in mind is to 1) not Activate the chart (no need); 2) disable screen updating during the macro; 3) since you're inside a With clause, replace n = ActiveSheet.ChartObjects(1).Chart.SeriesCollection.Count with the simpler n = .SeriesCollection.Count — A.S.H
Thank you for your comment. I've got rid of Activating chart. I do have code to disable and enable screen updating code between the codes below. I just didn't add that here. As your recommendation, I've used 'Step In' with the debugger. the problem persists. it works at first, but it fails at second. I am not sure why it does work sometimes and doesn't work. And thanks for the tip on '.SeriesCollection.Count'. yes, it's cleaner. Anyway, the issue persists. — Danny Yun

Shai Rado Shai Rado · Accepted Answer · 2017-05-06T04:42:12

Try the code below, I've added ChartObject and Series objects variables for an easier and "cleaner" code. Also, to avoid the need of using ActiveSheet and ActiveChart - a method that be unreliable.

Code

Option Explicit

Sub chtupdate()

Dim MyCht       As ChartObject
Dim Ser         As Series

Dim Rng As Range
Dim i As Long
Dim n As Long

' fully qualify all your Ranges and Cells
With Worksheets("Sheet3") ' <-- replace "Sheet1| with your sheet's name
    Set Rng = .Range("G37").CurrentRegion

    ' set the chart object
    Set MyCht = .ChartObjects("Chart1")
End With

With MyCht
    .Chart.ChartArea.ClearContents
    .Chart.SetSourceData Source:=Rng, PlotBy:=xlRows

    For Each Ser In .Chart.SeriesCollection
        Ser.ChartType = xlColumnStacked
    Next Ser

    ' modify the last one
    Set Ser = .Chart.SeriesCollection(.Chart.SeriesCollection.Count)
    Ser.ChartType = xlLineMarkers
End With

End Sub

Excel VBA Updating Chart Series

1 Answers