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

Adding a sub-menu in an Excel add-in

Someone commented to my post on add-in menus asking if it was possible to add sub-menus to menus - the answer is yes. The code is essentially the same, with one small difference. When you add a menu item to a menu, you will add a CommandBarControl (the menu item) to the controls of a CommandBarPopup, the menu container. If you want to add a “nested” menu to the menu, instead of adding a CommandBarControl, you will add a CommandBarPopup, which can then receive menu items (or more nested menus!).

In code, it would look something like this:

// Add the sub-menu to parentMenu, which is a CommandBarPopup 

CommandBarPopup parentCommandBarControl = (CommandBarPopup)parentMenu.Controls.Add(
MsoControlType.msoControlPopup, Type.Missing,
Type.Missing, Type.Missing, true);

parentCommandBarControl.Caption = "Sub-Menu";
parentCommandBarControl.Visible = true;

// Add the menu item to the sub-menu 

CommandBarControl commandBarControl = parentCommandBarControl.Controls.Add(
MsoControlType.msoControlButton, Type.Missing,
Type.Missing, Type.Missing, true);

commandBarControl.Caption = menuItemCaption;
commandBarControl.Visible = true;

VSTO session code sample

Thanks to you guys who attended my session on VSTO at Silicon Valley Code Camp 2008! This was the first time I gave a talk on VSTO, and I really enjoyed the discussion and questions.

I have put the code I presented up for download here; it includes the TicTacToe “engine”, the add-in itself, the installer, and the Excel workbook. As a result, the download is somewhat big - sorry! The purpose of the code was to keep things simple and understandable, and it could definitely be tightened up. Specifically, it is very optimistic in the way it is accessing the data in the workbook: adding some checks for whether a workbook/worksheet are open would be highly recommended… Let me know if you have questions or comments!