During my VSTO add-in session last week-end, the following question came up: what performance difference should I expect if I run code in VSTO instead of VBA? This is particularly important for Excel power users, who leverage VBA to automate their workbooks and run computation intensive procedures. One audience member had a good example: he used VBA to run Monte-Carlo simulations on budget forecasts stored in Excel.

My answer was that I expected VSTO to outperform VBA in the area of pure computation, but that VBA might do better for direct interaction with the application (reading data for instance), because of overhead. However, I had no hard evidence for that, and the question got me wondering, so I decided to run comparisons. My first test confirmed my intuition: on a calculation-heavy procedure, the VSTO code ran about 3 times faster than the equivalent VBA code (30 seconds vs. 1 minute and a half, on the same machine).

The code I wrote was intended to isolate computation speed; to do that, I tried to write something simple and comparable in both languages, using little memory, with no interaction with the host application.The code performs a simple Monte Carlo simulation, replicating the gambler's ruin problem. A gambler calls heads or tails, starting with $30, and loses/wins 1$ at every coin toss; what is the probability that he loses all his money if he plays up to 1,000 times? The simulation runs 1,000,000 games, and records all the cases where the gambler is ruined. The code for both implementations is provided below.

Next I'll try and see how things look with procedures involving lots of object manipulation and memory usage. Oh, and the by the way, the gambler has about 34% chances of going home with no money left!

The VBA version

Note: sorry for the poor VBA code rendering, but the code-formatting extension handles VB very poorly... I tagged it as C#, but this is really VBA code.

The C# / VSTO version