Speed up Excel execution
 

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

Speed up Excel execution
 

Don't know about your cutting and pasting - I can't imagine what the
difference would be. Different XL versions use somewhat different
algorithms in the calculation engine, so it may be version dependent too.

See

http://www.decisionmodels.com/optspeed.htm

for tips on optimizing speed.

In article ,
Sinus Log wrote:

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

Speed up Excel execution
 

Sinus,

A few things:
1. For VBA you are better off with Long rather than integer variable types.
2. You can export and import using Rob Bovey's Code Cleaner add-in. It's
very useful.
3. Comment everything that might need commenting. The time lost in
calculation is minimal compared to the problems you'll face when you come
back in years to uncommented code.
4. VBA is interpreted when it runs, not compiled.

Here are a couple of links on calculation algorithms that might explain
what's going on:

http://msdn.microsoft.com/library/de...c_xlrecalc.asp

http://www.decisionmodels.com/calcsecretsc.htm

Robin Hammond
www.enhanceddatasystems.com

"Sinus Log" wrote in message
...
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

Speed up Excel execution
 

You already have a lot of good general advice about speeding things up.
However it seems there's something else going on, perhaps unnecessary
looping or processing of your own code, something calculating in the
workbook or a combination.

Any events being triggered - disable, or udf's - calc to manual (ensure no
possibility of not getting reset when done due to errors). If disabling
speeds things up find out why, can things be changed to avoid disabling.

Might be worth adding a debug log. Set a global boolean debug flag and call
a routine to either debug.print or write to a file, say at start/end of
proc's and loops etc, and of course record time (try the GetTickCount API).

If bLog then meLog "ProcName " & OtherInfo
Also perhaps record sheetnames & large range address's being processed, if
applicable.

Regards,
Peter T


"Sinus Log" wrote in message
...
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

Speed up Excel execution
 

Peter T wrote:
You already have a lot of good general advice about speeding things up.
However it seems there's something else going on, perhaps unnecessary
looping or processing of your own code, something calculating in the
workbook or a combination.

Any events being triggered - disable, or udf's - calc to manual (ensure no
possibility of not getting reset when done due to errors). If disabling
speeds things up find out why, can things be changed to avoid disabling.

Might be worth adding a debug log. Set a global boolean debug flag and call
a routine to either debug.print or write to a file, say at start/end of
proc's and loops etc, and of course record time (try the GetTickCount API).

If bLog then meLog "ProcName " & OtherInfo
Also perhaps record sheetnames & large range address's being processed, if
applicable.

Regards,
Peter T


"Sinus Log" wrote in message
...

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



Thanks to all for the info. I'll need a lot of time checking
and modifying my workbook. There are so many tests to do.
I'll post a feedback as soon as possible.

Speed up Excel execution
 

I've done a lot of progress since my last post. Execution
time dropped from 13 seconds to 4.5. It's still too long,
and I have some ideas to test. Meanwhile, I thought I'd
write a list of things that helped me speed up my workbook.
I found most of them by reading your posts, by looking up
the links you gave me, and by searching Google Groups. I
reproduced the links below.

I wish to point out that 95% of the speed gain resulted from
the 2nd and 3rd items in the list: declaring some arrays
Public, and minimizing calls between worksheets. For the
former, I can still go further but there's a difficulty. I
posted about it in another thread in the same newsgroup, on
December 6: To detect changes in a worksheet. I would
greatly appreciate any help with that.
------------
The following list is certainly not exhaustive. It's rather
what seems most helpful to me. I hope there are no gross
errors in it. Any comment will be viewed as a way to improve
myself.

- Knowing about the calculation sequence is interesting, if
not very helpful in practice.
There is a first pass, where Excel builds a Dependency Tree
to optimize the calculation sequence. Something like:
Sheet1-Cell1 depends on Sheet2-Cell2 which depends on
Sheet3-Cell3 which didn't change... The tree can contain
65,536 entries. If that limit is reached, calculation time
can stretch. Worksheets are scanned in the alphabetical
order of their names, and inside a worksheet, cells are
scanned from top left to bottom right.
In the next pass, or passes, cells are calculated in a
sequence derived from the Dependency Tree.

- Variables and arrays can be kept in memory by declaring
them Public. This can reduce calculation time considerably.
But when the data (on which these public variables are
based) is modified, they must be reinitialized. The easiest
way to do it is to close and reopen the workbook.

- Minimize the calls between worksheets. More important: try
to avoid circular cross-reference paths (Sheet1-Cell1
calling Sheet2-Cell2, and Sheet2-Cell3 calling Sheet1-Cell4).

- Links to other workbooks are time-consuming. Check Edit
Links to see if there are links you're not aware of.

- When the type of a variable, array, or function is not
declared, it's considered Variant. That type requires more
memory and execution time than the others.

- Lookup functions are time-consuming. Keep them on the same
worksheet as the data they are looking up. Also, a Lookup
can be expressed in terms of an Index and a Match. If you
use many Lookup calls, you might be able to replace them by
one Match and many Index calls (Charles Williams has a lot
more to say about lookups).

- Try to keep remarks out of loops, where they would be read
(and rejected) repeatedly.

- When Calculation is set to Manual, use the following keys:
F9: to recalculate modified cells and their dependencies in
the workbook,
Shift-F9: the same, but in the current worksheet only,
Ctrl-Alt-F9: for full calculation.

- For macros, you can use Application.ScreenUpdating=False
at the beginning and Application.ScreenUpdating=True at the
end. But if the macro fails, remember that the last command
won't execute.

- When the VBA editor is open, calculation can be much slower.

- After editing a module, copy the code, delete the module,
create a module, insert the copied code. This can be done
automatically by Rob Bovey's VBA Code Cleaner.

Links
-----
Charles Williams' site:
http://www.decisionmodels.com/optspeed.htm
http://www.decisionmodels.com/calcsecretsc.htm

http://msdn.microsoft.com/library/de...c_xlrecalc.asp

Rob Bovey's site:
http://www.appspro.com/