How can I control when and if my chart updates and not updates?

[stanhilliard]

I have a scatter chart on which I display points with VBA. I want to make sure the intended data point gets plotted but not to plot other intermediate data.

The chart is bound to three columns, one of which I change by adding data to the bottom of its column. This is a simulation, so I add data in a loop with code like the following.

DataPoint = MyRandomNormal() 'random normal observation.
Cells(R0 + nF, 4).Value = DataPoint 'display data to data column.

I use the following subroutine which is called when the data column is to be updated to the chart:

Sub ChartDataUpdate()
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(3).Select
Calculate
Range("H19").Select
End Sub

There are only certain configurations of the data column that I want to update the chart with, but I think the chart gets updated when I don't want it to.

I tried to use:

Application.ScreenUpdating = True
<snip>
Application.ScreenUpdating = False

to make the chart update when and only when I want it to, but there must be other considerations that I don't know about. The chart seems to update too often when I don't want it to.

Q1 - How can I make the chart update only when and if I want it to?

Q2 - One question I have is how does "Application.ScreenUpdating" interact with the code in the Sub.

Q3 - What other factors might effect this problem ?

Q4 - Is there a reference that addresses this problem?

[p45cal]

I'm guessing; I don't think you can control when the chart updates, but you may be able to control when the data updates by setting calculation to Manual, then either changing it to automatic or asking the sheet/workbook to be recalculated.Check out:
Application.Calculation = xlManual
Calculate
ActiveSheet.Calculate
Application.Calculation = xlAutomatic
Application.Calculation = xlSemiautomatic

