Hi, I've noticed that whenever I write anything that requires significant CPU
resource in VBA (either through excel or word) the program hangs if I choose
to invoke another program while it runs in the background. As an example,
create a simple form with a progress bar and then initiate a loop which
counts from 1 to 10,000,000 and update the progress bar based upon the value
of the loop.

Once the program starts running, move down to the task bar (I'm using XP
with all the latest patches) and you'll find that excel and your program code
stops responding and doesn't come back unless you force a close of excel.
The same applies if your screen saver kicks in.

This is quite frustrating for any intensive processing that goes on and I'd
appreciate other people's comments and fixes that you may be aware of.

Thanks in advance for replies.

Peter

First, when you say Excel doesn't come back, how much time are you giving it?
It is going to take a long, long time to loop 10,000,000 times even for a
fast computer. What if you Ctrl-Break? Do you get into debug? Can you test
the value of your loop variable and see if it is continuing to grow? A true
"hung" app (at least by my reckoning) is one that is lost forever in an
endless loop or an error condition; otherwise it may be busy and task manager
may say "Not Responding" but the app is actually working (working badly,
maybe, but still working) and will eventually respond when it is finished its
processing.

Excel will appear to "hang" while your code runs since your sub takes
precedence over other Excel processes - Excel cannot respond while your code
is in control. So the typical scenario is this: You start your code. What
you see is the Excel screen the way you left it. You switch to another app
(or another one like a screensaver takes control of the screen). When you
try to switch back you either see no response or a partially painted screen
(maybe just the title bar and window frame, with blank space, your desktop or
even parts of other apps showing through). This is because you have actually
switched to Excel, and it is actually working, but your code is still busy
and is "in control" of Excel, so the normal Excel processing (including
redrawing the window) cannot occur. But you should be able to Ctrl-Break and
get into debug still, and if your code is properly written so you don't have
any endless loops or other problems it will eventually finish and control
will return to Excel. It just may take a long, long time.

If you have not included a DoEvents statement somewhere in your loop, try
it. Do Events passes control back to Excel momentarily so it can process any
of its normal events and will give Excel a chance to "respond" to the user or
other Windows processes. Whenever you have code that will take a long time
to process it is a good idea to have DoEvents in there - at the very least it
prevents users from panicking when they see "frozen" or unpainted Excel
windows.
--
- K Dales


"sponny" wrote:

Hi, I've noticed that whenever I write anything that requires significant CPU
resource in VBA (either through excel or word) the program hangs if I choose
to invoke another program while it runs in the background. As an example,
create a simple form with a progress bar and then initiate a loop which
counts from 1 to 10,000,000 and update the progress bar based upon the value
of the loop.

Once the program starts running, move down to the task bar (I'm using XP
with all the latest patches) and you'll find that excel and your program code
stops responding and doesn't come back unless you force a close of excel.
The same applies if your screen saver kicks in.

This is quite frustrating for any intensive processing that goes on and I'd
appreciate other people's comments and fixes that you may be aware of.

Thanks in advance for replies.

Peter

Thank you very much, K Dales, the inclusion of the DoEvents within the loop
does the job! My forms were indeed freezing and the progress bars that they
contained were no longer being updated if you chose to, for example, take a
look at the task bar at the bottom of the screen. The inclusion of the
DoEvents fixed it great. Thank you!

"K Dales" wrote:

First, when you say Excel doesn't come back, how much time are you giving it?
It is going to take a long, long time to loop 10,000,000 times even for a
fast computer. What if you Ctrl-Break? Do you get into debug? Can you test
the value of your loop variable and see if it is continuing to grow? A true
"hung" app (at least by my reckoning) is one that is lost forever in an
endless loop or an error condition; otherwise it may be busy and task manager
may say "Not Responding" but the app is actually working (working badly,
maybe, but still working) and will eventually respond when it is finished its
processing.

Excel will appear to "hang" while your code runs since your sub takes
precedence over other Excel processes - Excel cannot respond while your code
is in control. So the typical scenario is this: You start your code. What
you see is the Excel screen the way you left it. You switch to another app
(or another one like a screensaver takes control of the screen). When you
try to switch back you either see no response or a partially painted screen
(maybe just the title bar and window frame, with blank space, your desktop or
even parts of other apps showing through). This is because you have actually
switched to Excel, and it is actually working, but your code is still busy
and is "in control" of Excel, so the normal Excel processing (including
redrawing the window) cannot occur. But you should be able to Ctrl-Break and
get into debug still, and if your code is properly written so you don't have
any endless loops or other problems it will eventually finish and control
will return to Excel. It just may take a long, long time.

If you have not included a DoEvents statement somewhere in your loop, try
it. Do Events passes control back to Excel momentarily so it can process any
of its normal events and will give Excel a chance to "respond" to the user or
other Windows processes. Whenever you have code that will take a long time
to process it is a good idea to have DoEvents in there - at the very least it
prevents users from panicking when they see "frozen" or unpainted Excel
windows.
--
- K Dales


"sponny" wrote:

Hi, I've noticed that whenever I write anything that requires significant CPU
resource in VBA (either through excel or word) the program hangs if I choose
to invoke another program while it runs in the background. As an example,
create a simple form with a progress bar and then initiate a loop which
counts from 1 to 10,000,000 and update the progress bar based upon the value
of the loop.

Once the program starts running, move down to the task bar (I'm using XP
with all the latest patches) and you'll find that excel and your program code
stops responding and doesn't come back unless you force a close of excel.
The same applies if your screen saver kicks in.

This is quite frustrating for any intensive processing that goes on and I'd
appreciate other people's comments and fixes that you may be aware of.

Thanks in advance for replies.

Peter