(C) 2009 - 2014 by Mourad Louha · All rights reserved

VBA Performance in Excel 2010

I’m currently developing a tool for comparing the VBA performance of Excel 2010 with Excel 2007 and Excel 2003. In its actual version, this tool is able to measure the time used when running nested loops and when creating shapes. In this first article of a series I would like to presents my first results.

Excel 2010 VBA Performance

The first test I have done, was to run a nested loop in the three versions of Excel. I used Windows API functions to measure the time, as they allow more precise measurements. The test can be repeated several times to become a little more meaningful. The tool stores the test results in a report sheet, which also calculates the average of the tests results.

At this point it should be noted, however, that my tests can provide only approximate values, these tests have not been done under laboratory conditions. And the results of these test are reflecting the performance on my machine. The three Office suites are all installed on the same computer. I can’t say if this has an impact on the test results. Following picture shows the results of my first test:

Excel 2010 VBA Performance

As you can see, the test results of the three applications are similar. Excel 2010 is a little bit more slower than Excel 2007. And Excel 2007 is in turn a little bit slower than Excel 2003. I repeated this test several times, the differences were minimal. If I now execute 3 nested loops with each 1000 runs, I get the results as shown in the picture below.

Excel 2010 VBA Performance

Overall, my conclusion is that the performance of that kind of calculation has not changed much in the three apps. Following an excerpt from the source code for the execution of the loops.

As you can see, I first start a timer, then the loops are executed. The vars u,v and w are representing the upper bounds. The code also checks how many nested loops will be excuted. The var r is only a dummy filled with some values. Finally the timer is stopped.

The second more interesting test, I executed, was the creation of shapes – like rectangles, ovals oder triangles – with VBA code. Here, we will see clear differences between the versions. The tools has several options and settings, as you can see in the picture below.

Excel 2010 VBA Performance

First, I inserted 1000 shapes on a sheet of a new workbook. The shape type was generated randomly and they were also colored with a randomly generated color. When inserting the shapes, the sheet is scrolled down to see each object created on the screen. Here is the result of my test:

Excel 2010 VBA Performance

Excel 2007 needs more than 6 minutes for this test. Excel 2010 and Excel 2003 are much more efficient, not even one tenth of the time of Excel 2007 is needed! And Excel 2010 reaches almost the speed of Excel 2003. If you consider now, that some new properties have been added to the shapes since 2007, I think, there is also an increase of the speed of Excel 2010 compared to Excel 2003.

I runned the same test, but in this case I disabled the screen updating. The result is shown below:

Excel 2010 VBA Performance

Excel 2010 is still faster than 2007, however, both are slower than 2003. But I think we have also to keep in mind here again that the object properties have been extended. In my opinion, these tests shows us that the grafics engine of Excel 2010 must have significant improvements. By the way, these test also shows us, that disabling the screen updating results in high performance benefits. This can especially be seen in Excel 2007.

Finally, I’m working on more test procedures, for example for formulas, charts or calculations. I will publish the results in one of my next posts. And I will publish the tool as freeware when all tests are implemented. And please consider, if you have read my German article, the results of my tests are not exactly the same, as I repeated them in the english version of the tool.

This article has also been published in German and in English on the following sites:

 

 
Comments

No comments yet.

Leave a Reply