Excel 2007 speed test


Up until now, I have been using Excel 2003 at both home and work, and have had no strong urges to switch to 2007. Since its release I have seen rather a lot of comment about its performance, with assessments ranging from fair to rubbish, for instance on Newton Excel Bach and at keyongtech. The Excel team blog also has a recent post asking for examples from people who’ve been experiencing slower VBA performance with Excel 2007 than with 2003. So up until now I’ve held off. However, my employer is about 3 months away from rolling out Office 2007 nationwide, and so I thought it’d be worthwhile taking a look.

So this evening I downloaded a copy of the trial version of Office 2007 at home and tried out a (very basic) speed test. The code below repeats a primality test 10,000 times and outputs to cells in the active sheet:

Public Function ISPRIME(ByVal lTest As Long) As Boolean
‘ Returns TRUE for a prime, FALSE for composite
Dim lIn As Long

lIn = 2
ISPRIME = True
Do While lIn <= Sqr(lTest) If lTest Mod lIn = 0 Then ISPRIME = False Exit Do End If lIn = lIn + 1 Loop End Function Public Sub SpeedTest() Dim rngTest As Range Dim sglStart As Single Dim sglFinish As Single sglStart = Timer For Each rngTest In ActiveSheet.Range("A1:J1000") If ISPRIME(rngTest.Row + (rngTest.Column - 1) * 1000) Then rngTest.Value = "Prime!" Else: rngTest.Value = "Not prime..." End If Next rngTest sglFinish = Timer Debug.Print "Total seconds elapsed = " & sglFinish - sglStart End Sub[/sourcecode] Running this 10 times in 2003 gave the following results: Total seconds elapsed = 6 Total seconds elapsed = 6.15625 Total seconds elapsed = 6.171875 Total seconds elapsed = 5.953125 Total seconds elapsed = 6.203125 Total seconds elapsed = 6.234375 Total seconds elapsed = 6.6875 Total seconds elapsed = 6.203125 Total seconds elapsed = 5.953125 Total seconds elapsed = 5.9375 and in 2007: Total seconds elapsed = 9.84375 Total seconds elapsed = 9.296875 Total seconds elapsed = 9.203125 Total seconds elapsed = 9.171875 Total seconds elapsed = 9.21875 Total seconds elapsed = 9.140625 Total seconds elapsed = 9.140625 Total seconds elapsed = 10.09375 Total seconds elapsed = 9.1875 Total seconds elapsed = 9.171875 The main point of the exercise for me was to evaluate whether there was a significant difference in speed of data transfer from VBA to Excel. Not only has this been an issue for my code in the past (i.e. based on experience this is one of the first things I generally look at when optimising performance), but a number of people have commented that VBA in 2007 does this a lot slower than it does in 2003. While the results above are hardly concrete evidence, I think at first glance this does seem to be borne out. Incidentally, I tried running a similar test without transferring the data to a worksheet, using the code below: [sourcecode language='vb'] Public Sub SpeedTest2() Dim lTest As Long Dim bResult As Boolean Dim sglStart As Single Dim sglFinish As Single sglStart = Timer For lTest = 2 To 10000 bResult = ISPRIME(lTest) Next lTest sglFinish = Timer Debug.Print "Total seconds elapsed = " & sglFinish - sglStart End Sub [/sourcecode] and the results showed very little difference between the two (as I would have expected, really). Here's 2003: Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.0625 Total seconds elapsed = 0.0625 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.09375 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 and in 2007: Total seconds elapsed = 0.09375 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.0625 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125 Total seconds elapsed = 0.078125

Advertisements

5 thoughts on “Excel 2007 speed test

  1. Tim Mayes says:

    Geoff, Jon Peltier has a good post on this same issue with some good links to other posts. In particular, he’s done some testing on Excel 2007’s new charting engine that show that it is about 10 times slower than Excel 2003. To me, that is what I notice the most. Even on my very fast PC, charts just feel slow.

    Check out Jon’s post.

  2. geoffness says:

    Thanks for the link Tim. I have to say overall I’m more than a little disappointed by this whole issue – from my early days of using Excel I was always impressed by its ability to recalculate so much so quickly. It now appears that in the attempt to push the product to a new place something got broken along the way. As you point out, this is happening on fast PCs – the average desktop is a whole lot faster now than it was when Excel 2003 was released, so one would hope new versions of the software would be able to take advantage of the beefier CPUs to offer enhanced performance. It’s encouraging to see the Excel team acknowledging the problem though…I just hope they are able to pin down the issue(s) involved and resolve them before the next Office release. I don’t relish the thought of being stuck with a lemon for the next x years before my employer decides to upgrade again.

  3. dougaj4 says:

    Geoff – I think the main point that comes out of your numbers (and this reflects my experience) is to transfer data between the spreadsheet and VBA as arrays in as large lumps as possible, do the processing in VBA, then pass it back as arrays. This applies to all versions, but especially to XL2007.

    The other thing that has become even more of a bottleneck than before is the use of the worksheetfunction method. If you have routines that make frequent use of worksheetfunction calls its well worthwhile to write your own UDFs to replace them.

    Finally in defence of Microsoft, or at least in defence of the team currently working on this issue, I have had some feedback from my posts at the MS Excel blog, indicating that they recognise there are problems, and that they are working on them.

  4. geoffness says:

    Those are excellent points Doug, thanks. In particular, I must admit worksheetfunction calls (usually lookup functions) to be something I have over-used in the past. It certainly makes much more sense to use VBA array processing – in fact, I think I might post later this week about a great example of some code I wrote last year which could be improved by doing just that.

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