January 26th, 2011, 7:01 pm
I am starting to test our sheets and addins in Excel 2007 (from Excel 2003) and I am finding Excel 2007 at times more than an order of magnitude slower than its predecessor, and sometimes on very trivial operations. An example is attached. In the sheet I look at a range from VBA (e.g. sheet1.range("A1").value = 234). Repeating the operation 10,000 times in Excel 2003 takes about 0.2seconds; in Excel 2007 with same spec machine takes 6seconds, a staggering 30 times slower!I know that it is more efficient to create an array and write it in a single operation; I just wonder if there is some hidden setting of Excel that I missed or if it is really something I've to live with.Another question: if I open another spreadsheet within the same Excel session; even if my VBA macro doesn't do anything to it, it slows down even further! For example, if I open both my test sheet and one downloaded from:Value of assumption.xlsthen the same task that took in Excel 2003 0.2seconds, in Excel 2007 6 seconds, now takes: 16 seconds. This worries me: I have no control on how many sheets a trader may open in the same session (nor I think I should worry), but if something as random as what else happens in the same session of Excel can have huge effects in performance, I am worried.Do you notice anything similar moving from Excel 2003 to Excel 2007? Any comments?thanksgc

Attachments

 excel2007 benchmark.zip
 (77.68 KiB) Downloaded 18 times