application.statusbar updates freeze

[Robert Flanagan]

I have some code that updates the statusbar with the row number being
processed. Sometimes this update freezes but the code keeps running. Is
there a way to force the statusbar to update all the time?

For example

For r = 1 to 60000
application.statusbar "processing row " & r
next

will freeze. I've tried doing it every 10, 20, 100 rows and it will still
freeze. I can do a ctl-break and resume, but I don't want users to do that
obviously.

 

[Dave Peterson]

You could try adding doEvents in your code. It'll allow the operating system a
little time to do what it needs to do.

You could even do it every so often (depending on what else you do in your real
loop).

Dim r As Long
For r = 1 To 60000
Application.StatusBar = "processing row " & r
If r Mod 50 = 0 Then
DoEvents
End If
Next r

 

[Robert Flanagan]

Thanks Dave. I ust did what you suggested. And added a lot of different
screen activity. I ended up doing something like:

if rnd() > .995 then
statusbar and doevents and select cell
end if

which kept the screen changing. Problably slowed things down by 10-20% but
no one will complain.

Bob

 

[Dave Peterson]

I'm not sure why you're selecting the cell. There's not that many things that
need to have a specific cell selected.

And don't forget to turn calculation to manual, screenupdating to off, do the
work, calc back to automatic (or whatever it was before) and screenupdating to
on.