图表在数据更改时不会自动更新 [英] Charts Do Not Automatically Update When Data Changes

问题描述

希望这是一个容易的。我在MS Excel中有一系列图表，指向同一工作表上的数据。使用VBA函数计算工作表上的数据。当通过VBA功能更新数据时，新数字不会反映在指向它们的图表中。我尝试调用Application.Calculate，但是没有这样做。任何想法？

---

UDPATE：

我能够以更小的规模复制这个问题。如下：

• 创建新的工作簿


• 将表1重命名为摘要


• 将表2重命名为数据



• 在VBA编辑器中打开摘要表，并粘贴以下代码：

  
  

    Private Sub Worksheet_Change（ByVal Target As Range）
  如果Target.Parent.Range（worksheetDate）=目标然后
   Application.CalculateFull 
   End If 
   End Sub 
    

  
  




• 创建一个新的VBA模块




• 将以下代码粘贴到新的VBA模块中（我道歉 - 我不能得到堆栈溢出格式化这个正确的我的生活 - 这是最好的我可以做到）：
  
  。

   函数getWeekValue（weekNumber As Integer，valuesRange As Range）As Integer 
   
   Dim aCell As Range 
   Dim currentDate As Date 
   Dim arrayIndex As Integer 
   Dim weekValues（1 To 6）As Integer 
   
   currentDate = ThisWorkbook.Names（worksheetDate）。RefersToRange.Value 
   arrayIndex = 1 
  每个aCell在valuesRange 
  如果month（currentDate）= month（ThisWorkbook.Sheets（Data）。Cells（_ 
   aCell.Row  -  1， aCell.Column））然后
   weekValues（arrayIndex）= aCell.Value 
   arrayIndex = arrayIndex + 1 
   End If 
   Next 
   
   getWeekValue = weekValues（ weekNumber）
  结束功能
    

  。




• 修改数据工作表以匹配以下图像：

• 选择单元格B1并将范围命名为worksheetDate


• 以下图像中的第1到3行重复：

• 在第4行的Week X标题下，输入以下公式：

。

  = getWeekValue（1，Data！$ A $ 2：$ M $ 2）
  

将getWeekValue函数的第一个参数每周增加一个（例如，第1周的第1个，第2周的第3周，第3周的第2个等等

• 使用单元格A3至E4创建条形图作为数据


• 更改单元格B2中的日期到2010年10月1日至2010年12月31日，选择当前在单元格中的月份以外的一个月，例如，如果日期是2010年11月12日，请将其更改为注意，数据和图表都会正确更新。


• 修改单元格B2增益中的日期。请注意，数据更新，但是图表没有。

奇怪的是，经过一段时间（几分钟），图表终于更新了不确定是否因为我一直在执行触发更新的其他活动，或者因为Excel在几次之后触发更新分钟。

解决方案

只是想到了这个问题的解决方案，因为我患有同样的问题。

我刚刚在打印或导出之前添加了DoEvents（），图表已刷新。

示例

  Sub a（）
 Dim w As Worksheet 
 Dim a 
 Set w = Worksheets（1）
 
对于每个a在w.Range（a1：a5）
 a.Value = a.Value + 1 
下一个
 
 DoEvents 
 
 End Sub 
  

Hopefully this is an easy one. I have a series of charts in MS Excel that point to data on the same worksheet. The data on the worksheet is calculated using a VBA function. When the data is updated by the VBA function the new numbers are not reflected in the charts that are pointing to them. I tried calling Application.Calculate, but that didn't do the trick. Any thoughts?

---

UDPATE:

I was able to duplicate this issue on a much smaller scale. Here's how:

• Create a new workbook
• Rename Sheet 1 to "Summary"
• Rename Sheet 2 to "Data"

• Open the Summary sheet in the VBA editor and paste the following code:

  Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Parent.Range("worksheetDate") = Target Then
        Application.CalculateFull
     End If
  End Sub
  

• Create a new VBA module

• Paste the following code into the new VBA module (I apologize - I can't get Stack Overflow to format this correctly for the life of me - this is the best I could get it to do):
  .

   Function getWeekValue (weekNumber As Integer, valuesRange As Range) As Integer   
  
   Dim aCell As Range  
   Dim currentDate As Date  
   Dim arrayIndex As Integer  
   Dim weekValues(1 To 6) As Integer  
  
   currentDate = ThisWorkbook.Names("worksheetDate").RefersToRange.Value
   arrayIndex = 1  
   For Each aCell In valuesRange 
       If month(currentDate) = month(ThisWorkbook.Sheets("Data").Cells( _  
                                     aCell.Row - 1, aCell.Column)) Then
           weekValues(arrayIndex) = aCell.Value 
           arrayIndex = arrayIndex + 1 
       End If 
   Next
  
   getWeekValue = weekValues(weekNumber)   
   End Function  
  

  .

• Modify the Data worksheet to match the following image:

• Select Cell B1 and name the range "worksheetDate"
• Duplicate rows 1 through 3 in the following image:

• In row 4, under the "Week X" headers, enter the following formula

.

 = getWeekValue(1, Data!$A$2:$M$2)

incrementing the first argument to the getWeekValue function by one for each week (e.g., pass 1 for Week 1, 2 for Week 2, 3, for Week 3, etc.

• Create a bar graph using cells A3 through E4 as the data
• Change the date in cell B2 to a date between 10/1/2010 and 12/31/2010, choosing a month other than the month that is currently in the cell. For example, if the date is 12/11/2010, change it to something like 11/11/2010 or 10/11/2010. Note that both the data and chart update correctly.
• Modify the date in cell B2 gain. Note that the data updates, but the chart does not.

Oddly, after a period of time (several minutes) has elapsed, the chart finally updates. I'm not sure if this is because I have been performing other activities that triggered the update or because Excel is triggering an update after several minutes.

解决方案

Just figured out the solution to this issue as I was suffering from the same.

I've just added "DoEvents()" prior to printing or exporting and the chart got refreshed.

example

Sub a()
   Dim w As Worksheet
   Dim a
   Set w = Worksheets(1)

   For Each a In w.Range("a1:a5")
     a.Value = a.Value + 1
   Next

   DoEvents

End Sub  

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