a trick to speed things up

[mhume]

Not sure if this is common sense or not, but considering my excel macro's have gotten rather monstrous lately I started using a trick to speed things up.

' put this at the top of your code
Application.Calculation = xlManual

' put this at the end of your code
Application.Calculation = xlAutomatic
Calculate

For all my routines that manipulate formulas in my worksheets, I have seen HUGE reductions in load time from doing this. In some cases, from a couple minutes down to a few seconds. I think this trick is pretty much mandatory for any complex spreadsheet manipulations.

Just thought I'd share.

 

[Norie]

This method is common practice along with others.

Another one is to turn off screen updating.

Application.ScreenUpdating = False
   ' rest of code
Application.ScreenUpdating = True

If your code has become 'monstrous' then it might be time to take a good look at it and see if it can be optimized/cleaned up.

How exactly that would be done is hard to tell without seeing the code or a sample of it.

And an explanation of the code's purpose wouldn't go amiss either.

 

[Andrew Fergus]

jonmo had some other tips here:
http://www.mrexcel.com/forum/showpost.php?p=1678819&postcount=39

per the index the section on speeding up VBA code is quite a way down that post.

Andrew