刷新所有图表而不闪烁 [英] Refresh all charts without blinking

问题描述

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

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

我使用Microsoft Excel 2010.

I work with Microsoft Excel 2010.

我们知道，有一个错误吗?在Excel中显示，以便Excel即使在更新之后也不会更新图表

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

这确实有效.但是，所有Excel图表都闪烁(更新时，它们会变成白色一会儿).你能建议，有什么办法避免这种眨眼吗?

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?

我试图打电话

.Refresh

在所有图表上( https://msdn.microsoft.com/en-us/library/office/ff198180(v=office.14).aspx ):

For Each ChartObject In ActiveSheet.ChartObjects
    ChartObject.Refresh
Next

但是由于某些原因，我的Excel(2010)显示错误#438对象不支持此属性或方法".

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?

推荐答案

未经测试，但是 .Refresh 可以与此配合使用:

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

那是因为(如您所提供的链接所示) .Refresh 仅适用于对象图表，而不适用于对象 ChartObjects 您一直在尝试应用它.希望它会引导您朝着正确的方向发展. (还在代码中为屏幕上的闪烁添加了引号)

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)

这篇关于刷新所有图表而不闪烁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！