Solved: Macro processes too much, so Office apps hang until finished! Ideas?

[rgmatthes]

I have a master macro doing lots of things, and it takes about 3 minutes to run. The macro itself runs just fine, everything works. But while it is running, users can't do anything else in any Office application. Outlook freezes, Excel and Word are useless... and I'd like to fix this if possible.

...but I have no idea where to start! Is there any way to restrict how much processing power macros can use up so my users don't have to just wait around until the macro is finished to check their e-mail?

I don't think this is a code issue... maybe a "coding structure" problem? So here are some details:

I'm running Win7 and Office 2010 (which is nice, btw).

I have three forms and one module in my project. Form 1 collects info and saves it to variables, which is used to auto-generate some of the fields in the next form, form 2. When the user completes form 2, form 3 activates, which makes a series of calls to various subroutines stored in the module. There are 14 subroutines in all, and each is ran one after the other with calls. These subroutines manipulate the active file in various ways, drawing upon the variables from forms 1 and 2. When all 14 subroutines are done, parts of form 3 fill themselves out to display a few stats, and a button makes itself visible for exiting.

Any ideas? THANKS A MILLION!
:beerchug:

[Paul_Hossler]

Try adding a call to DoEvents between tightly called subroutines and see if that gives the other programs a chance to run

From Help:



DoEvents
Yields execution so that the operating system can process other events

Paul

[parry]

Hi in addition to DoEvents you may want to think about how you can optimize the speed.

1) Reference cells directly rather than selecting them, use Ws.Range("A1").Value = "xyz" rather than Selection.Value = "xyz"
2) Use Application.ScreenUpdating=False before changes that update cells then turn ScreenUpdating back on at the end of the procedure
3) You might want to consider using arrays -these can be quicker than looping through cells.

regards,
Graham

[Mis_Sailesh]

Please see:-
Are the form 1 and form 2 unloaded or just kept hidden.
If not unloaded please do so.
:help

[mdmackillop]

A similar issue was discussed here (http://www.vbaexpress.com/forum/showthread.php?t=29382&highlight=instance)

[rgmatthes]

All great stuff! DoEvents did the trick, but it looks like I forgot to unload the old forms, too! Thanks guys! :)

[Mis_Sailesh]

:) Enjoy