Application Lockups During Code Execution 4

[MattGreer]

So I wrote a little code to run through a table and trim all the cells. As has happened in the past, Excel will "lock up" during code execution so I can't tell if it's still running, confused, broken, or angry. (Does Excel get angry? Perhaps, heh.)

I learned a few months ago that putting in the 'DoEvents' command would allow the application to update the screen, or some such thing, so that any update messages would be sent to the Status Bar and the whole business wouldn't lock up. However, it seems that adding 'DoEvents' increases the time required for executing the sub.

For example, in a table with ~53000 cells, it took 6.5 minutes to Trim all the cells with 'DoEvents' included, versus 3.5 minutes without.

Is there a better way to keep Excel (or other Office applications for that matter) from locking up during code execution? Or is there a way to throw something up on the screen to let the user (usually me) know "Hey, everything is OK, I haven't locked up, just running through the code"?



Thanks!!


Matt

 

[Andrzejek]

Did you Turn off Screen Updating while going down the rows?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

faq707-4105

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

The issue isn't the speed per se. The issue is that Excel locks up, and in order to keep it from locking up I have to add something that slows everything down. So I'm wondering if there is something less costly that I can do instead of 'DoEvents'?

(edit: I also specifically have made sure I am not 'Selecting' and 'Activating' cells except when absolutely necessary.)

With regards to screenupdating:

I have this bracketing the call to the sub procedure. When I start, bStarting = TRUE, and then after the procedure I call this again with bStarting = FALSE. If you can think of anything to add to this, I would appreciate the suggestion.

Public Function RunCode(bStarting As Boolean)

    If bStarting = True Then
        With Application
            .ScreenUpdating = False
            .DisplayStatusBar = True
            .EnableEvents = False
            .EnableAnimations = False
            .Calculation = xlCalculationManual
        End With
    Else
        With Application
            .ScreenUpdating = True
            .StatusBar = vbNullString
            .EnableEvents = True
            .EnableAnimations = True
            .Calculation = xlCalculationAutomatic
        End With
    End If

End Function

Thanks!!


Matt

 

[DjangMan]

Drawing on the screen is a very 'expensive' task for the computer to do so when you can avoid that, you should.

You could call DoEvents every X number of records instead of once per cell. I'll update the status bar when I call DoEvents so that the user can see that things are progressing. And I'll usually choose a number that's unusual like 63 or 117 so that the "processed x of 50000" records status update is more fun to watch because the numbers are changing in 'unpredictable' increments.

 

[Andrzejek]

If I want to update my display every, let's say, 50 records, I do something like:

For i = 1 To intNoOfRecs
    If i Mod 50 = 0 Then
        Application.StatusBar = "Record " & i & " of " & intNoOfRecs
    End If
    ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

Excel isn't locked up, it's just busy doing the things you told it to do.

Any you're too impatient to let it finish.

As Andy says, update the statusbar every occasionally to give you some reassurance that things are still ticking.

https://www.exceltip.com/menus-tool...e-statusbar-using-vba-in-microsoft-excel.html

 

[combo]

If values/text is the output, you can assign range into variant array, process the array, and return it back to the sheet. The schema:

[tt]Dim rngInput as Range, vInput
Set rngInput = ... ' the range you process
vInput = rngInput ' get values from range to 2d array
' process vInput array items
rngInput = vInput ' processed data back to the worksheet[/tt]

Should run much faster.

combo

 

[MattGreer]

I'd wondered about stuffing everything into an array, faster eh? I'll check that out for sure. Thanks!

Thanks!!


Matt

 

[MattGreer]

mintjulep SIX MINUTES IS A LIFETIME or something... Yes, I am impatient, but at the same time I have a crappy computer and for all I know it's locked up even though my code is PERFECT, hehehe. I appreciate the link, thank you!

Thanks!!


Matt