Chart Updates

Hi All,

I cant post the whole code due to size and the data but basically, I am having an issue updating a chart. I want, if possible, for the chart to update each iteration of a loop (I'm looping over columns and rows). I know that the loops are running and that the data is updating (I have the loop counter and data temporarily referenced to individual debug cells) but the chart only updates right at the end.

I assumed that the loops were running too fast so I added DoEvents (and even screen updating) as below but still no updates. The Call Delay is an existing function to slow loops down but that doesn't help whether in front or behind the chartObj refresh.

Next i ' End of row loop

Call Delay

Application.ScreenUpdating = True
chartObj.Chart.Refresh
DoEvents


Am i missing a way to 'force' an update as data changes?


Any thoughts appreciated thanks

Next j ' End of column loop

The nested loops are simply taking a 2D block of data, iterating columns then running a check of data down a series of rows. Basically, each cell in each row is checked against a corresponding row value to check higher/lower causing icons (named per a corresponding row) to be updated on a diagram. To reduce the overhead I was intending to update the chart on column shift rather than on the row shift (one action per loop rather than dozens in the row loop).


I did actually get the update to work by moving it back into the row loop (the individual chart bars now update in real-time) but it doubles run-time.

I then spammed DoEvents to see if they were being skipped (as you noted):

" Next i ' End of row loop

chartObj.Chart.Refresh
DoEvents
DoEvents
DoEvents

Call Delay

Next j ' End of column loop "

Trial and error suggests, for my application at least, it takes 3 x DoEvents before Excel takes the hint (1 is ignored and 2 only updates on about half of the iterations).

it really looks like Excel just doesn't update the chart (even with the DoEvents) on a loop that takes a fraction of a second.

I will leave the question open for a couple of days to see if anyone has any thoughts on a more elegant solution