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/