Multithreading bug?

  • Thread starter Thread starter PaulH
  • Start date Start date
P

PaulH

I have an Excel file that appears to crash Excel 2007 because of the
multithreading feature in xl2007. It opens fine in xl2003. I can open it in
xl2007 if I first set the recalculation mode to manual, but xl2007 crashes as
soon as I do a recalc. If I disable the multithreading option before a
recalc, it works fine. I can then save it as an xlsx or xlsm file, close it,
reopen it, enable multithreading, and recalc, but a rebuild of the
calculation tree (Ctrl-Alt- Shft- F9) crashes Excel.
I have 2 questions:

1) Is there a known bug in xl2007 that would cause this? For what it’s
worth, there are a large number of OFFSET array formulas in the file, and I’m
guessing xl2007 thinks they are multithread safe, but they really are not.

2) Is there any way to keep the file in xl2003 format but deal with the
multithreading problem if a user opens it in xl2007? I have tried a
Workbook_Open sub that checks the Excel version and disables
MultiThreadedCalculation if it is xl2007, but Excel crashes before the sub
can run.
 
I have some very large workbooks using OFFSET very extensively and they do
not cause XL 2007 to crash: I am not aware of a known XL 2007 bug like this
(but that does not mean that it does not exist!).

One thing to try would be rebuilding the calculation tree using Excel 2003.

You could probably bypass the problem by having a Personal.XLS (or .XLSM
etc) that switches off Multi-threaded Calculation, and then having a
workbook Open sub that switched it back on in any workbooks where you wanted
to use it.

If you are able to send me a zipped copy of the file I would be happy to
take a look at it.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
Charles,

I rebuilt the dependency tree in xl2003, but it still crashes xl2007. Also,
I have replaced all the offset formulas with non-volatile functions (index,
simpler sumproducts, mmult) and it still crashes xl2007, so I guess the
offset formulas are not the problem.

I appreciate your offer to look at the file - I'll shoot you a copy.

Thanks,
Paul
 
For anyone finding this later, it apparently is a pointer bug in Excel 2007,
probably in the INDEX function. Thanks very much to Charles for confirming
this and pointing me to the offending formulas. I replaced them with
equivalent OFFSET functions, and now it is working fine.
 
Gents,

Just a short note to say thank-you for posting this thread, and, in
particular, Paul, taking the time to add the solution at the end.

I have run into exactly the same issue, working on macros in Excel 2003 for
a client using 2007. Whenever the calculation was attempted, Excel 2007 gave
up and 'had to close'. I've laboriously replaced all the INDEX functions
with OFFSETS, but it now seems to be working fine. I don't understand why,
but at least it works!

Best regards,


Simon
 
Back
Top