Searching in Google Groups gave me some useful results about
speeding up execution:
- recalc off,
- application.screenUpdating=false,
- declare all variables, with integer the best and variant
the worst,
- after editing a module, copy the code, delete the module,
create a module, insert the copied code (I am going to do
it, although I can't believe it will change a thing),
- keep comments to the minimum (unbelievable too: since the
code is compiled, not interpreted - or is it ? -, the
comments are read just once),
- keep VBA closed (this one, I discovered by myself).
But I am sure there is something else: the order of the
sheets in the workbook, the order of the cell contents, and
things like that. Here is why.
I am working on a workbook with 5 sheets. Execution takes 13
seconds, whatever changes I make in the workbook, and even
if there is no change (pressing F9 repeatedly, for
instance). I tried something: I cut all cells in sheet2 and
pasted them at the bottom of sheet1. Now, when I make a
change in sheet5, execution takes half a second (but still
13 seconds when I make a change in the other sheets).
Reversing, that is cutting all cells from sheet1 and pasting
them at the bottom of sheet2, yields 13 seconds execution
time everywhere.
I'd be grateful if someone would give me some guidelines.
And also, I would appreciate a comment about the 2 things
I can't believe, above.
Thanks
Bookmarks