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: 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:
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.