Excel 2013 vba refresh chart content after each iteration of For Loop

Question

Problem: Refresh graphical representaion of Series in ChartObject after each iteration of for loop

Ex. y=m*Cos(x) 
y - Values
m - parameter

I have some data counted from formula with parameter. I want to visualise what influence has the change of parameter on XYgraph. I want to do it in for loop (added Sleep do have some time to see results). Data and formula are in Excel SpreadSheet. Update script of parameter is in VBA module. Update works on values in spreadsheet but doesn't affect graph.

WorkBook.RefreshAll doesn't work; Chart.Refresh doesn't work

Chart updates after last iteration.

Any ideas?

vacip · Accepted Answer · 2015-06-21 12:12:17Z

1

Add a DoEvents to your code after each iteration step. It works for me.

answered Jun 21, 2015 at 12:12

vacip

5,4563 gold badges29 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lukasz Over a year ago

Thx Vacip. Works like a charm! DoEvents works. DoEvents+Sleep is dead.

vacip Over a year ago

You can try application.wait instead of sleep. The drawback is that the minimum time is one second there. The upside is that it is not dependant on CPU ticks (sleep is).

Mika72 · Accepted Answer · 2018-09-27 07:04:35Z

In Excel for Office 365 I got XYChart live updating working only this way (XYChart to be updated during/in the end of each For loop iteration round). I could not get Sleep() working at all. Unfortunately Application.Wait tick resolution is 1 second, so the animation may be relatively slow. Also <chartObject>.Chart.Refresh did not have desired effect (for auto updating during the For loop).

Note: Some of the codes below may not be necessary in your case (at least Application.ScreenUpdating should be ON by default).

Application.ScreenUpdating = True    
ActiveSheet.ChartObjects(1).Activate

...

    For i = 1 To 10
       'Chart source data cells manipulation/update and other chart related routines here.'

       ActiveSheet.ChartObjects(1).Activate
       Application.Wait (Now + TimeValue("00:00:01"))
       DoEvents
   Next i

Collectives™ on Stack Overflow

Excel 2013 vba refresh chart content after each iteration of For Loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related