OK: this is a fairly obscure Excel 2007 only bug but I thought I should place it on record.
The conditions required for the bug are:
- Excel 2007 (tested with 12.0.6683.5002 SP3)
- Automatic calculation Mode
- Multi-threaded calculation is on and the system has multiple cores
- XLL multi-threaded worksheet functions are being used
- A VBA routine uses Range.SubTotal to create subtotals
The bug symptoms are
- Either Range.subtotal fails and Excel becomes unstable
- Or Range.subtotal seems to work but Excel becomes unstable
This bug seems to have been fixed in Excel 2010 and later versions.
Bypassing the bug
If your VBA switches off Multi-threaded calculation (Application.MultiThreadedCalculation.Enabled = False) just before doing the Range.SubTotal and then switches it back on again the bug is bypassed.
Whats causing the problem
Using Range.subtotal is triggering a calculation event. VBA always runs on the main thread, but the XLL multi-threaded functions can run on any thread.
So presumably the problem happens when the XLL function is not calculated on the main thread and tries to return a result to Excel, but Excel is not ready to accept it, thus a portion of memory gets overwritten.