Excel 2007 multi-threading bug with Range.SubTotal and XLL

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.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s