What is the fastest way to read values from and write values to an Excel worksheet with VBA? And how much faster is it?
1. Turn off the screen updating while the code is running
2. Set Excel’s calculation mode to manual while the code is running
3. Don’t select (activate) objects (ranges, charts, shapes, etc.)
4. Optimize the way of reading values from and writing values to the worksheet. Instead of looping through a range, read or write the entire block of data in one go with one single statement.
Well, you have seen those before, right? Nothing new under the sun.
However, how much can you speed up your code with these best practices?
Well, for the screen updating and the calculation mode, it depends on how your specific workbook looks like. The more complex the display is (i.e. how much rendering Excel has to do to update the screen?) and the more and more complicated formulas you have in your workbook (i.e. how long does one recalculation take?), the higher the performance gain will be. Thus, it is hard to provide universally valid numbers on the potential performance increase.
With regards to the avoidance of Select statements and the method of transferring data between Excel and VBA, however, a more or less generic test environment is possible.
Today’s post provides a small Performance Test Tool to demonstrate how much faster your VBA code can be, if you are using the best method to read data from or write data to an Excel worksheet using VBA. As always, the article comes with the Excel workbook for free download.