Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel hangs up. | Excel Discussion (Misc queries) | |||
Excel Hangs | Excel Discussion (Misc queries) | |||
DDE Startup Hangs | Excel Programming | |||
DDE startup Hangs | Excel Programming | |||
ddeinitiate Hangs | Excel Programming |