vbaexcelupdates

VBA program stops to refresh the worksheet


I'm making my own Conway's Game of Life on VBA where the current state is displayed on a worksheet. Because I'm not skilled, the implementation is probably not very efficient (I use 2 boolean matrices to model the current state and the next one). To display the result at each step, I've sub display() that took the matrix_currentand color each cell in black or white. To make the whole process smoother, I've wrapped Application.ScreenUpdating = Falseand Application.ScreenUpdating = True. Short story long, it looks like that :

Private Sub display()
Application.ScreenUpdating = False
For i = 0 To sizeGrid
    For j = 0 To sizeGrid
        If matrix_curr(i, j) Then
            Cells(i + xmin, j + ymin).Interior.ColorIndex = 1
        Else
            Cells(i + xmin, j + ymin).Interior.ColorIndex = 2
        End If
    Next
Next
Application.ScreenUpdating = True
End Sub

Before each Call display() I call the method Sleep() to let enough time to watch each step.

So, here is my issue: The display on the worksheet often stops after a small amount of steps. However, the program is still running and finally shows the last state. So basically, I can monitor the beginning, then nothing change until the last step that is displayed. Everything happen as if the worksheet suddenly stop to be refreshed.

Do you have any idea to solve this issue.

I thank you in advance for your help (and hope that I make myself understood despite my poor English)


Solution

  • My problem is that after a number of steps, nothing more happen on the screen (as if it was freeze).

    That's because Excel is essentially running out of breath - it's basically not keeping up with all the ScreenUpdating toggles.

    When you toggle Application.ScreenUpdating back on once, Excel happily responds by repainting itself.

    When you're running a busy loop, it's prioritizing execution of the VBA code and the Excel UI goes:

    (not responding)
    

    This is normal: there's a lot of things to process, so it's processing them - updating the UI, raising worksheet events, responding to user actions, calculating cells, all these things "drop priority", until the VBA code completes.

    Try adding a DoEvents instruction immediately after toggling Application.ScreenUpdating back on; this explicitly tells Excel "okay, go ahead, process whatever other stuff you've got, then come back here when you're ready". I'd warmly recommend leaving Application.EnableEvents off and Application.Calculation set to xlCalculationManual until the code completely executed.