Excel extensions with VSTO power tools

Apparently, VSTO Power Tools have been around for a while (Feb 02), but if I had not read this article, I would have missed them - which would be too bad, because they are awesome. The Power Tools consist of a few dlls, which, while not officially supported, have been released by Microsoft developers. I started playing with the Excel Extensions, and I love it; it is a “very thin wrapper to the Office primary interop assemblies”, which essentially gives you cleaner methods to access the Excel object, with type safety, and without the clumsy “Missing.Value” arguments.

Collectively deciding between alternatives

The November issue of msdn magazine has a nice piece by Dr. James McCaffrey on determining a best option from a list of choices. It’s an interesting departure from his usual topic, testing, and it proposes a discussion and implementation of the Schultze method. As far as I remember, Arrow’s impossibility theorem demonstrates that

no voting system can convert the ranked preferences of individuals into a community-wide ranking while also meeting a certain set of reasonable criteria with three or more discrete options to choose from.

Therefore, I have to assume that there are limitations to this method. If I have time, I’ll try to figure out in what cases these show up. However, the exposition of the problem is worth reading.

Shortest path with the Excel solver

Great post on the Excel blog, explaining from start to finish how to set up a shortest path problem using Excel, and how to use the solver to identify the optimal solution.

Using Windows Live Writer

I have been a bit frustrated by the sometimes erratic behavior of the code formatter that ships with Blogengine.Net. It works well for C# code, but I still couldn’t figure out why it totally garbled my attempt at VB code. So I was happy to see a post from a credible source titled “Best Code Syntax Highlighter for Snippets in your Blog. Turns out, after looking at the comments, I am not convinced yet I have found the Holy Grail of code formatting, but on the other hand, it triggered me to try out Windows Live Writer; and indeed, Hanselman has a point - why haven’t I used it before? It’s just so much more comfortable!

VSTO vs. VBA performance comparison

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