Refresh all charts without blinking

Question

The aim is to refresh all charts in Excel after cells recalculation.

I work with Microsoft Excel 2010.

As we know, there is a bug? in Excel so that Excel does not update charts even after

Application.CalculateFullRebuild

A known hack is to do something like this:

Application.ScreenUpdating = False
Temp = ActiveCell.ColumnWidth
ActiveCell.Columns.AutoFit
ActiveCell.ColumnWidth = Temp
Application.ScreenUpdating = True

This does work. However, all Excel charts blink (they become white for a moment while updating). Could you advise, please, is there any way to avoid such blinking?

I tried to call

.Refresh

on all charts (https://msdn.microsoft.com/en-us/library/office/ff198180(v=office.14).aspx):

For Each ChartObject In ActiveSheet.ChartObjects
    ChartObject.Refresh
Next

but for some reason my Excel (2010) shows error #438 "Object doesn't support this property or method".

Could you advise, please, do I miss something important?

Answer 1

Untested But the .Refresh may work with this:

Sub ChangeCharts()
Application.ScreenUpdating = False 'This line disable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower
Dim myChart As ChartObject
For Each myChart In ActiveSheet.ChartObjects
    myChart.Chart.Refresh
Next myChart
Application.ScreenUpdating = True'This line reenable the on screen update for better performance, the blink you see, you could delete both lanes but it will run slower
End Sub

And that's because (as the link you provide shows) .Refresh only works with the object Chart and not with the object ChartObjects as you have been trying to apply it. Hope it'll guide you in the right direction. (also added quotes for the blink/flicker on screen in the code)

Answer 2

Happy Pi Day!

I just did some experiments with animating charts, using VBA to change a counter in a cell, and worksheet formulas to recalculate chart data based on this counter.

I used to do a lot of chart animations, back in the days of Excel 97-2003, and those ran pretty well. When Excel 2007 came out, the animations really degraded, and nothing seemed to help. But just now I did these tesst in the latest build of Office 365 (Version 1904, Build 11504). And it turns out, sometime in the past few years or so, Microsoft has made it work better.

Sub ChartAnimation1()
  Dim i As Double
  For i = 0 To 1000 Step 50
    ActiveSheet.Range("Stepper") = i
  Next
End Sub

The animation didn't animate, that is, the chart didn't change despite the data changing.

My experience told me I should put something like DoEvents in the code after I change the cell's value.

Sub ChartAnimation2()
  Dim i As Double
  For i = 0 To 1000 Step 50
    ActiveSheet.Range("Stepper") = i
    DoEvents
  Next
End Sub

This helped a little, the chart changed, but the animation was not smooth. Some steps were missed, and the effect was a herky-jerky animation.

Sub ChartAnimation3()
  Dim i As Double
  For i = 0 To 1000 Step 50
    ActiveSheet.Range("Stepper") = i
    DoEvents
    DoEvents
  Next
End Sub

This ran a bit more slowly than with one DoEvents, but it was a lot smoother; still not perfect, but pretty good.

More than two DoEvents was overkill: the code took the same length of time, and the animation was not any smoother.

I also tried various combinations of Chart.Refresh, Chart.Activate, and ScreenUpdating. Two takeaways:

• Without a couple DoEvents, the animation didn't work regardless of what other things I tried.
• With a couple DoEvents, none of these extra steps made the animation any smoother, but they could make it significantly slower.

This was pretty interesting, so I'll blog about it some day. When I do I'll come back and post a link.

Answer 3

As is often the case I was sent to this VBA post following a VB.NET query regarding blinking or flashing Excel Charts after turning on Excel ScreenUpdating. Blinking Charts is something that has been driving me mad for a long time now and I have seen no solutions that work including the above solution that looks like it should work but doesn't. I have now found a solution that works 100% for all of my programs. As this is a VBA post I have shown a VBA solution to the flashing charts but my VB.NET solution is for anyone else who is sent to this post looking for a VB.NET solution. My solution is based on the answer by Zegad above but it has a couple of essential additions that are not documented and which to me are not obvious. Use the following sub as a replacement for "MyXLApp.ScreenUpdating = True". If you find it works for you please do not ask me to explain why it works. I'm sure there are many here who could probably explain this but for me it is the result of luck and dogged determination. An odd addition here is that you actually only need to activate and refresh then deactivate any one chart and all of the charts will update without flashing when re-enabled, See 'VB.NET CODE-2 sub below.

Sub ScrUpdateEnableNoFlicker()'VBA CODE
    Dim myChartObj As ChartObject
    For Each myChartObj In ActiveSheet.ChartObjects
        myChartObj.Activate 'IMPORTANT ADDITION
        myChartObj.Chart.Refresh
    Next
    Cells.Range("A1").Select 'IMPORTANT ADDITION
    Application.ScreenUpdating = True
End Sub

Private Sub ScrUpdateEnableNoFlicker() 'VB.NET CODE-1
    'BEFORE TURNING SCREEN UPDATING BACK ON...
    'ACTIVATE and refresh the chart objects on the sheet with the charts.
    Dim aSheet As Excel.Worksheet = CType(mXLWrkbk.Sheets("Sheet1"), Excel.Worksheet)
    Dim aChartObjects As Excel.ChartObjects = CType(aSheet.ChartObjects, Excel.ChartObjects)
    For Each achartobject As Excel.ChartObject In aChartObjects
        achartobject.Activate() 'IMPORTANT - Will not work without activating first
        Dim achart As Excel.Chart = achartobject.Chart
        achart.Refresh()
    Next
    'Now deactivate the current activated chart object by selecting any cell
    'THIS IS IMPORTANT - It will not work without doing this
    Dim selRange As Excel.Range = aSheet.Range("A1")
    selRange.Select()
    'Now turn Screen Updating back on...
    'All of the Charts will have updated and will not flicker
    mXLApp.ScreenUpdating = True
End Sub

Private Sub ScrUpdateEnableNoFlicker() 'VB.NET CODE-2
    'BEFORE TURNING SCREEN UPDATING BACK ON...
    'ACTIVATE ANY ONE of the chart objects on the sheet with the charts.
    Dim aSheet As Excel.Worksheet = CType(mXLWrkbk.Sheets("Sheet1"), Excel.Worksheet)
    Dim aChartObject As Excel.ChartObject = CType(aSheet.ChartObjects("Chart 9"), Excel.ChartObject)
    aChartObject.Activate() 'IMPORTANT - Will not work without activating first
    'Refresh just the ONE activated chart.
    Dim aChart As Excel.Chart = aChartObject.Chart
    aChart.Refresh()
    'Now deactivate the current activated chart object by selecting any cell
    'THIS IS IMPORTANT - It will not work without doing this
    Dim selRange As Excel.Range = aSheet.Range("A1")
    selRange.Select()
    'Now turn Screen Updating back on...
    'You only need to activate/deactivate any one chart and all of the Charts will have updated and will not flicker
    mXLApp.ScreenUpdating = True
End Sub

Answer 4

I was having this issue when hiding or showing a series in my chart. The change would not be apparent until I would scroll away then back again to the chart, which was really a pain. I tried all the above solutions with no luck until I realized unselecting and selecting again the chart before doing the change would work.

myChart.TopLeftCell.Select

myChart.Select

...

Good luck in your research for a solution ;)

Answer 5

Thanks to those who have posted here before! Without your successes, I would not have smooth animation of a dynamic simulation. In my case, it is an xlXYScatterLinesNoMarkers type chart. Running in VBA.

This works for me when changing the series programmatically. The chart is animated smoothly. Running Excel 2016 64 bit

Public Sub ShowOneAnimationFrame(worksheetName As String, chartName As String, _
                                 xvals() As Double, yvals() As Double)

    'update chart series programmatically
    'Excel 2016 64bit
    'Dec 21, 2020
    'Author:  S^3
    
    Dim theChart As chart
    Dim chrtObj As ChartObject
    Dim oneSeries As Series
    
    Set chrtObj = Sheets(worksheetName).ChartObjects(chartName)
    Set theChart = chrtObj.chart
    
    If theChart.SeriesCollection.Count = 0 Then
        theChart.SeriesCollection.NewSeries
    End If
    Set oneSeries = theChart.SeriesCollection(1)
    
    'update the series with new values
    oneSeries.XValues = xvals
    oneSeries.Values = yvals
    
    theChart.Refresh  'required (this and the next line are required but the order doesn't matter)
    chrtObj.Select    'required
    Cells.Range("A1") = Cells.Range("A1").value  'something like this is required
    
End Sub