+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Sudden "freeze" while executing vba-loop

Hybrid View

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    norway
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Sudden "freeze" while executing vba-loop

    Solve: Adding in DoEvents in the code removed the random halts.

    I have been exploring the more entertaining sides of excel recently, mostly by various animations performed through vb-algorithms. By manipulating the fill-color in cells, I have been able to replicate a simple snake-game, fireworks-animations and now a simple wave-simulation.

    However, upon execution of any of these little programs, it seems as though the code stops executing after about 5-10 seconds or so.
    Task manager says excel is using ~100% cpu for the entire execution of the program. After the program is seemingly halted, the cpu usage is still 100%, and the worksheet is "locked" as though a program was running some code on it. I need to press CTRL+break to stop the code properly, after which I can restart the code without any problems for the next 5-10 seconds.

    Surely I must be missing something.

    Here is the code for the wave-simulator (with no real delay, thus making the animation way too fast on any new computers):
    'Some ugly coding, I know..
    Dim y(300) As Double
    Dim ys As Double
    Dim yd As Integer
    Dim temp5 As Long
    
    Sub wave()
    
        init
        
        While Cells(1, 1) = "False"
        
        For temp = 300 To 1 Step -1
            Cells(CInt(y(temp)), temp).Interior.ColorIndex = Null
            y(temp) = y(temp - 1)
            Cells(CInt(y(temp)), temp).Interior.ColorIndex = 1
        Next temp
        
        If yd = 1 Then
            If ys > 1 Then
                yd = -1
                ys = -0.1
            End If
        ElseIf yd = -1 Then
            If ys < -1 Then
                yd = 1
                ys = 0.1
            End If
        End If
        ys = ys / 0.9
        
        Cells(CInt(y(0)), 1).Interior.ColorIndex = Null
        y(0) = y(0) + ys
        Cells(CInt(y(0)), 1).Interior.ColorIndex = 1
        
        Wend
    
    End Sub
    
    Sub init()
    
    Cells(1, 1) = "False"
    For temp = 0 To 300
        Cells(20, temp + 1).Interior.ColorIndex = 1
        y(temp) = 20
    Next temp
    ys = 1#
    yd = 1
    End Sub
    Last edited by JohnFred; 05-31-2010 at 07:50 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Sudden "freeze" while executing vba-loop

    try adding a DoEvents inside the While/Wend loop.

        Cells(CInt(y(0)), 1).Interior.ColorIndex = Null
        y(0) = y(0) + ys
        Cells(CInt(y(0)), 1).Interior.ColorIndex = 1
        DoEvents
        Wend
    
    End Sub
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    05-31-2010
    Location
    norway
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Sudden "freeze" while executing vba-loop

    Ah! Very good, my code is running smoothly now without any weird halts.

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1