vba - 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)