Previous posts on Writing Efficient UDFs Part 1 and Part 2 have looked at the importance of optimising the way you transfer data between Excel and VBA.
This is often a major factor in the speed (or lack of it) of VBA Macros. So this post shows you how the transfer time varies according to the amount of data you transfer, compares these times for recent Excel versions and derives a simple formula that enables you to predict the times.
The timings in this post were done using my Dell Latitude D830 laptop, which has a 2.39 GHz Intel Core 2 T7700.
The method used is to read a block of data from Excel cells into a Variant array and write a block a block of data from a Variant array into Excel cells. The size of the blocks is varied, and the time is measured for each read and write block.
When you plot time versus block size for this data you get a straight line, and you can use Excel’s LINEST function to find out what the slope and intercept of the line is:
Read/Write Times versus block size
This chart is for Excel 2010 32-bit (Excel 14). The blue line is for Read and the red line is for Write, and you can see that Reading data is much faster than writing data.
The formulae are:
- Read: Time in Milliseconds=0.0023 + 0.00042 * Number of cells
- Write: Time in Millseconds=0.028 + 0.00174 * Number of cells
The straight line is a good fit (Rsquared=0.996).
The overhead time associated with each read/write operation is significant: this is why it is so much faster to read/write large blocks of cells rather than read/write one cell at a time.
The formulae predict that reading and then writing a Range of 20 columns by 200 rows would take:
- Single read and write of the entire range: 9 Millisecs
- Reading and writing each cell in the range one-by-one: 132 Millisecs
Looking at how these times have varied by Excel version gives this chart:
Read Write times by Excel Version
I have left out the timings for Excel 2007 before the SP2 update: they were extremely slow!
You can download a workbook from here that contains all the code you need to run these benchmarks, charts and derive the equations on your system.
Excel and UDF Performance Stuff 
Interesting Charles, 2002 is the fastest! Any ideas why its getting slower? Is there some grid size impact before the read/write, is it check for something?
Other thing that’s interesting is the read time is slower in 2002, but the right time comes down?
I think I’m going to simply you formulas to:
Read = Cells * 0.002
Write = Cells * 0.03
😉 – slap dash as I am!!!
Good stuff thanks Charles
Ross
The chart is a bit misleading because you immediately focus on the top lines which are for single cells, and of course we would not do that anyway – would we!
Getting slower –
The only significant change I can think of is the introduction of Unicode in Excel 2007 which allowed strings longer than 255. Thats probably why Excel 2007 VBA before SP1 was so unbelievably slow.
If you look at the detail in the download file you can see that XL 2010 is the champion at reading blocksize 512 cells – about twice as fast as any other version.
The winner for writeing blocks is XL 2000.
For a combined read and a write of 512 cells XL 2010 is nearly back to XL 2003 speed!
I always though that XL 2000 had the most optimised calc engine and was the fastest as far as formulas are concerned