vba 数据更改时图表不会自动更新

Charts Do Not Automatically Update When Data Changes

提问by Adam

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?

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

---

---

UDPATE:

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:

• 创建新工作簿
• 将工作表 1 重命名为“摘要”
• 将工作表 2 重命名为“数据”

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

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

• 创建一个新的 VBA 模块

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

   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  
  

  .

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

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

• 选择单元格 B1 并将范围命名为“工作表日期”
• 复制下图中的第 1 行到第 3 行：

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

• 在第 4 行的“第 X 周”标题下，输入以下公式

.

.

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

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.

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

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

• 使用单元格 A3 到 E4 作为数据创建条形图
• 将单元格 B2 中的日期更改为 10/1/2010 和 12/31/2010 之间的日期，选择当前单元格中的月份以外的月份。例如，如果日期是 12/11/2010，请将其更改为 11/11/2010 或 10/11/2010 之类的内容。请注意，数据和图表都正确更新。
• 修改单元格 B2 增益中的日期。请注意，数据会更新，但图表不会。

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.

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

回答by TmTron

at the end of my changes I close the workbook and reopen it. that seems the easiest and most reliable way to update everything for me.

在更改结束时，我关闭工作簿并重新打开它。这对我来说似乎是更新所有内容的最简单和最可靠的方法。

回答by Ageel Al-Shayeb

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.

我刚刚在打印或导出之前添加了“DoEvents()”，并且图表得到了刷新。

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  

回答by Cyberchipz

I've found that calling this Sub works...

我发现调用这个 Sub 有效......

Sub DoAllEvents()
    DoEvents
    DoEvents
End Sub

BUTMicrosoft cautions about being caught with the next DoEvents executing before the first DoEvents completes, which can happen depending on how often it's called without a delay between calls. Thus DoEvents appears to be acting as a type of non maskable interrupt, and nesting non maskable interrupts cancause the machine to freeze for multiple reasons without any recovery other than reboot.

但是微软警告不要在第一个 DoEvents 完成之前被下一个 DoEvents 捕获，这可能会发生，这取决于它在调用之间没有延迟的情况下被调用的频率。因此，DoEvents 似乎充当了一种不可屏蔽的中断，嵌套不可屏蔽的中断会导致机器由于多种原因而冻结，除了重新启动之外没有任何恢复。

(Note: If one is not calling the routine above, often and quickly, nesting maynot be an issue.)

（注意：如果没有经常快速地调用上面的例程，嵌套可能不是问题。）

Using the following Sub below, which I modified from their suggestion, prevents this from happening.

使用下面的 Sub ，我根据他们的建议进行了修改，可以防止这种情况发生。

Sub DoAllEvents()
    On Error GoTo ErrorCheck
    Dim i
    For i = 1 To 4000    ' Start loop. Can be higher, MS sample shows 150000
        'I've found twice is enough, but only increased it to four or 4000.
        If i Mod 1000 = 0 Then     ' If loop has repeated 1000 times.
            DoEvents    ' Yield to operating system.
        End If
    Next i
    Exit Sub
ErrorCheck:
    Debug.Print "Error: "; Error, Err
    Resume Next
End Sub

I appears that the number of DoEvents needed is based on the number of background tasks running on your machine, and updating the graph appears to be a background task for the application. I only needed two DoEvents because I call the routine frequently; however, I may end up upping it later if needed. I also keep the Mod at 1000 so to not change the lag between each DoEvents as Microsoft suggests, preventing nesting. One possible reason you might want to increase the number from 2000 to a higher number is if you system does not update the graph. Increasing this number allows the machine to handle larger numbers of background events that DoEvents might encounter through multiple calls as they are probably on a stack, and the DoEvents event is only allowed to run a specific number of cycles before marking its place in the stack to allow unhandled events and returning, leaving them to be handled on the next call. Thus the need for multiple calls. Changing this to their example of 150000 doesn't appear to slow the machine too much, to play it safe you might want to make it 150000.

