VBA更改数据时Excel图表不更新 [英] excel chart not updating when data changed by vba

问题描述

我有一个气泡图，我试图通过让宏更新表和随后的图表来添加运动".我有一个单元格用作偏移量"，数据表使用该单元格来获取数据.我有一个按钮，该按钮运行一个VBA宏，该宏每个月都会更新我的数据中的偏移"单元格，并在偏移量更新时更新数据表.

I have a Bubble chart that I am trying to add "motion" to by having a macro update the table and subsequently the chart. I have a cell that I use as the "offset" which is used by my data table to get the data. I have a button that runs a VBA macro that updates this "offset" cell for each month in my data, which updates the data table when the offset updates.

当我手动更改偏移单元格时，表格和图表都会更新.但是，当我单击按钮并运行VBA宏时，只会更新表，而不会更新图形.

When I alter the offset cell manually, both the table and chart update. However, when I click the button and run the VBA macro, only the table updates, NOT the graph.

我已经研究了可能的解决方案，包括Stack Overflow上位于此处的那些项目，并尝试了以下方法:

I have looked researched possible solution, including those items located here on Stack Overflow, and have tried the following:

-DoEvents
-Applications.Calculate
-ActiveWorkbook.RefreshAll
-Chart.Refresh
-setting the Application.ScreenUpdating to false and back to true

这是我的VBA代码:

Sub Button7_Click()
Dim i As Integer

For i = 0 To 9:
    Range("P1").Value = i
    Application.Calculate
    Application.Wait DateAdd("s", 1, Now)
Next
Range("P1").Value = 0
End Sub

通过VBA更新表格时，更新图形应该不难.

It shouldn't be this hard to update a graph when the table updates via VBA.

推荐答案

我遇到了这个问题.似乎与Excel 2016有关.

I've had this problem. Seems related with Excel 2016.

Sub CommButton1_Click()
i = Cells(4, 24)
start = Cells(4, 22)
rango = Cells(4, 23) + start
Do Until start > rango
    Sleep (20)
    Cells(4, 25).Value = start
    start = start + i
    DoEvents
    DoEvents
Loop
End Sub

灵魂似乎是要添加另一个DoEvents.我已经在Excel 2016中进行了尝试，并且可以正常工作，但是时间延迟很大.

The soultion seems to be to add another DoEvents. I've tried in Excel 2016 and it worked, but with a big time delay.

这篇关于VBA更改数据时Excel图表不更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！