Walkthrough: Adding your own menus

Note: Since XLL+ version 4.3.1, you can use the XLL+ AppWizard to create a new add-in with a menu. See the AppWizard reference for details. However, the instructions below are useful if you want to add a menu to an existing add-in project.

To make your add-in methods available as commands from the Excel Application menu, you must create a CXlMenu object and manage its lifetime. The following walkthrough shows how to create and manage Excel menus from an XLL.

Creating the project

To create the project using Visual Studio 6

1. From the File menu, select New to show the New dialog.
2. Select the XLL+ AppWizard 4 project template from the list in the Projects tab, and enter MenuTest in the Project name box. Under Location, enter an appropriate directory in which to create the project.
3. Accept all the default settings in both pages of the XLL+ AppWizard.

To create the project using Visual Studio .NET or Visual Studio 2005

1. From the File menu, select New and then Project to open the New Project dialog.
2. Select the XLL+ Excel Add-in project template from the list of Visual C++ Projects, and enter MenuTest in the Name box. Under Location, enter an appropriate directory in which to create the project.
3. Accept all the default settings in the XLL+ AppWizard.

For more details about creating projects, see Creating an add-in project in the XLL+ User Guide.

Adding the code

Adding the menu code

1. In your application's main header file, MenuTest.h, include xlmenu.h, after the line that includes xllplus.h.

   #include <xllplus.h>
   #include <xlmenu.h>
   

2. For each menu bar you wish to add to the Excel menu, you must create an instance of CXlMenu in the application class. In this case we will add one menu bar:

   class CMenuTestApp : public CXllApp
   {
   public:
       CMenuTestApp();
   
       // User-defined menu
       CXlMenu m_menu;
   
       ...
   };
   

3. Add declarations for the OnXllOpenEx() and OnXllClose() event handlers to your application class. These two events are raised when the add-in is first loaded, and when it is closed.

   class CMenuTestApp : public CXllApp
   {
   public:
       CMenuTestApp();
   
       // User-defined menu
       CXlMenu m_menu;
   
       ...
   // Overrides
       virtual BOOL OnXllOpenEx();
       virtual void OnXllClose();
       ...
   };    
   

4. In the main source file, MenuTest.cpp, implement the OnXllOpenEx() event-handler, as follows:

   BOOL CMenuTestApp::OnXllOpenEx() {
       // Initialise the menu's text
       m_menu.SetTexts("&MyMenu", "My add-in menu");
       // Add two menu items
       m_menu.AddItem("Macro &1", "MyAddinMacro1", "Add-in Macro 1");
       m_menu.AddItem("Macro &2", "MyAddinMacro2", "Add-in Macro 2");
       // Create the menu, to the left of the existing Tools menu
       m_menu.Create("&Tools");
       
       return TRUE;
   }
   

5. In MenuTest.cpp, implement the OnXllClose() event-handler, as follows:

   void CMenuTestApp::OnXllClose() {
       // Destroy the added menu. 
       m_menu.Destroy();
   }
   

Implementing the command functions

1. It is essential that any functions called from menus be marked as macro functions. This means that they must satisfy two criteria:

   1. The function should take no arguments.

      There is no way to pass any arguments to the macro if it is called from a menu.

   2. The Macro function check-box should be checked in the XLL+ Function wizard.

      This will ensure that the final argument to the IMPLEMENT_XLLFN2() macro is 2, as in the example functions below, and thereby make the function available from menus and push-buttons.

2. Add the following code to MenuTest.cpp:

   // Function:    MyAddinMacro1
   // Purpose:     Demo macro function
   
   //{{XLP_SRC(MyAddinMacro1)
       // NOTE - the FunctionWizard will add and remove mapping code here.
       //    DO NOT EDIT what you see in these blocks of generated code!
   IMPLEMENT_XLLFN2(MyAddinMacro1, "R", "MyAddinMacro1", "", 
       "User Defined", "Demo macro function", "", "", 2)
   
   extern "C" __declspec( dllexport )
   LPXLOPER MyAddinMacro1()
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       MessageBox(CXllApp::XlHwnd(), "Add-in macro 1", "Add-in macro", 0);
       return xloResult.Ret();
   }
   
   // Function:    MyAddinMacro2
   // Purpose:     Demo macro function
   
   //{{XLP_SRC(MyAddinMacro2)
       // NOTE - the FunctionWizard will add and remove mapping code here.
       //    DO NOT EDIT what you see in these blocks of generated code!
   IMPLEMENT_XLLFN2(MyAddinMacro2, "R", "MyAddinMacro2", "", 
       "User Defined", "Demo macro function", "", "", 2)
   
   extern "C" __declspec( dllexport )
   LPXLOPER MyAddinMacro2()
   {
       CXlOper xloResult;
   //}}XLP_SRC
   
       MessageBox(CXllApp::XlHwnd(), "Add-in macro 2", "Add-in macro", 0);
       return xloResult.Ret();
   }
   

Testing the add-in

To register the add-in

1. Build the project.
2. Start Excel, and use the Tools - Add-Ins... menu to open the Add-ins dialog.
3. Click on the Browse... button and select the built add-in file MenuTest.xll from the Debug sub-directory of your project directory.
4. If a message box asks whether you wish to replace the existing item, reply Yes.
5. Click on the OK button to close the Add-Ins dialog.
6. Note that a new menu MyMenu appears on the Excel menu bar, just to the left of the Tools menu.
7. Click on either of the menu items, to invoke the add-in functions.

To unregister the add-in

1. Use the Add-Ins dialog to remove "New Xll" from the list of registered add-ins, and notice the MyMenu menu disappear.