Unusal screen updating/flicker issue

[NeverLift]

I have a somewhat complex VBA script that's working well but is annoying.

I'm building a chart with a messy computation involving accessing and changing the contents of several worksheets to compute each point. I want to watch the chart as it is being built, but not all the worksheet changes.

So, I'm calling a routine in a loop. It does the work of computing the next point and adding it to the source arrays of the chart. (There are actually multiple plots on the one chart, all being built with one more point on each call.) I use

Application.ScreenUpdating = False

before each call, then

Application.ScreenUpdating = True

to get the chart display updated, after each call.

The annoyance: When the latter is hit, it appears that Excel is visibly cycling through all the changed Worksheets and updating them as well!

Any ideas on preventing this? I'd like to see just the chart being updated without the flicker factor. Incidentally: I'd like the sheet in which the chart is located to be updated -- the computation shows its progress by maintaining certain values in its cells -- and that sheet, of course, is the selected one when I initiate the chart building.

None of the code uses any sheet selection, but rather references all the data it needs/updates through "With" or explicit sheet labels in the cell addresses/labels/ranges.

 

[Derek Brown]

I will sometimes add something like the following after each 'ScreenUpdating' line:

Application.Wait Now() + TimeValue("00:00:03")

 

[Hamburgler]

I have struggled with flickering issues many times before too. Not sure if it is applicable here but would DoEvents rather than toggling ScreenUpdating be any better? Also, does your code call any other procedures that switch ScreenUpdating back on?

 

[NeverLift]

Nope, checked that by reading the update property in the "cover" routine that turns it off, then on, just before the latter action. It was off the whole way.

If it had not been: The sub that the cover routine calls would have run forever, with a lot of flickering. It's building a large multiline chart a point at a time, and that chart is, visibly, not being updated until the final screen updating being reset to automatic.

 

[NeverLift]

What would be really cool is being able to turn screen updating back on for just the active worksheet. But, it would be cool if I won the Texas Powerball . . .

 

[Akihito Yamashiro]

Does this help you?
http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.charting/2006-09/msg00193.html

If not, I'm sorry.

 

[NeverLift]

Thanks. It's not the chart flicker that I wanted to avoid but that, on my turning on screen updating when all computations were done and the new chart points had been added to their ranges, Excel was very quickly visibly activating each sheet that had changed entries in order to update it, finally updating the chart.

The flicker is very quick, really an annoyance that I can live with.

 

[Hamburgler]

Is the procedure that you are calling selecting the different sheets in order to update them? If so can you update the data without actually selecting the sheets? e.g. Sheets("X").[A1].Value = 1. Also did you try not enabling ScreenUpdating until the very end and just using DoEvents once for each loop?

 

[NeverLift]

Is the procedure that you are calling selecting the different sheets in order to update them? If so can you update the data without actually selecting the sheets? e.g. Sheets("X").[A1].Value = 1. Also did you try not enabling ScreenUpdating until the very end and just using DoEvents once for each loop?

Just checked. All references to the sheets other than that in focus, where the chart resides, are made exactly as you describe; there are no sheet selections anywhere in the computations.

Haven't tried DoEvents but, from what I read, that is to permit the OS to pay attention to requests from outside the running VBA, not to update my chart. I know it is doing the former since my chart building loop has a 1 second wait built in so I can see its progress and interrupt it -- Ctrl-Break -- when it displays a situation for which I want to take action. (When I use that interrupt, it takes effect when the wait ends, which is good, that's when the update cycle is at a stable point where interruption does not introduce anomalies from unfinished business.) The interrupt is then honored and processed, and I can even resume (by restarting, with the assigned Ctrl-Shift key) the updating (or use one of a number of other macros to record, process, analyze the most current chart source data).

Again, it is an annoyance. I'll be analyzing data with this sheet for a long time, I can live with the annoyance.