Why doesn't status bar update consistently?

[joeu2004]

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"

 

[joeu2004]

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]

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

 

[joeu2004]

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]

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

 

[joeu2004]

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 .

 

[Peter T]

Are you saying the debug results are not consistent with how you see the
statusbar change - if so I'd be perplexed!

New API's are sometimes introduced with new Windows OS, and calling in a
Windows OS that does not support the API will invoke nasty problems that
cannot be trapped with an error handler. However Gettickcount has been
around at least since W95. API's don't work in Mac. Do you know of any
limitations with Unix?

Rollovers, if T1 > T2 then !!

Regards
Peter T

 

[Peter T]

PS - forgot to add - don't think any VBA time related function returns a
greater degree of precision than 1 second.

Peter T

 

[joeu2004]

PS - forgot to add - don't think any VBA time related
function returns a greater degree of precision than 1
second.

Easy enough to prove:

1. In the VBE Immediate Window, type "? Timer". I see
fractional seconds.

2. Construct and execute the following macro:

sub testtimer()
curTime = Timer
MsgBox curTime & " " & Int(curTime) & " " & _
(curTime = Int(curTime))
end sub

My output is "55672.98 55672 False"

3. RFTM. The VBE Help text for "timer function" says:
"In Microsoft Windows the Timer function returns fractional
portions of a second."

QED.

Ah, but the VBE Help text also states: "On the Macintosh,
timer resolution is one second." Perchance are you using
a Mac?

Not I. I guess I need to be more careful about specifying
the platform I am running on. When I said that I am using
XP Pro 2002 SP2 with Office Excel 2003, I ass-u-me-d that
implies I am using a "non-Mac" computer. Right? YMMV
if you are using any other platform. Let's not compare
apples and oranges.

Aside: Hmm, what is the correct term for "non-Macs" --
meaning Intel-compatibles? I think some people use the
term "PC" for that purpose, but that is not, uh, "PC"
(politically correct). A Mac is just as much a PC (personal
computer) as an Intel-compatible computer. And not all
Intel-compatible computers are truly "PCs". I would not
call an Intel-compatible server a "personal" computer.

Also, I wonder if Timer behaves differently in older Excel
revisions. I believe you said you are using "xl2000",
whereas I said I am using Office Excel 2003.

 

[Peter T]

I stand corrected - Timer does indeed return fractions of a second in any xl
version in Windows, to two d.p. it seems.

Regards,
Peter T

 

[joeu2004]

Are you saying the debug results are not consistent with
how you see the statusbar change

Yes, in so many words. The debug.print output shows every
intended change to application.statusbar, whereas the status
bar does not (sometimes).

if so I'd be perplexed!

Welcome to my side! But this has not been a total waste
of time. At the very least, I hope that your debug.print
idea dispels any lingering doubts about the test macro and
its results, as I reported them.

Now if only a knowledgable person can explain why status
bar updates (sometimes) fail to reflect the change to
application.statusbar.

The only explanation I can think of -- besides a defect -- is
that the status bar update is asynchronous, whereas the
debug.print is not.

 

[Peter T]

OK I'm perplexed!

In general VBA is not asynchronous though obviously it may interact with
other operations that are.

The debug line at least proves the statusbar property is being written, as
expected, so for some reason it seems it is not consistently re-drawn. In my
experience it always does even with screenupdating disabled (though there is
an API which completely locks all screen re-draw which I doubt you are
using).

I can't re-create your problem but hopefully someone else may shed some
light on it.

In the meantime does this work - your function modified to use Gettickcount

Public Declare Function GetTickCount _
Lib "kernel32.dll" () As Long

Sub timeit2()

limitTime = 20 * 1000: interval = 1 * 1000
Application.StatusBar = _
"00:00 Start limit=" & limitTime & " intvl=" & interval

startTime = GetTickCount: lastTime = startTime
n = 0: nLoop = 0


t = GetTickCount
Do
curTime = GetTickCount: nLoop = nLoop + 1

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

Regards,
Peter T

 

[joeu2004]

In the meantime does this work - your function modified
to use Gettickcount

I had already gone and implemented a Gettickcount version
of the loop. And yes, it fails sometimes, too.

However, it does __seem__ to fail less often -- although
I really have not done enough test runs to say that with
impunity.

Comparing tight loops around Timer and Gettickcount, it
appears that the Timer loop (in the benchmark, not in the
original test macro) takes 5X more time to execute. Some
of that difference might be due to "single" floating point
arithmetic using Timer v. "long" integer arithmetic using
Gettickcount. But I "conjecture" (WAG) that some of that
difference might be due to increased time in the Timer
function (and kernel APIs it might call) compared to time
in the Gettickcount function.

I suspect the huge time differential might explain why the
Gettickcount version __seems__ to be more reliably --
though still not completely reliable. But I reiterate that the
assertion that it is more reliable is speculative since I have
not (and will not) do the kind of statistical testing that is
necessary to test the hypothesis.

This is really as far as I'm going to take this digression. I
really am looking for an explanation of the status bar problem.

 

[Tom Ogilvy]

I ran it more than 20 times in xl2003 (US English), Windows XP with no
problems

Likewise, more than 20 times in xl97 (US English), Windows 98SE with no
problems.

 

[joeu2004]

I ran it more than 20 times in xl2003 (US English),
Windows XP with no problems

That is useful information, insofar as it might point to something
odd that is happening on my laptop -- which I have suspected
for a long time. But in order for me to know what the hardware
problem might be -- more specifically, in order for me to vet
what the manufacturer tech might claim the problem is -- , I
need some insight into what might cause the status bar not to
reflect changes to application.statusbar. For example, is my
theory about asynchronicity (process or kernel threads) correct?
Could missing or spurious interrupts (which?) cause this
symptom?

Does anyone know of any other additional debugging
approaches or tools that help me isolated the root cause?

And actually, before I leap to that conclusion (hardware problem)
-- which will be very hard to prove to the manufacturer, I'm
sure -- I guess we need to compare software revision/updates.
But what software is relavent: Excel, Windows, anything else?

For me:
Office Excel 2003 11.5612.5606
Win XP Pro 2002 SP2

Question: For Win XP, where can I find more specific revision
information, such as latest patch level? Or would that be
reflected in "My Computer" Properties, if indeed I had any?

And Tom, which did you run: the Timer macro, the
Gettickcount macro, or both? My experience is that the
Timer macro __seems__ to expose the problem more easily.

Thanks again for the feedback. I would like to hear from
others who try the experiment.

 

[Tom Ogilvy]

I ran the initial code you posted unchanged. So "the Timer macro"

From System Info in xl2003 help:

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 1 Build 2600

For Excel:
Version 11.0
Build 6355

From the About dialog:
Microsoft Office Excel 2003 11.6355.5408 SP1

 

[joeu2004]

I ran the initial code you posted unchanged.
So "the Timer macro"
From System Info in xl2003 help:

[... snip ...]

Thanks. Immediately after I posted my diatribe about possible
"hardware problems", I realized just how silly a jump to a
conclusion that is. I have been having spurious problems with
my not-so-new-anymore laptop, and I am eager to find a smoking
gun. However, this is not it. There can be oh-so many things
different between any two PCs that could mask a common
software problem on one of them. For example, simple timing
differences due to CPU speeds, not to mention the many other
things that alter timing of internal events.

I would like to submit a no-charge email to MS support. But I
try, MS discovers that my PC manufacturer provides support
(for a fee :-<), so MS refuses to allow me to use the no-charge
service :-(.

I guess this just be one of life's many mysteries ;-).