Update chart in Excel after every iteration of loop
up vote
0
down vote
favorite
I'm trying to animate a parametric curve in Excel by generating its ranges in an iterative loop. I'm using the code:
Public Sub playswirl()
Range([a8], [a8].End(xlDown)).Clear
Set cell = Range("a8")
Do While cell.Offset(0, 1) <> ""
cell.Offset(-1, 0).Copy cell
ActiveSheet.ChartObjects(2).Chart.Refresh
DoEvents
Sleep (50)
Set cell = cell.Offset(1, 0)
Loop
End Sub
The chart is cleared in the first instruction, then rebuilt one item at a time in the loop. While the code is running, I see the values in the spreadsheet changing one at a time, but the chart never updates (I see the full curve, i.e. the state before the first line of code is executed). When I ctrl-break and put the code in debug state, the chart updates to the point where the code was interrupted.
I thought that the chart.refresh would have done the trick - and added the doevents in for good measure when that didn't work - but no luck. Changing the sleep call to the Excel-native Application.Wait call doesn't help either (and is too slow in any case).
Any ideas?
excel vba charts
asked Nov 10 at 15:36
plantrob
134
add a comment |
up vote
0
down vote
favorite
I'm trying to animate a parametric curve in Excel by generating its ranges in an iterative loop. I'm using the code:
Public Sub playswirl()
Range([a8], [a8].End(xlDown)).Clear
Set cell = Range("a8")
Do While cell.Offset(0, 1) <> ""
cell.Offset(-1, 0).Copy cell
ActiveSheet.ChartObjects(2).Chart.Refresh
DoEvents
Sleep (50)
Set cell = cell.Offset(1, 0)
Loop
End Sub
The chart is cleared in the first instruction, then rebuilt one item at a time in the loop. While the code is running, I see the values in the spreadsheet changing one at a time, but the chart never updates (I see the full curve, i.e. the state before the first line of code is executed). When I ctrl-break and put the code in debug state, the chart updates to the point where the code was interrupted.
I thought that the chart.refresh would have done the trick - and added the doevents in for good measure when that didn't work - but no luck. Changing the sleep call to the Excel-native Application.Wait call doesn't help either (and is too slow in any case).
Any ideas?
excel vba charts
asked Nov 10 at 15:36
plantrob
134
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm trying to animate a parametric curve in Excel by generating its ranges in an iterative loop. I'm using the code:
Public Sub playswirl()
Range([a8], [a8].End(xlDown)).Clear
Set cell = Range("a8")
Do While cell.Offset(0, 1) <> ""
cell.Offset(-1, 0).Copy cell
ActiveSheet.ChartObjects(2).Chart.Refresh
DoEvents
Sleep (50)
Set cell = cell.Offset(1, 0)
Loop
End Sub
The chart is cleared in the first instruction, then rebuilt one item at a time in the loop. While the code is running, I see the values in the spreadsheet changing one at a time, but the chart never updates (I see the full curve, i.e. the state before the first line of code is executed). When I ctrl-break and put the code in debug state, the chart updates to the point where the code was interrupted.
I thought that the chart.refresh would have done the trick - and added the doevents in for good measure when that didn't work - but no luck. Changing the sleep call to the Excel-native Application.Wait call doesn't help either (and is too slow in any case).
Any ideas?
excel vba charts
asked Nov 10 at 15:36
plantrob
134
I'm trying to animate a parametric curve in Excel by generating its ranges in an iterative loop. I'm using the code:
Public Sub playswirl()
Range([a8], [a8].End(xlDown)).Clear
Set cell = Range("a8")
Do While cell.Offset(0, 1) <> ""
cell.Offset(-1, 0).Copy cell
ActiveSheet.ChartObjects(2).Chart.Refresh
DoEvents
Sleep (50)
Set cell = cell.Offset(1, 0)
Loop
End Sub
The chart is cleared in the first instruction, then rebuilt one item at a time in the loop. While the code is running, I see the values in the spreadsheet changing one at a time, but the chart never updates (I see the full curve, i.e. the state before the first line of code is executed). When I ctrl-break and put the code in debug state, the chart updates to the point where the code was interrupted.
I thought that the chart.refresh would have done the trick - and added the doevents in for good measure when that didn't work - but no luck. Changing the sleep call to the Excel-native Application.Wait call doesn't help either (and is too slow in any case).
Any ideas?
excel vba charts
excel vba charts
asked Nov 10 at 15:36
plantrob
134
asked Nov 10 at 15:36
plantrob
134
asked Nov 10 at 15:36
plantrob
134
asked Nov 10 at 15:36
plantrob
134
asked Nov 10 at 15:36
plantrob
134
134
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:
- Try adding the "DoEvents" to your loop like explained here.
- Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.
Let me know how those go.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:
- Try adding the "DoEvents" to your loop like explained here.
- Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.
Let me know how those go.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
add a comment |
up vote
0
down vote
I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:
- Try adding the "DoEvents" to your loop like explained here.
- Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.
Let me know how those go.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
add a comment |
up vote
0
down vote
up vote
0
down vote
I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:
- Try adding the "DoEvents" to your loop like explained here.
- Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.
Let me know how those go.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I think it may not be a problem with the chart; it may be a problem with the updating system. If Application.ScreenUpdating = True (which it is by default), then here are some things you can try:
- Try adding the "DoEvents" to your loop like explained here.
- Try adding "Calculate" to the function. For example, write the code ActiveSheet.Calculate.
Let me know how those go.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered Nov 11 at 2:44
Parker.R
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered Nov 11 at 2:44
Parker.R
64
answered Nov 11 at 2:44
Parker.R
64
64
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Parker.R is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
add a comment |
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
Thank you for your reply. As shown in my code above, DoEvents is already part of the loop. The calculations are being performed immediately as each source cell is added (I see dependent cells being populated as the loop progresses). Just to be sure, I added Application.Calculate to the loop, but it didn't help with the problem.
– plantrob
Nov 11 at 14:37
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240504%2fupdate-chart-in-excel-after-every-iteration-of-loop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
