Excel VSTO add-in menu manager

02 Jul 2008

If you have been looking at my recent bookmarks, you may have
noticed a pattern: they all revolve around Excel and VSTO. The reason is that I
am starting multiple Excel development projects in the next few weeks. I am
very experienced in VBA and Office development, but after 4 years of writing C# code in Visual Studio, I have been spoiled, and VBA
suddenly feels very painful to work with, as if I were traveling back in time to the
middle-ages of development.

Fortunately, there is now an alternative: with VSTO, you can add custom features to
classic Office applications, using .NET languages and the comfort of Visual
Studio 2008. So I thought it was time to give VSTO a shot.

My first project was to establish a simple way to expose the
add-in functionality to the user through menus. I started from this article, and
adapted the code to encapsulate the menu-related behavior in one easy-to-use
class, the MenuManager.

The sample add-in works
with Office 2003 and 2007, and Windows XP and Vista. It installs an add-in which creates its own menu in Excel, “My Add-In”, containing two choices, “Do This” and “Do That”. When
these are clicked, message boxes pop up, displaying if the user has selected to “Do This” or “Do That”,
and the name of the currently active sheet.

The code for the sample illustrates how to achieve this. The
ThisAddIn class contains a member MenuManager. In the add-in start-up section, the
add-in menu and menu items are added through the menu manager. This automatically
creates these elements, and hooks up the menu click events. The add-in shutdown
section unsubscribes the events. That’s it.

usingSystem;usingSystem.Collections.Generic;usingSystem.Windows.Forms;usingOffice=Microsoft.Office.Core;namespaceExcelAddIn{internalclassMenuManager{#regionMembersprivateThisAddInm_AddIn;privateOffice.CommandBarm_ExcelMenuBar;privateOffice.CommandBarControlm_AddInMenu;privateList<Office.CommandBarButton>m_MenuItems;#endregion#regionConstructorinternalMenuManager(ThisAddInaddIn){m_AddIn=addIn;m_MenuItems=newList<Microsoft.Office.Core.CommandBarButton>();InitializeExcelMenuBar();}#endregioninternalvoidCreateAddInMenu(stringmenuCaption){try{Office.CommandBarControlmenu=m_ExcelMenuBar.Controls.Add(Office.MsoControlType.msoControlPopup,Type.Missing,Type.Missing,Type.Missing,true);menu.Caption=menuCaption;m_AddInMenu=menu;}catch(Exceptionex){MessageBox.Show(ex.Message,ex.Source,MessageBoxButtons.OK,MessageBoxIcon.Error);}}internalvoidAddMenuItem(stringmenuItemCaption){Office.CommandBarButtonmenuItem=CreateMenuItem((Office.CommandBarPopup)m_AddInMenu,menuItemCaption);m_MenuItems.Add(menuItem);SubscribeMenuItemClick(menuItem);}#regionhelpermethodsprivatevoidInitializeExcelMenuBar(){try{m_ExcelMenuBar=m_AddIn.Application.CommandBars["Worksheet Menu Bar"];}catch(Exceptionex){MessageBox.Show(ex.Message,ex.Source,MessageBoxButtons.OK,MessageBoxIcon.Error);}}privateOffice.CommandBarButtonCreateMenuItem(Office.CommandBarPopupparentMenu,stringmenuItemCaption){Office.CommandBarControlcbc=null;try{cbc=parentMenu.Controls.Add(Office.MsoControlType.msoControlButton,Type.Missing,Type.Missing,Type.Missing,true);cbc.Caption=menuItemCaption;cbc.Visible=true;}catch(Exceptionex){MessageBox.Show(ex.Message,ex.Source,MessageBoxButtons.OK,MessageBoxIcon.Error);}return(Office.CommandBarButton)cbc;}#endregion#regionhandlingofeventsprivatevoidMenuItem_Click(Office.CommandBarButtonmenuItem,refBooleanCancelDefault){ExecuteMenuItemAction(menuItem);}privatevoidExecuteMenuItemAction(Office.CommandBarButtonmenuItem){stringselectedMenu=string.Format("You just selected '{0}' from the menu.",menuItem.Caption);stringworkBookSelected="No workbook selected";if(m_AddIn.Application.Workbooks.Count>0){workBookSelected=String.Format("The name of your workbook is '{0}'.",m_AddIn.Application.ActiveWorkbook.Name);}stringmessage=selectedMenu+"\n"+workBookSelected;MessageBox.Show(message,"Add-In Menu Demo",MessageBoxButtons.OK,MessageBoxIcon.Information);}privatevoidSubscribeMenuItemClick(Office.CommandBarButtonmenuItem){menuItem.Click+=newMicrosoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(MenuItem_Click);}privatevoidUnsubscribeMenuItemClick(Office.CommandBarButtonmenuItem){menuItem.Click-=newMicrosoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(MenuItem_Click);}internalvoidUnsubscribeAll(){foreach(Office.CommandBarButtonmenuIteminm_MenuItems){UnsubscribeMenuItemClick(menuItem);}}#endregion}}

The design has at least two limits I can see. First, only
one menu can be added so far, and all menu items are added to that menu.
Extending the class to create multiple menus for the add-in would be fairly
trivial, but I also question the need for this: if your add-in needs more than
a few menu items, you should probably create a form with a dedicated menu bar.

The real limit, in my opinion, is in the way the menu click events
are processed in MenuManager.ExecuteMenuItemAction() method. There is no reason
why the MenuManager should be responsible for executing the add-in business
logic: its only role should be to receive notifications from the menu, and
convey that information to another class responsible for taking action. The
current implementation is completely temporary, and intended as a
proof-of-concept; my next step will be to actually design that other class.

This is still work-in-progress, and I would welcome
feedback, comments, or criticism (questions, too); nothing like a review to
improve your code!

I apologize for the large and somewhat cumbersome installer; the size is due to the inclusion of Office 2003 and 2007 PIA. Once downloaded, extract the entire folder and run ExcelAddInSetup.msi. You may have to run Setup.exe as well, depending on how your machine is setup.