Based on an example in Walkenbach's book [1], the
following code is intended to update the status bar every
second or so. Sometimes the status bar is updated as
expected. Sometimes the updates "stall" until the "done"
update. The point at which the updates "stall" varies. I
have tried intervals of 1, 2 and 3 sec, all with the same
erratic results.

Any idea why the status bar update "stalls", and why the
"stall" is erratic?


Sub timeit()
limitTime = 20: interval = 1
Application.StatusBar = _
"00:00 Start limit=" & limitTime & " intvl=" & interval
startTime = Timer: lastTime = startTime
n = 0: nLoop = 0
Do
curTime = Timer: nLoop = nLoop + 1
If curTime - lastTime = interval Then
lastTime = curTime: n = n + 1
Application.StatusBar = _
Format(lastTime - startTime, "00:00") & _
" n=" & n & "/" & nLoop & _
" limit=" & limitTime & " intvl=" & interval
End If
Loop While curTime - startTime < limitTime
Application.StatusBar = _
Format(curTime - startTime, "00:00") & _
" n=" & n & "/" & nLoop & " Done intvl=" & interval
End Sub


The macro continues to run when the update "stalls".
nLoop averages 44.5e6 +/- 0.2% independently of the
interval, even when I set the interval to 30 so that there
are no updates. (Of course, the average nLoop count
will vary from system to system.)

My guess is that the status bar updates are asynchronous,
and the macro fails to yield the CPU long enough for it to
occur. My guess is that when it works, it is because higher
priority interrupts (processes or hardware interrupts) allow
the system to complete the status bar update before
resuming execution of the macro.

But I am surprised that Walkenbach does not hint at the
possibility in his example. That makes me suspicious of my
interpretation (guess) of the problem, and I wonder if it is
unique to my system -- perhaps symptomatic of other erratic
system misbehavior.

PS: I am using Excel 2003.


-----
[1] "Excel 2003 Power Programming with VBA", p. 474.
Walkenbach writes:

You can, of course, update the status bar while your
macro progresses. For example, if you have a variable
named Pct that represents the percent completed, you
can write code that periodically executes a statement
such as this:
Application.StatusBar = "Processing..." & Pct & "% Completed"

" wrote:
Any idea why the status bar update "stalls", and why the
"stall" is erratic?

I meant to also ask: is there perhaps some VBA or Excel option
that would force a synchronous update of the status bar?

Of course, that ass-u-me-s that my guess of the root cause of
the problem is correct.

PS: My apologies for the poor VBA formatting. I don't know
why the indentation was "lost".

Your code worked as expected for me in xl2000. Why not add a debug.print
line in your code similar to what you are putting in the statusbar. I expect
both will update similarly.

Regards,
Peter T


" wrote
in message ...
" wrote:
Any idea why the status bar update "stalls", and why the
"stall" is erratic?

I meant to also ask: is there perhaps some VBA or Excel option
that would force a synchronous update of the status bar?

Of course, that ass-u-me-s that my guess of the root cause of
the problem is correct.

PS: My apologies for the poor VBA formatting. I don't know
why the indentation was "lost".

"Peter T" wrote:
Your code worked as expected for me in xl2000.

I'm curious: how many times did you try? As I wrote before,
the misbehavior is erratic. Originally, it seemed to fail most
of the time -- in fact, I thought it "always" failed. But as I
continued to tweak the example, it seemed to misbehave less
often -- perhaps 1 in 5 times.

I really do suspect this has more to do with my system than
with Excel. As I mentioned, it seems to suffer from other
inexplicable erratic misbehaviors.

Why not add a debug.print line in your code similar to what
you are putting in the statusbar. I expect both will update
similarly.

I meant to add that I am not looking for a work-around. I know
several. I am seeking insight into this particular problem.

And I suspect my WAG is wrong. A tight loop simply updating
the status bar seems to work just fine. If anything should suffer
from competition for CPU cycles, I suspect this would even more
so than my original example.

sub testit()
nloop = 0
do
nloop = nloop + 1
application.statusbar = nloop
loop while true
end sub

I only tried your sample code a couple of times. However I have used the
statusbar extensively to update progress and never encountered the problem
you describe. Updating the Statusbar does not require anything like repaint
(not that you could) or doevents and changes display even with
screenupdating disabled.

Your problem might be related to use of the Timer function which returns
whole seconds since midnight. Might be better to use an API, eg
Gettickcount, or if this is to update progress of your code update in terms
of %age or volume done (but not in every loop).

As I suggested before, why not try a debug line in your code to verify the
timer loop functions as expected, eg

debug.? application.statusbar

Regards,
Peter T

" wrote
in message ...
"Peter T" wrote:
Your code worked as expected for me in xl2000.

I'm curious: how many times did you try? As I wrote before,
the misbehavior is erratic. Originally, it seemed to fail most
of the time -- in fact, I thought it "always" failed. But as I
continued to tweak the example, it seemed to misbehave less
often -- perhaps 1 in 5 times.

I really do suspect this has more to do with my system than
with Excel. As I mentioned, it seems to suffer from other
inexplicable erratic misbehaviors.

Why not add a debug.print line in your code similar to what
you are putting in the statusbar. I expect both will update
similarly.

I meant to add that I am not looking for a work-around. I know
several. I am seeking insight into this particular problem.

And I suspect my WAG is wrong. A tight loop simply updating
the status bar seems to work just fine. If anything should suffer
from competition for CPU cycles, I suspect this would even more
so than my original example.

sub testit()
nloop = 0
do
nloop = nloop + 1
application.statusbar = nloop
loop while true
end sub

"Peter T" wrote:
As I suggested before, why not try a debug line in your
code to verify the timer loop functions as expected, eg
debug.? application.statusbar

Good idea! I misunderstood your previous suggestion.

The debug.print output shows that application.statusbar
changes as intended every interval, even when the status
bar updates "stall".

Your problem might be related to use of the Timer
function which returns whole seconds since midnight.

Yes, I discovered that later. I was following a Walkenbach
example for measuring time intervals. Nonetheless, I was
not executing across midnight, and I believe my system clock
was valid during all executions, if those are the potential
problems you are alluding to. If not, please elaborate.

Also, I failed to mention that the "done" message always
showed the expected "n" for the interval size, even when
the status bar updates "stalled". So I was "sure" that Timer
returned the correct values (increasing time) -- a fact that I
can now prove with your debug.print suggestion.

Aside: On my system (XP Pro 2002 SP2 with Excel/Office
2003), Timer is a "single" with apparently millisecond
resolution, not "whole seconds". Of course, no telling how
often that time measurement is updated. On Unix systems,
gettimeofday() claims to show microsecond resolution, but
the "softclock" is usually updated only every 10 msec on
some Unix systems.

Might be better to use an API, eg Gettickcount

Good idea. I recall that I stumbled across something like
this somewhere -- as well as a variable, I think. I thought
it was a VBA thing. But, sigh, I cannot remember the exact
names, and I cannot find anything like this now in VBE Help.
In fact, I might have stumbled across them during a google
search, and they might simply have been someone's
implementation around the gettickcount API. I wish I had
paid closer attention.

I would prefer not to use an API, if there is a VBA equivalent.
I suspect there is greater potential for compatibility problems
-- albeit not likely for gettickcount() per se. So if anyone
knows a VBA equivalent, I would appreciate hearing about it.

In the meantime, thanks for the pointer to gettickcount().
I just have to be sure that Windows does not run for more
than 24 days in order to avoid rollover problems :-).