So if you set calculation to manual, change data from which the points are calculated (assumes they're formulae), then just Calculate, or Sheets("xx").calculate.
If your points are plain values, I can't think of a way of preventing the chart updating.

A workaround might be to delete/remove the series you're plotting, or plot some blank cells instead, then when you're ready to update the chart re-establish the right data source for the series:

With ActiveSheet.ChartObjects("Chart 2").Chart
  .FullSeriesCollection(3).Values = "=Sheet1!$O$3:$O$11" 'blank cells
  'change the data here
  .FullSeriesCollection(3).Values = "=Sheet1!$P$3:$P$11" 'cells you want to plot
End With

You don't have to use strings for the address, you can use ranges instead:

With ActiveSheet.ChartObjects("Chart 2").Chart
  .FullSeriesCollection(3).Values = Range("$O$3:$O$11") 'blank cells
  'change the data here
  .FullSeriesCollection(3).Values = Range("$P$3:$P$11") 'cells you want to plot
End With

[p45cal]

A similar way is to plot your whole chart based on a range which is a copy of your real data. The copy would be plain values only (no formulae). Then update your real data points. When you're ready to update the chart, copy the real data to your copy range using vba.

[stanhilliard]

Here is an update: I originally thought the upates were happening at the wrong time to the wrong data because the points on the chart didn't agree with the column. I now discovered that what is happening is that the data points on the chart get updated 2 to 3 seconds after the correct data appears in the column. After 2 to 3 seconds the correct points appear on the chart.

So the right data is plotted, it just takes 2 to 3 seconds too long -- during which time the sheet looks like it contains a contradiction. So I just need to speed up the update.

[stanhilliard]

Here is another update: I think the experienced delay in updating the chart is not due to the ability of the updating process to update quickly.

My program was going through about 1,000 cycles between updates. I changed that to 10,000 cycles between updates and ran it again. This time it took about 10 times longer for the chart to get around to updating.

I suspected I had an error in basic code but I checked that out and it seems ok. It should update right after the new data is in the columns. Instead, the update is delayed 10,000 cycles (about 4 minutes) and occurs right at the beginning of the next 10,000 cycle group.

I moved the code:
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(3).Select
Calculate
Range("H19").Select
out of its sub routine and put it in-line following the data display. That didn't change the timing at all. The program seems to have a mind of it's own.

[stanhilliard]

Thank you p45cal,
It has taken my a while to get this procedure into my thick skull, but see if I understand.

My existing data column has the following configuration:

Dim AveragesColumn As Range

In Sub Macro1():
Set AveragesColumn = Range("E24:E35") 'Define column for clearing.
AveragesColumn.ClearContents 'For each cycle of the simulation.
<snip>

When it's time to update the chart:

Call CopyChartDataPoints()

Sub CopyChartDataPoints()
With ActiveSheet.ChartObjects("Chart 2").Chart
.FullSeriesCollection(3).Values = Range("$F$24:$F$35") 'always blank cells.

'my current data has previously been assigned to E24:E35

.FullSeriesCollection(3).Values = Range("$E$24:$E$35") 'copy existing cells.
End With
End Sub

Is this what you mean? I will try it tomorrow.

[p45cal]

Is this what you mean? I will try it tomorrow

Not really. Could you attach a file so that we can experiment for a solution?

[stanhilliard]

Attached is the complete file. I have set RND with a seed so the program runs the same every time as long as the input stays the same.

1 The file is "Simulator of TP414-K -- Test13.xlsm

2 The main sheet is "SamplingPlan"

4 Data points on the chart are dots (not lines).

3 The blue cells are output to compare to the chart. I find the most convenient way to compare is to after refresh see the number of dots on the chart and then to observe the number in cell J10.

5 The red cell is input (cycles[events] between chart updates). 3,000 cycles gives you 7 seconds on average to compare the number of data points on the chart to cell J10. You will see that the points on the chart are delayed by - exactly - one data display event.

6 Note1: The blue column below the chart represents one event. The number of occupied cells in the column is in J10. At 3,000 cycles, there are 2999 simulation events that are (intentionally) not displayed by my using "Application.ScreenUpdating = False"

7 NOTE2: When idle, the chart sometimes becomes not visible.

8 NOTE3: When the program is running, it often fades whitish and Windows 10 says "not responding", but the program continues running.

[p45cal]

In the attached I've made scant changes apart from what the chart plots; I've moved that to an area N24:R33.
When you want the chart to update, you update those values in one shot by calling:

Sub UpdateChart()
Dim zz
With Sheets("SamplingPlan")
  .Range("N24:R33").Value = .Range("A24:E33").Value
End With
Application.ScreenUpdating = True
For zz = 1 To 8 'experiment by doubling/halving the To value until you see satisfactory screen updates.
  DoEvents
Next zz
'MsgBox ""
Application.ScreenUpdating = False
End Sub

The above code is at the bottom of Module1
So in places in your code where you want the chart to update, just call UpdateChart.
Remove any other existing lines which try to update the chart save for those changing the TextBoxes.
You can hide columns N:R
If the chart doesn't update enough, enable the UpdateChart line directly before Next j , but that slows it down a lot. There is another commented out line above that which would update the chart every 500 iterations of j.

[stanhilliard]

Thanks.
I will work on it and post back tomorrow afternoon.

[stanhilliard]

I moved UpdateChart to where it should update -- right after the data is displayed. This works but the previous delay problem still exists.

But there is another problem. It turns out that statement "Application.ScreenUpdating = False" does not work in Excel 2016 -- which I have. A Google search confirms this. It is a bug and Microsoft apparently does not intend to fix for my version.

This calls for an alternative method of controlling screen updating. Fortunately in my program, all chart updating is done when j=1 and not the other thousands of values. I will examine the code to determine if I can use j>1 as a criterion for not updating. I will post back.

[p45cal]

I moved UpdateChart to where it should update -- right after the data is displayed. This works but the previous delay problem still exists.

Could you say exactly where this line has been put? Excel is not designed for animation. Oh, and did you try experimenting with:

For zz = 1 To 8 'experiment by doubling/halving the To value until you see satisfactory screen updates.

?

But there is another problem. It turns out that statement "Application.ScreenUpdating = False" does not work in Excel 2016 -- which I have. A Google search confirms this. It is a bug and Microsoft apparently does not intend to fix for my version.

I'm running Excel 2019 - same problem. But I was able to see charts fully updated in tune with the data visible on the sheet.
If .ScreenUpdating no longer works, it is even more important that the sheet and chart is updated as few times as possible. I have done this for the chart side of things - there is no reason for Excel to try and update the chart at all until you want it to, it no longer wants to update the chart with every single value change. We could also do this for the values in the cells, and only update them when we want them updated, by holding the values calculated by the macro in variables and arrays and only writing those values to the sheet when necessary - if you want to go further, you don't even need to write values to the sheet to change the chart.

This calls for an alternative method of controlling screen updating.

Well, pedantically, Microsoft isn't going to allow that as easily as before - so rather than control screen updating, control what appears on screen, and critically, when it appears. (This is what my code/suggestion does for the ranges plotted by the chart.)

Fortunately in my program, all chart updating is done when j=1 and not the other thousands of values. I will examine the code to determine if I can use j>1 as a criterion for not updating. I will post back.

In my previous message I referred (on the last line of that message) to a line that only updated the file every 500 iterations; this was it:

If j Mod 500 = 0 Then UpdateChart

A small change to

If j = 1 Then UpdateChart

should do that for you.

[stanhilliard]

I will try each of those things tomorrow. Meanwhile I did the following without favorable results.

I removed all "Application.ScreenUpdating = False" from the program since they don't work anyway - I thought. To comment or remove last one, the "Application.ScreenUpdating = False" in UpdateChart, caused the all 5,000 cycles to start updating the chart and I had to stop the program.

I determined its value is True before and after it executes. I don't understand why removing a nonworking function would do that.

Again, I will try your suggestions tomorrow.

[stanhilliard]

Solved - the chart now updates at the same time that the data to plot is put on the sheet.
p45cal: I am grateful for your help.

First, I was wrong earlier about "Application.ScreenUpdating = False" not working.
It does work. I had paused the program following the statement's execution. I positioned the mouse cursor over ScreenUpdating and the popup said "True". I interpreted that incorrectly as meaning "not false", even though the statement was "...= False". I believe now that the statement is neither true nor false. I think that Excel has a property that the VBA statement sets to "don't update."

Second -- this is what worked to fix the Chart update problem.
I had not yet tried putting all the data in VBA variables and then writing it to the sheet. So I googled something like "write a matrix to an Excel sheet." It found this:
http://excelexperts.com/33-vba-tips-...ithout-looping

Sub ReadAndWriteBackArray()
Dim myArray As Variant
myArray = Range("E24:E33").Value
Range("E24:E33").Value = myArray
End Sub

I added some stuff:

Sub ReadAndWriteBackArray()
Dim myArray As Variant
Dim zz
myArray = Range("E24:E33").Value
Range("E24:E33").Value = myArray
Application.ScreenUpdating = True
For zz = 1 To 8
DoEvents
Next zz
Application.ScreenUpdating = False
End Sub

I called ReadAndWriteBackArray() twice - right after displaying the final data point, and about 20 statements later. So far, this seems to work. I ran cycles from 100 to 100,000 with no problems.

[p45cal]

I suspect it will work just as well if this was the macro:

Sub ReadAndWriteBackArray()
Dim zz
Application.ScreenUpdating = True
For zz = 1 To 8 'try with larger values; 64, 256?
  DoEvents
Next zz
Application.ScreenUpdating = False
End Sub

[stanhilliard]

You are right. One call to that Sub containing a loop of 256 DoEvents.

I removed all the extra code.