On 01/08/2011 16:17, kittronald wrote:
Clif,

Thanks for the in-depth help.

I added the code, but I'm not sure there's a difference - although my
design might have something to do with that.

Using Excel 2007, I have an .xlsb workbook that contains two worksheets.

On Sheet1, there are about 1,000,000 cells that each contain sixteen
nested formulas.

1) In each cell, there's an initial IF test that determines whether the
next of three other nested IF formulas should run.

2) Each nested IF formula contains four other function calculations. If
the first IF formula evaluates TRUE, then calculation for that cell stops.
Otherwise, the second IF formula runs and if it evaluates FALSE, the last IF
formula runs. So in total, there's a maximum of about 16,000,000
calculations.

On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE / FALSE
value. That value is what the initial IF formulas on Sheet1 use to determine
if the formulas should further calculate.

I presume you have forced xlCalculation to manual whilst you update the
formulas and then want to start the full calculation running and detect
when it has finished. Forcing a fresh recalculation of all cells with
screen updating either turned off or on. I have found for some perverse
reason that XL2007 can sometimes be faster with screenupdating on!!!

One way to test for completeness is to have a some cells with a formula
at the last point to be computed (usually the last row, but this may not
be guaranteed these days with multicore & multithreading).

eg. A1 = RANDBETWEEN(1000000), A2 = RANDBETWEEN(1000000)

And right at the end of the sheet A9999=A1+A2

Then in VBA you spend most of your time sleeping or giving other threads
priority and once a minute or so you test to see if Cell A9999 contains
the result of the most recent pair of random numbers.

This may not be foolproof. Alternatively query the machine performance
and computer a conservative estimated time to completion and then wait.

So on Sheet2, choosing the ON radio button triggers the formulas on
Sheet1 to calculate. But since Sheet2 has the focus, screen repainting
doesn't occur.

The biggest bottleneck appears to be a third party COM add-in that
provides four custom functions in each of the three nested IF formulas.

Apparently, the COM application that returns the evaluated data takes too
long to do so.

This appears to cause all of the nested IF formulas to evaluate which is
why it takes so long up to 3.5 hours in total.

If there's a way to speed that up, I'd like to know.

Your best bet might be to cache locally all the answers that the COM
database has already provided and look in the cache first. This only
helps if you can implement a caching algorithm that is faster than the
original lookup - and in VBA this is doubtful.

Counting the number of fetches made by COM lookup might be enlightening
- it is possible that the nested IF statements are actually executing
more paths than you think. CHOOSE might be faster than nested IFs if you
can cast your computation into the right form to use it.

Until then, I'll be hoping The Great Pumpkin at Halloween will bring me a
new Intel octo-core hyper-threaded computer.

Looking carefully at the structure of the code is your best bet.

Regards,
Martin Brown