Chart updates only after all calculations finished... why?

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
I recall reading about this somewhere but I can't remember the "nuts and bolts". Searched previous posts with no luck. I have a column (500 rows) that I fill with data using values calculated thru VBA. The column is linked to a line chart. If I write the values to the column once, the values show on the chart. But if I update the column repeatedly (using a nested For... Next loop) say 1000 times, the 500 values do not show on the chart until after the 1000th iteration. Is there a way around this so that the chart is updated as the rows in the column are filled during each iteration (or at least at the end of each iteration)?

Another question - if I write data to cells in the worksheet within a For... Next statement, the speed of the looping slows dramatically (even if it's only to a couple of cells). Is this normal?

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
For your first question. Put DoEvents in the loop like

For x = 1 to 1000
code here
DoEvents
Next x

This will update the chart, but the macro will be slower.

For your second question. Loops are slow by nature. If you try to do anything in the loops they are even slower. Put Application.ScreenUpdating = false at the beginning of your code to speed things up.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Just as an example of how long it takes to run loops. I made a bar chart with three values. I made a loop

Code:
Sub test()

Dim MyTime         As Double

MyTime = Timer
'Application.ScreenUpdating = False
For x = 1 To 1000
Range("b2").Value = x
Range("b3").Value = x + x
Range("b4").Value = 2 * x
'DoEvents
Next x

MsgBox Timer - MyTime
End Sub

With screenupdating on and doevents it takes me 7+ seconds
take off the doevens and it takes 4+ seconds
take off the doevents and turn off screenupdating and it takes .9 seconds.

You can make macros run faster, but if you want to see what is happening it will take a lot longer.
 

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
Re: Chart updates only after all calculations finished... wh

Thanks Jacob,

I had tried the DoEvents before you posted your second message and saw how much slower it was. I'll post my code in case you or anyone can see a way of making this faster. The data written to Column 3 (Cells(i_Reel + 1, 3).Value = Round(ParVal(i_Reel), 0)) is linked to a line chart.
Code:
Private Sub CommandButton1_Click()
Dim Ran_Noise() As Double, Ran_Shift() As Double, Ran_PShift() As Double
Dim U1_Noise As Double, U2_Noise As Double, V1_Noise As Double
Dim V2_Noise As Double, S_Noise As Double
Dim U1_Shift As Double, U2_Shift As Double, V1_Shift As Double
Dim V2_Shift As Double, S_Shift As Double
Dim Mean() As Double, SD_Noise As Single, SD_Shift As Single, Prob_Shift As Single
Dim ParVal() As Double
Dim i_Reel As Integer, i_Sim As Integer
Dim UnderSpec As Integer, OverSpec As Integer, Iteration As Integer
Randomize
BelowSpec = 0
OverSpec = 0
Iteration = 0
Cells(1, 1).ClearContents
Cells(2, 4).ClearContents
Cells(3, 4).ClearContents
For i_Sim = 1 To 1000
    Range("C:C").ClearContents
    ReDim Ran_Noise(1 To 500)
    ReDim Ran_Shift(1 To 500)
    ReDim Ran_PShift(1 To 500)
    ReDim ParVal(1 To 500)
    ReDim Mean(0 To 500)
    Mean(0) = 91.9
    SD_Noise = 2.774887
    SD_Shift = 6.884766
    Prob_Shift = 0.04561
        For i_Reel = 1 To 500
            S_Noise = 2
            S_Shift = 2
            Do While S_Noise > 1
                U1_Noise = Rnd
                U2_Noise = Rnd
                V1_Noise = 2 * U1_Noise - 1
                V2_Noise = 2 * U2_Noise - 1
                S_Noise = V1_Noise ^ 2 + V2_Noise ^ 2
            Loop
            Do While S_Shift > 1
                U1_Shift = Rnd
                U2_Shift = Rnd
                V1_Shift = 2 * U1_Shift - 1
                V2_Shift = 2 * U2_Shift - 1
                S_Shift = V1_Shift ^ 2 + V2_Shift ^ 2
            Loop
            Ran_Noise(i_Reel) = (Sqr(-2 * Log(S_Noise) / S_Noise) * V1_Noise) * SD_Noise
            Ran_Shift(i_Reel) = (Sqr(-2 * Log(S_Shift) / S_Shift) * V1_Shift) * SD_Shift
            Ran_PShift(i_Reel) = Rnd
                If Ran_PShift(i_Reel) < Prob_Shift Then
                    Mean(i_Reel) = Mean(0) + Ran_Shift(i_Reel)
                Else:
                    Mean(i_Reel) = Mean(i_Reel - 1)
                End If
            ParVal(i_Reel) = Mean(i_Reel) + Ran_Noise(i_Reel)
                If ParVal(i_Reel) < 50 Then
                    BelowSpec = BelowSpec + 1
                ElseIf ParVal(i_Reel) > 120 Then
                    OverSpec = OverSpec + 1
                End If
            Cells(i_Reel + 1, 3).Value = Round(ParVal(i_Reel), 0)
            Cells(2, 4).Value = "No. Below Spec = " & BelowSpec
            Cells(3, 4).Value = "No. Over Spec = " & OverSpec
            DoEvents

        Next i_Reel
Iteration = Iteration + 1
Cells(1, 1).Value = "Iteration No. = " & Iteration
Next i_Sim
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,123
Members
425,262
Latest member
sabry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top