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

[Skippy]

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

 

[DRJ]

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]

Just as an example of how long it takes to run loops. I made a bar chart with three values. I made a loop

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]

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.

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