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!

TDD session code and slides

Thank you to all of you who attended my session on test-driven development for C# developers at Silicon Valley Code Camp 2008!

Here are my slides and, more importantly, the code I presented during the session. In order to get it to work, you will need to install NUnit on your machine first. Besides the unit tests for City, there are 3 files in the folder “Tests”. RowTestIllustration is an example of how to use RowTest to pass different set of values to the same test; note the using statement “using NUnit.Framework.Extensions”. SetupIllustration shows how the method marked “SetUp” runs before each test method is executed. Finally, “MockIllustration” shows how you can use mocks to easily create instances that satisfy an interface, and verify that the class you are testing conforms to a specified behavior when interacting with that interface.

That’s it! Let me know if you have questions or feedback.

Installing a VSTO add-in on a .NET 2.0 machine

Last Friday, I had a meeting to present a VSTO Excel add-in to a client - and I got an unpleasant surprise: not only did the client not have .NET 3.5 installed on his laptop, but the IT department was not willing to install it, as they had not evaluated it. Rather than trying to convince them that 3.5 was innocuous, I thought, let’s try to change the project target from .NET 3.5 to .NET 2.0. After removing all dependencies, I rebuild, do an install on a clean, 2.0-only virtual machine, and… it fails miserably, with the message “This setup requires the .NET Framework 3.5”. Not good.

When I had nearly given up figuring out what reference I had forgotten (and was bracing myself for a lengthy discussion with IT), XL-Dennis came to the rescue with this post, via the Excel User Group. Thank you a million. It turns out that when you change the Target Framework from 3.5 to 2.0, most things get updated, except… the launch condition that checks the presence of the .NET Framework on the target machine, which stays stuck on 3.5. Once you manually change it to 2.0, everything works fine.

Free trade

In its October issue, Wired has a short interview of the economist Jagdish Bhagwati, on free trade. I enjoyed his point that most of the free-trade discourse focuses on free-trade agreements which are ultimately large protectionist blocks, and ignores the idea of IP free-trade.