There is a new kid on the block: Excel 2013. So its time to see how it shapes up for VBA performance against its predecessors. Along the way I will try to answer these questions:.
How does Excel 2013 VBA performance compare with previous versions?
Is 64-bit VBA faster or slower than 32-bit VBA?
Is there an optimum block size (number of cells) for getting cell data with VBA or C++?
How much faster is C++ than VBA when reading data from Cells?
Excel 2013 VBA Benchmark
Frequently the bottleneck in VBA processing time is reading and writing to Excel cells. So my Variant_Benchmark times this for a variety of cell sizes.
It turns out the relationship between time taken and the number of cells you get in a single block is pretty much linear over small ranges:
Time = overhead + number_of_cells * Time_per_cell
Running this test for on my Dell Studio XPS desktop (2.9 MHZ) for all the versions of Excel I currently have installed gives this table, (slightly overcoloured by the Excel 2013 quick analysis tool – but Quick Analysis is great for charting and formatting idiots like me).
The first 2 columns give times in milliseconds for reading and writing a single block of 512 cells, and the second 2 columns give times in milliseconds for writing a single cell 512 times.
Two things are immediately obvious:
- Reading and writing cell-by-cell is at least an order of magnitude slower than reading and writing a large block of cells in a single call.
- Writing data back to Excel is much slower than reading data from Excel.
Looking at the variations by Excel versions it is interesting to see that Excel 97 is the fastest version for cell-by-cell but the slowest version for block by block.
Here is a bar chart (again produced by Excel 2013 Quick Analysis) for reading and writing a block of 512 cells:
Read times for a 512 block have been decreasing since XL 2003: and the current champion reader is Excel 2013 64-bit.
But Write times for a 512 block have been increasing since XL 2000 – the current champion writer.
Looking at cell-by-cell you get this bar chart:
Reading and writing cell-by-cell just goes from bad (Excel 97) to worse (Excel 2010 32).
But its interesting to note that the 64-bit versions are faster than the 32-bit versions for 512 cell blocks, but slower for cell-by-cell.
Is there an optimum Block size for VBA data transfer?
Many excel versions ago (Excel 97?) I did an analysis which showed that if you increased the number of cells being read into the variant array too much the time started to increase. So I thought I would look at this again with the latest Excel versions.
So far I have not detected any decrease in efficiency: you can use as large a block of cells as you like (subject to memory limits of course).
What about C++ XLLs?
The XLL equivalent to transferring data from a range of excel cells to a variant array is the COERCE function. This has the additional benefit of letting you know if the attempt failed because of uncalculated cells.
And XLL Coerce is faster than VBA, by a factor of 2 or more.
Coerce does seem to have an optimum block size. Beyond about 1000 cells the time taken suddenly jumps by 25 to 30% in the 32-bit versions, except for XL 2013 64-bit where this does not happen.
Conclusions
- Read/write using large Ranges is much more efficient than cell-by-cell
- Excel 2013 VBA read/write speed is comparable to Excel 2010
- The 64-bit versions are faster for large ranges than the 32-bit versions
- VBA does not currently appear to have an optimum block size: the larger the range the better.
- XLL Coerce is a lot faster than VBA
- XLL Coerce 32-bit has an optimum block size just under 1000 cells
I have not tested .NET Interop with Excel 2013, but I expect its still the same depressing performance story. If you know anything different please let me know!
Excel and UDF Performance Stuff 
It would be lovely if you could compare VB.NET via Excel-DNA with the VBA and C++.XLLs. Excel-DNA implements the ExcelReference.GetValue() using COERCE, so it might give an interesting intermediate result.
Yes, I would expect XLDNA to give a result somewhere in between. I will try when I get a moment …
maybe you can explain this. a client has a query retrieve data from an as 400. once the data is refreshed the code opens another workbook (formulas). I lookup an item number from a cell in each row from the query in the formulas workbook to retrieve data. pretty simple.
I this code to perform the task:
Set rngfound = .Find(itmNum, lookat:=xlWhole, LookIn:=xlValues)
If Not rngfound Is Nothing Then
formNum = ws2.Range(“C” & rngfound.Row).Value
End If
there is some other formatting going on, but those 2 code segments are all that’s different.
excel 2013, 1900 rows of data
1 minute 11 seconds
excel 2003 , 1900 rows of data
9 seconds
then I switched to vlookup:
formNum = WorksheetFunction.VLookup(itmNum, ws2.Columns(“A:e”), 3, False)
excel 2013, 1900 rows of data
11 seconds
excel 2003
1 second
now, I don’t know what’s going on, but I can see some overhead is from that damn auto table generation when a query is run. can’t convert to range because the query is disabled. i have no idea what excel is doing for that other minute.
unbelievable how slow this is.
@gary, maybe you are looking at 1 million rows in Excel 2013 and 64000 in Excel 2003? Try working with a range that is 1900 rows rather than whole columns.
thanks for the response. i’m only checking in one column, from row 9 to the last row
For Each cell In ws.Range(“L9:L” & lastrow)
formNum = WorksheetFunction.VLookup(itmNum, ws2.Columns(“A:e”), 3, False) is looking at whole columns
you’re right, that was an old version I posted. I changed that to just look at the range.
formNum = WorksheetFunction.VLookup(itmNum, ws2.Range(“a1:e” & lastrow2), 3, False)
but that’s not the code that’s slow, it’s the find. vlookup is faster on either version. the find code takes a minute and 11 seconds, in excel 201x, the vlookup takes 11 seconds
the find code takes 8 seconds in 2003 and the vlookup code takes 1 second in 2003.
the find code uses a finite range, (about 11,000 rows)
With ws2.Range(“a1:A” & lastrow2)
Well its not possible to tell what the problem is from your code fragments, and I am now out of the office for a few days …
no need to worry about it, i’m just pointing out the new versions of excel are slow as hell doing certain things. it runs fine for what my client needs, only takes 11 seconds.
Another great post: thanks Charles.
Ross
Great post. Thanks, that was very interesting.
I’ve just installed 2013 Professional, and can’t believe how much slower code seems executes on it compared to the same code running on Excel 2010. Even very simple code.
For instance, I’ve got some code that toggles the view of some additional worksheets in the workbook. It’s nearly instantaneous in 2010. It takes around a quarter of a second in Excel 2013.
Interesting: it only appears to be slower with one particular workbook. The project is pretty complex, with lots of capturing of events and such forth…but with hardly any formulas. So maybe its something to do with events. Maybe not.
Jeff,
let me know if you manage to pin it down to something specific.
Pingback: VBA speed in XL2013x64 on Win7x64 | carolomeetsbarolo
I have been trying to find performance benchmarking comparing
Excel and google sheets. Sheets can import from excel so a large
Sheet with some well chosen formulas would provide a quantitative comparison
Seems like a natural post for your site.
Googled a lot but could not find any hard data just vague commentary.