[Phillyguy36]

Megadyne, two suggestions may be helpful:

You can control the sequence of calculations by calcing a specified range or worksheet. That will override the Excel order of calculations. It does require some care in determining the proper sequence.

Instead of the F9 button, set up a giant user "calc" button that accomplishes the same thing. And the button can call a calc macro that controls the sequence of calculations.

[Megadyne]

Thanks Phillyguy.

I had thought about a Calc button and that may yet be put in place. However, there are more fundamental problems that I need to sort out, if only for my own satisfaction, Your post has reminded me that I should feed back some progress so far, and this is it.

First point, I tried all the suggestions made above. They are all worth doing because I can see that they improve the style and quality of my work - but in fact they made no difference to the calculation time.

Except: thanks Nesr! Closing the VB editor certainly makes a difference! Although it's still too slow. :-(

Next, I found what I think is an 'artificial' circular reference in the computation order. One part of the whole workbook consists of an array of input data in columns, with a set of aggregating computations in the rows below the input data. Users add a new column of data, typically every day for 3 months or so. The aggregating formulas depend on the input data above them and on the previous column. So, working from left to right and top to bottom, there is no circular reference. However, if a user edits the input data in the middle of the table, then there are dependencies both to left and right. To add to the mix, there is a cell at the top of each data input cell that validates the input data in a rather complicated way. Working through the data (and validity checks) by hand - and with the eye of a human - there is a straightforward left-to-right calculation order with no circular references. By trial and error I discovered that Excel seems to create a recursive loop and recalculates many times, before, I suppose, hitting some threshold and dropping out - but with no warning to the user. In the end it gives the correct answer, though. Guess what? This was giving a bottleneck in the sequence. The solution was to replace the validation checks (which I had written in to a VBA UDF and called in the worksheet) with something that was based entirely in the worksheet, with no UDFs. The check is weaker, but it speeded up the sums by at least an order of magnitude.

The second big improvement was in some coding that was badly written. There are some intermediate calculations involving moderately complex arithmetic which I had written in to UDFs and called in an intermediate sheet. Easy to read, check and maintain, but slow because the UDFs were called hundreds and hundreds of times. I replaced the UDF by a large table of cells in a hidden worksheet, which again did the arithmetic using worksheet formulae only.

These two changes have made the calculation speed acceptable, but all this work - and your suggestions - have shown me that there are still some issues that I need to resolve and I shall probably use FastExcel to do the analysis - unless anyone can suggest an Open Source/freeware product that will do the job?

Again, thanks to everyone who has contributed to this thread. Your suggestions have been very helpful.

[Zack Barresse]

IMHO FastExcel is well worth it.