vba 图表不会在 Excel (2007) 中更新

Chart won't update in Excel (2007)

提问by samJL

I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values

我有一个 Excel 文档（2007），其中有一个图表（簇状列），它从包含计算值的单元格中获取其数据系列

The calculated values never change directly, but only as a result of other cells in the sheet changing

计算值永远不会直接改变，而只是由于工作表中的其他单元格发生变化

When I change other cells in the sheet, the Data Series cells are recalculated, and show new values - but the Chart based on this Data Series refuses to update automatically

当我更改工作表中的其他单元格时，数据系列单元格会重新计算，并显示新值 - 但基于此数据系列的图表拒绝自动更新

I can get the Chart to update by saving/closing, or toggling one of the settings (such as reversing x/y axis and then putting it back), or by re-selecting the Data Series

我可以通过保存/关闭或切换其中一项设置（例如反转 x/y 轴然后将其放回去）或重新选择数据系列来更新图表

Every solution I have found online doesn't work

我在网上找到的每个解决方案都不起作用

• Yes I have Calculation set to automatic
• Ctrl+Alt+F9 updates everything fine, EXCEPT the chart
• I have recreated the chart several times, and on different computers

• I have tried VBA scripts like:

  Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
DoEvents

• 是的，我将计算设置为自动
• Ctrl+Alt+F9 更新一切正常，除了图表
• 我已经多次重新创建图表，并在不同的计算机上

• 我尝试过 VBA 脚本，例如：

  Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild
ActiveWorkbook.RefreshAll
DoEvents

None of these update or refresh the chart

这些都不更新或刷新图表

I do notice that if I type over my Data Series, actual numbers instead of calculations, it will update the chart - it's as if Excel doesn't want to recognize changes in the calculations

我确实注意到，如果我输入我的数据系列，实际数字而不是计算，它会更新图表 - 就好像 Excel 不想识别计算中的变化

Has anyone experienced this before or know what I might do to fix the problem? Thank you

有没有人以前经历过这个或知道我可以做些什么来解决这个问题？谢谢

采纳答案by tpascale

I have run into this same issue - not sure why, and when it happens the only way I have ever gotten the chart to force update is to change something in the chart definition itself, which can easily be done via VBA as in:

我遇到了同样的问题 - 不知道为什么，当它发生时，我让图表强制更新的唯一方法是更改​​图表定义本身中的某些内容，这可以通过 VBA 轻松完成，如下所示：

Dim C As ChartObject: Set C = Me.ChartObjects("chart name")
C.Chart.ChartTitle.Text = C.Chart.ChartTitle.Text + "1"

There may be a better answer that gets to the bottom of the problem - but I thought this might help. Working on the sheet I would do a quick Ctrl-X, Ctrl-V on a piece of the chart (or the whole thing) to force the chart to update.

可能有一个更好的答案可以解决问题的根源 - 但我认为这可能会有所帮助。在工作表上工作时，我会在图表的一部分（或整个事物）上快速按 Ctrl-X、Ctrl-V 以强制更新图表。

回答by Jason

This is the only thing I've found to consistently update a chart. It cuts the root cause of the problem (I assume): the series data is getting cached in the chart. By forcing the chart to re-evaluate the series, we are clearing the cache.

这是我发现的唯一可以持续更新图表的方法。它消除了问题的根本原因（我假设）：系列数据被缓存在图表中。通过强制图表重新评估系列，我们正在清除缓存。

' Force the charts to update
Set sht = ActiveSheet
For Each co In sht.ChartObjects
    co.Activate
    For Each sc In ActiveChart.SeriesCollection
        sc.Select
        temp = sc.Formula
        sc.Formula = "=SERIES(,,1,1)"
        sc.Formula = temp
    Next sc
Next co

回答by pb33

I had the same problem with a simple pie chart.

我在一个简单的饼图上遇到了同样的问题。

None of the macros worked that I tried. Nothing worked on cut, pasting, relocatingchart.

我尝试过的宏都不起作用。cut, pasting,relocating图表上没有任何效果。

The Workaround I found was to edit the chart text, remove the labels, then re-select the labels. Once they re-appeared, they were updated.

我发现的解决方法是编辑图表文本，删除标签，然后重新选择标签。一旦它们重新出现，它们就被更新了。

回答by Jo?o

I had this problem while generating 1000+ graphs through VBA. I generated the graphs and assigned a range to their series. However, when the sheet recalculated the graphs wouldn't update as the data ranges changed values.

我在生成 1000 多个图形时遇到了这个问题VBA。我生成了图表并为它们的系列分配了一个范围。但是，当工作表重新计算时，图表不会随着数据范围更改值而更新。

Solution--> I turned WrapText off before the For...Next Loop that generates the graphs and then turned it on again after the loop.

解决方案--> 我在 For...Next 循环之前关闭了 WrapText 以生成图形，然后在循环之后再次打开它。

Workbooks(x).Worksheets(x).Cells.WrapText=False 

and after...

之后...

Workbooks(x).Worksheets(x).Cells.WrapText=True

This a great solution because it updates 1000+ graphs at once without looping through them all and changing something individually.

