vba 刷新所有图表而不闪烁

Refresh all charts without blinking

提问by Yura Perov

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

目的是在单元格重新计算后刷新 Excel 中的所有图表。

I work with Microsoft Excel 2010.

我使用 Microsoft Excel 2010。

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

我们知道，有一个错误？在 Excel 中，以便 Excel 即使在

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?

这确实有效。但是，所有 Excel 图表都会闪烁（更新时它们会暂时变成白色）。请问有什么办法可以避免这样的眨眼吗？

I tried to call

我试着打电话

.Refresh

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

在所有图表上 ( 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".

但出于某种原因，我的 Excel (2010) 显示错误 #438“对象不支持此属性或方法”。

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

你能建议，拜托，我错过了什么重要的事情吗？

回答by Zegad

UntestedBut the .Refreshmay work with this:

未经测试但.Refresh可能适用于这个：

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) .Refreshonly works with the object Chartand not with the object ChartObjectsas 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)

那是因为（如您提供的链接所示）.Refresh仅适用于对象Chart而不适用于您一直尝试应用的对象ChartObjects。希望它会引导您朝着正确的方向前进。（还在代码中为屏幕上的闪烁/闪烁添加了引号）

回答by Jon Peltier

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.

我只是做了一些动画图表实验，使用 VBA 更改单元格中的计数器，以及工作表公式根据此计数器重新计算图表数据。

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.

我曾经做过很多图表动画，回到 Excel 97-2003 的时代，这些动画效果很好。当 Excel 2007 出来时，动画真的降级了，似乎没有任何帮助。但是刚才我在最新版本的 Office 365（版本 1904，版本 11504）中进行了这些测试。事实证明，在过去几年左右的某个时候，微软已经让它运行得更好。

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 DoEventsin the code after I change the cell's value.

我的经验告诉我DoEvents，在更改单元格的值后，我应该在代码中添加类似的内容。

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.

这比使用 one 运行得慢一点DoEvents，但要流畅得多；仍然不完美，但相当不错。

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

两个以上的 DoEvents 是矫枉过正：代码花费的时间相同，动画也没有任何流畅。

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

我也尝试过的各种组合Chart.Refresh，Chart.Activate和ScreenUpdating。两个要点：

• 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.

• 如果没有几个DoEvents，无论我尝试过什么其他事情，动画都无法正常工作。
• 使用几个DoEvents，这些额外的步骤都没有使动画更流畅，但它们可能会使其速度明显变慢。

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

这很有趣，所以我有一天会写博客。当我这样做时，我会回来并发布链接。

回答by Rodent

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.

通常情况下，在打开 Excel ScreenUpdating 后，在 VB.NET 查询有关闪烁或闪烁 Excel 图表后，我被发送到此 VBA 帖子。闪烁图表是让我发疯很长时间的事情，我没有看到任何有效的解决方案，包括上述看起来应该有效但实际上无效的解决方案。我现在找到了一个对我的所有程序都 100% 有效的解决方案。由于这是一篇 VBA 文章，我已经展示了一个针对闪烁图表的 VBA 解决方案，但我的 VB.NET 解决方案适用于被发送到这篇文章以寻找 VB.NET 解决方案的任何其他人。我的解决方案基于 Zegad 上面的答案，但它有几个基本的补充，这些补充没有记录在案，对我来说并不明显。使用以下子替代“MyXLApp.ScreenUpdating = True”。如果你发现它对你有用，请不要让我解释它为什么有效。我相信这里有很多人可能会解释这一点，但对我来说，这是运气和顽强决心的结果。一个奇怪的补充是，您实际上只需要激活和刷新然后停用任何一个图表，所有图表将在重新启用时更新而不会闪烁，请参阅下面的“VB.NET CODE-2 子”。

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