There was an interesting post on Stack Overflow recently about a performance problem when updating Excel 2007/2010 Tables/Listobjects. Certainly something strange is going on!
Duplicating the Problem
1. Create a table by selecting a few cells (I used A1:A3) on a sheet (Sheet1) and using Format as Table on the Home tab
2. On a different sheet (Sheet2) create a few thousand (I used 10000) of the simplest formula you can think of (I used =”A”)
3. Create a VBA Sub that updates a different cell in the same sheet as the table:
Sub Updater() Dim j As Long Dim dTime As Double Application.ScreenUpdating = False Application.Calculation = xlCalculationManual dTime = Timer For j = 1 To 10000 Worksheets("Sheet1").Range("d5") = j Next j MsgBox Timer - dTime End Sub
4. Select a cell within the Table
5. Run the VBA Sub
On my system that takes 8.8 seconds.
Bypassing the Problem
For this slowdown to occur each of the following conditions must be true:
- A cell within the Table must be selected
- The sheet containing the Table must be the Active Sheet
- The cell being updated must be on the same sheet as the table, but does not have to be within the table
- There must be a reasonable number of formulas in the workbook.
So change any or all of these conditions or convert the Table to a Range or delete all the formulas and the update will only take 0.5 seconds on my system.
Whats actually happening?
The time taken is proportional mostly to the number and slightly to the size of the formulas in the workbook, but none of the formulas are actually being calculated.
So it seems to me that each time the cell is updated Excel is scanning all the formulas in the workbook as though they might need to be changed.
Maybe this has something to do with the automatic extension of formulas within a table when you add a new row or the fact that the Table definition and its associated Name has to change if you add a new row.
But the fact that the slowdown only occurs if the Table is on the active sheet means that I think this is a bug.