这是一个很好的解决方案，因为它一次更新 1000 多个图形，而无需遍历它们并单独更改某些内容。

Also, I'm not really sure why this works; I suppose when WrapText changes one property of the data range it makes the graph update, although I have no documentation on this.

另外，我不确定为什么会这样；我想当 WrapText 更改数据范围的一个属性时，它会使图形更新，尽管我没有这方面的文档。

回答by Marc

This is an absurd bug that is severely hampering my work with Excel.

这是一个荒谬的错误，严重阻碍了我使用 Excel 的工作。

Based on the work arounds posted I came to the following actions as the simplist way to move forward...

根据发布的变通办法，我采取了以下行动，作为前进的最简单方式......

Click on the graph you want update - Select CTRL-X, CTRL-V to cut and paste the graph in place... it will be forced to update.

单击要更新的图形 - 选择 CTRL-X、CTRL-V 将图形剪切并粘贴到位...它将被强制更新。

回答by Sam

This works very well for me -- it flips axes on all charts and then flips them back, which causes them to refresh without changing at all.

这对我来说非常有效——它翻转所有图表上的轴，然后将它们翻转回来，这使它们刷新而根本不改变。

'Refresh all charts
For Each mysheet In ActiveWorkbook.Sheets
    mysheet.Activate
    For Each mychart In ActiveSheet.ChartObjects
        mychart.Activate
        ActiveChart.PlotArea.Select
        ActiveChart.PlotBy = xlRows
        ActiveChart.PlotBy = xlColumns
        ActiveChart.PlotBy = xlRows
    Next
Next

回答by toan

I have another problem of refeshing charts. When generating the charts automatically, some charts appear over and cache the text in the sheet. It happens to be a problem of refreshing the generated charts. When I zoom in or zoom out, I can get the expected results. So I post the solution here if it interest someone. Programmatically, I added this after generating charts :

我还有另一个刷新图表的问题。自动生成图表时，一些图表会出现在表格中并将文本缓存起来。刚好是刷新生成的图表的问题。当我放大或缩小时，我可以获得预期的结果。因此，如果有人感兴趣，我会在此处发布解决方案。以编程方式，我在生成图表后添加了这个：

ActiveWindow.Zoom = ActiveWindow.Zoom + 1
ActiveWindow.Zoom = ActiveWindow.Zoom - 1

回答by user3063819

Ok I have a solution, really....

好的，我有一个解决方案，真的......

I found that the problem with my charts not updating first occurred shortly after I had hidden some data columns feeding the chart, and checked "show data hidden in rows and columns" in the Chart's "Select Data Source" msg box).

我发现我的图表没有更新的问题首先发生在我隐藏了一些提供图表的数据列之后不久，并在图表的“选择数据源”消息框中选中了“显示隐藏在行和列中的数据”）。

I found that if I went back into the "Select Data Source" msg box and unchecked/rechecked the "show data hidden in rows and columns" that the chart refreshes.

我发现如果我回到“选择数据源”味精框并取消选中/重新选中图表刷新的“显示隐藏在行和列中的数据”。

Programatically I inserted the following into a Macro that I linked a button to, it refreshes all of my charts quick enough for a workaround to a known bug. This code assumes one chart per worksheet but another for statement for charts 1 to N could be added if desired:

我以编程方式将以下内容插入到我将按钮链接到的宏中，它可以足够快地刷新我的所有图表，以便解决已知错误。此代码假设每个工作表有一个图表，但如果需要，可以为图表 1 到 N 添加另一个 for 语句：

Sub RefreshCharts()

    Application.ScreenUpdating = False

For I = 1 To ActiveWorkbook.Worksheets.Count

Worksheets(I).Activate

    ActiveSheet.ChartObjects("Chart 1").Activate

    ActiveChart.PlotVisibleOnly = True

    ActiveChart.PlotVisibleOnly = False

Next I

    Application.ScreenUpdating = True

End Sub

回答by Ayman Al-Absi

I faced the same issue. The issue is due to restriction in no. of calculated formulas in your sheet. you can solved it using two ways:

我遇到了同样的问题。问题是由于没有限制。工作表中的计算公式。您可以使用两种方法解决它：

Manual force re-calculate:

手动力重新计算：

Press SHEFT + F9

Macro to force re-calculate:add below code to the end of the function which changes the data

强制重新计算的宏：将以下代码添加到更改数据的函数的末尾

Activesheet.Calculate

I found the solution of it:From excel options make sure to change the calculation options as below. It changed sometimes to manual after heavy work in excel.

我找到了它的解决方案：确保从 excel 选项中更改计算选项，如下所示。在 excel 中进行繁重的工作后，它有时会更改为手动。

回答by Porsche9II

This is a known Excel bug...

这是一个已知的 Excel 错误...

The best and fastest workaround is the Columns.AutoFit- Trick:

最好和最快的解决方法是Columns.AutoFit- 技巧：

Sub Update_Charts()
    Application.ScreenUpdating = False
    Temp = ActiveCell.ColumnWidth
    ActiveCell.Columns.AutoFit
    ActiveCell.ColumnWidth = Temp
    Application.ScreenUpdating = True
End Sub