我似乎需要的 DoEvents 数量取决于您的机器上运行的后台任务的数量，更新图表似乎是应用程序的后台任务。我只需要两个 DoEvent，因为我经常调用例程；但是，如果需要，我可能会在以后结束它。我还将 Mod 保持在 1000，这样就不会像 Microsoft 建议的那样更改每个 DoEvents 之间的延迟，从而防止嵌套。您可能希望将数字从 2000 增加到更高的一个可能原因是您的系统没有更新图表。增加这个数字允许机器处理更多的后台事件，DoEvents 可能通过多次调用遇到，因为它们可能在一个堆栈上，并且 DoEvents 事件在标记它在堆栈中的位置之前只允许运行特定数量的周期，以允许未处理的事件并返回，让它们在下一次调用时处理。因此需要多次调用。将其更改为 150000 的示例似乎不会使机器变慢太多，为了安全起见，您可能希望将其设为 150000。

Note: the first example Sub with two DoEvents is probably safe depending on how often you call the Sub, however, if called too often, your machine might freeze up. Your call. ;-)

注意：第一个带有两个 DoEvent 的示例 Sub 可能是安全的，具体取决于您调用 Sub 的频率，但是，如果调用太频繁，您的机器可能会死机。您的来电。;-)

PS: DoEvents will become one of your best calls if you create a lot of nested loops and the program doesn't behave as expected. Fortunately, this is available in all apps that use VBA!

PS：如果您创建了很多嵌套循环并且程序没有按预期运行，DoEvents 将成为您最好的调用之一。幸运的是，这适用于所有使用 VBA 的应用程序！

回答by Drkhrse

Running Excel 2019.

运行 Excel 2019。

Added the following to the macro code:

在宏代码中添加了以下内容：

ActiveSheet.ChartObjects(1).Chart.Refresh    
DoEvents

The chart now updates during macro execution

图表现在在宏执行期间更新

回答by SLeepdepD

This solution worked for me. For the offending worksheet add:

这个解决方案对我有用。对于违规工作表添加：

Private Sub Worksheet_Activate()
  Dim rngSelection          As Range
  Dim objChartObject        As ChartObject
  Dim objChart              As Chart
  Dim objSeriesCollection   As SeriesCollection
  Dim objSeries             As Series
  Dim strFormula            As String

  Set rngSelection = Selection

  For Each objChartObject In Me.ChartObjects
    Set objChart = objChartObject.Chart
    Set objSeriesCollection = objChart.SeriesCollection
    For Each objSeries In objSeriesCollection
      strFormula = objSeries.Formula

      objSeries.Delete

      Set objSeries = objSeriesCollection.NewSeries

      objSeries.Formula = strFormula
    Next objSeries
  Next objChartObject

  rngSelection.Select
End Sub

回答by user3716832

It's possible that the issue is the argument list of getWeekValue, which includes only the week number and the data stream.

问题可能出在 getWeekValue 的参数列表上，它只包含周数和数据流。

If you add a third argument, worksheetDate, then Excel's recalculation engine will be hit on the side of the head with the fact that getWeekValue uses the value held in worksheetDate. In your current implementation, this fact is held only in the VBA code, where it is probably invisible to the recalculation engine.

如果您添加第三个参数 worksheetDate，则 Excel 的重新计算引擎将在头部一侧受到影响，因为 getWeekValue 使用了 worksheetDate 中保存的值。在您当前的实现中，此事实仅存在于 VBA 代码中，在该代码中，重新计算引擎可能看不到它。

I write this so hedgingly because I am not privy to the inner workings of the recalculation engine. (Maybe someone who knows about this better than I can comment on my speculation) But I did do a test, in which getWeekValue does have that third argument, and the chart does recalculate properly. Nice added benefit of this approach: you can remove all that other VBA event management. -HTH

我写这篇文章是因为我不了解重新计算引擎的内部工作原理。（也许有人比我对我的猜测更了解这一点）但我确实做了一个测试，其中 getWeekValue 确实有第三个参数，并且图表确实重新计算正确。这种方法的额外好处是：您可以删除所有其他 VBA 事件管理。-HTH

回答by Dr. belisarius

For 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

   w.ChartObjects(1).Chart.Refresh

End Sub  

回答by Patrick Honorez

Just an idea: in your Worksheet_Change Sub, insert as the first line:

只是一个想法：在您的 Worksheet_Change Sub 中，作为第一行插入：

Application.EnableEvents = False

in order to avoid self-firing events....
Of course set it back to True at the end of the Sub.

为了避免自发事件......
当然在Sub结束时将其设置回True。