Excel List-Object VBA Performance Bug?

Question

I have an issue with performance on an excel application which uses List Objects (AKA Excel Tables). I suspect it may be a bug, but despite my Googling I could not find any reference of it. I've already developed a workaround for my application, but what I’m interested in is if anyone can give any insight into why this happens.

Note: I’m using Excel 2007 on Windows Vista. The setup is as follows: I have a spreadsheet which holds data in a List Object, with VBA code which can be kicked off via a command button; this code may make several edits to any number of cells on the worksheet, so Excel’s Calculation mode is set to Manual prior to any edits.

The problem I’ve encountered is that if the currently active cell is within the List Object, then setting the Calculation Mode to manual seems to have no effect whatsoever. So if a user happens to have a heavy calculation workbook open in the same instance, then the VBA code runs very slowly. I practically had to pull my application apart to discover that this was caused by the active cell; and I created a new workbook with simple version of this scenario to confirm that there wasn’t some sort of corruption on my application.

I’ve been doing a number of test cases with this, and below are the results from what I’ve found:

1. Although it seems generally related to the calculation, there is still a time difference when the calculation mode is switched between Manual and Automatic...

   • Manual = 7.64 secs
   • Automatic = 9.39 secs

   Manual mode is just fewer than 20% faster than Automatic. But my expectation was they’d be more or less the same, considering the issue seems to be the calculation kicking off even when in Manual mode.

2. Compare that to when the active cell is not on a List Object, and the results are vastly different...

   • Manual = 0.14 secs
   • Automatic = 3.23 secs

   Now, the Manual run is 50 times faster, and Automatic run shows that the calculation shouldn’t have taken any more than 3.2 secs! So now the first test looks like it might have run the Calculation twice while in Manual mode, and nearly 3 times while in Automatic mode.

3. Repeating this test again, this time in an instance with no calculation formula in any cells, and suddenly it doesn’t seem as bad,

   • Active cell is List Object & Calc is Manual = 0.17 secs
   • Active cell is List Object & Calc is Automatic = 0.20 secs
   • Active cell is Empty & Calc is Manual = 0.14 secs
   • Active cell is Empty & Calc is Automatic = 0.18 secs

   It’s still slower, but now it’s only by 10-20%, making it unnoticeable. But this does show that the issue must be related to the Calculation in some way, as otherwise it should have taken just as long as the first test.

If anyone wants to create these tests to see for themselves, the setup is as follows:

• New Workbook with a List Object added (doesn’t have to be linked to any data)
• Add some formula that will take excel a while to calculate (I just did ‘=1*1’ repeated 30,000 times)
• Write a quick VBA code which will; (i) loop through a simple edit of a cell several hundred times, (ii) and record the time it took
• Then just run the code while changing the active cell between the List Object and an empty cell

I’d be very interested to hear if anyone can explain why Excel behaves in this way, and if is a bug or if is some feature to do with List Objects which actually has some genuine use?

Thanks, Stuart

Answer 1

Regarding Table performance (and performance, in general):

I know this is an old question, but I want to get this documented.

One thing that changed between older versions of Excel and the post-2007 versions is that Excel now activates the target sheet of any PasteSpecial operation. You cannot override it by turning off ScreenUpdating and making calculations manual. Such Activation WILL make the sheet visible, and cause uncontrollable flicker.

My original VBA code ran very fast on an old, single-processor XP box running Excel 2000. The change to Excel 2013 on a modern machine was stunning in the terrible slowness of code execution. The three areas that kill performance are PasteSpecial from one sheet to another, any other code that requires activating sheets (Zoom level, Advanced Filter, Sheet-Level range names, etc), and automating sheet protection/unprotection.

This is too bad, because PasteSpecial helped "cleanse" data you copy (Direct use of .Copy to a target will throw the occasional error).

So you need to review your code and make sure you are using direct assignment to the right property for the data type you need (from among Value, Value2, Text, and Formula, for example), instead of PasteSpecial.

e.g. .Range("MYRANGE").Value = .Cells(5, 7).Value2

You also need to be scrupulous in resisting use of Select and Activate throughout your code.

As referenced above, many comments you'll find in Excel fora about that last point will make statements that you "never" need to use Activation, which is clearly untrue, since several things in Excel only apply to or require active sheets. Understanding the cases where activation is forced automatically by a particular method or use of an object will help in coding as well. Unfortunately, you won't see much in the way of documentation of this.

Update:

Regarding Conditional Formatting, you'll find many complaints in various fora about the slowness of Excel when encountering a large number of Conditionally-formatted cells. I suspected this would impact Excel Tables since they have many table format options. To test this, I took a large workbook we use that is currently formatted as several worksheets with the same style of Excel Table on them.

After converting the tables to a conventional range, I noticed no difference in speed of code execution. This would seem to indicate that use of Excel Table formats is far superior to conditionally-formatting your own arrays of cells.

Answer 2

This is not relative to the "bug" you found, which is quite interesting and intriguing.

I just want to share that there is a great way to avoid calculation delays. I had fantastic results with this and now I use it all the time.

Simply put, Excel takes a long time copying data back and forth between the "VBA world" and the "spreadsheet world".

If you do all the "reads" at once, process, and then do all the "writes" at once, you get amazing performance. This is done using variant arrays as documented here:

http://msdn.microsoft.com/en-us/library/ff726673.aspx#xlFasterVBA

in the section labeled: Read and Write Large Blocks of Data in a Single Operation

I was able to refactor some code I had that took 5 minutes to run and bring it down to 1.5 minutes. The refactoring took me 10 minutes, which is amazing because it was quite complex code.