JKP Application Development Services.

                    Microsoft Office Application Development

Creating an addin from an Excel macro.

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

Menu’s

Excel 97 to 2003

It is very easy to manually add extra entries to Excel’s menu structure.

Adding entries to a menu

Right click menu customize new menu entry

This way of adding entries to the menu has a great disadvantage however: The changes are stored locally and thus only available to the current user. These changes are kept in a file which name depends on your version of Excel :

So this manual method is not suitable when one wants to distribute a macro to others, with a menu entry to invoke it. One might be tempted to copy the above .xlb file to someone else’s PC. Don’t! Doing that wipes out the customizations the other user has done to his or her menu system!

The only proper way to ship a menu with your application is by building one using VBA. Listing 1 below shows how this works.

Listing 1

Option Explicit

Sub MakeMenu()
    Dim cControl As CommandBarControl
    RemoveMenu    'Prevents duplicate entry of the menu item
    Set cControl = Application.CommandBars(1).FindControl(ID:=30007).Controls.Add _
    (Type:=msoControlButton, temporary:=True)
    With cControl
        .Caption = "&XL Utility Example"
        .OnAction = "DemoSub"
    End With
End Sub

Sub RemoveMenu()
    On Error Resume Next
    Application.CommandBars(1).FindControl(ID:=30007).Controls("&XL Utility Example").Delete
    On Error GoTo 0
End Sub

Sub DemoSub()
    MsgBox "You have just selected your new menu item!!!"
End Sub

The sub “MakeMenu” creates a new entry in the Tools menu, this is achieved by the following line:

    Set cControl = Application.CommandBars(1).FindControl(ID:=30007).Controls.Add _
    (Type:=msoControlButton, temporary:=True)
 

Note that the FindControl method is being used, looking up the control by its Id number rather than its name. This is done to make sure the code works on any language version of Excel. The Tools menu’s Id equals 30007. The Id numbers of all other Excel menus can be found in the file called xlmenufundict.zip.

A quick reference to the main menu entries in Excel’s workbook menu bar is listed below:

By first calling the sub RemoveMenu before adding the new entry, one avoids accidentally adding the same entry twice (this should normally never happen). The sub RemoveMenu should be called by the utilities’ closing code so the entry is removed when the utility is closed.

Strictly speaking RemoveMenu is not really mandatory, since the argument “Temporary:=true” has been used in order to ensure the entry is removed by Excel when it is shut down. But it is still a good idea to control the moment that your menu entry is removed yourself, so you can e.g. remove it when your add-in is uninstalled without the user closing Excel, or when the user just closes your file and not Excel.

Excel 2007

With Excel 2007, Userinterface changes by using VBA have changed drastically. Luckily, code as shown above will still work. Sort of. Below is a screenshot of what the Addins tab of Excel 2007 shows after opening one of my popular tools:

Ribbon of Excel 2007, addins tab

Yes, that's right: ALL "old style" VBA menu and toolbar customisations are pushed onto groups in the Addins tab.

Modifying the ribbon

You can manipulate the appearance of the ribbon. But not with VBA. See the figure below, with a screenshot of my Excel 2007 version of this same tool (I deliberately opened Excel in a small window to make this picture a bit smaller):

Ribbon of Excel 2007, customised by adding a group with one button

In order to get this to work, you need to follow quite a series of steps. Ready? Take a deep breath and dive in with me:

This example demonstrates the steps required to (manually) add a new Ribbon group to the Home tab. The new Ribbon group is named FlexFind, and it contains one button, labeled FlexFind. Clicking that button runs a VBA macro named FlexFind2007:

1. Create a new Excel workbook, insert a VBA module, and enter this procedure:

Sub FlexFind2007(control As Object)
    FlexiFinder
End Sub

Note that I declared control as Object rather than as IRibbonControl, which is the appropriate object. I did this on purpose, because otherwise previous versions of Excel will generate a compile error. I can now leave the code above in my old versions without errors.

2. Save the workbook, and name it YourName.xlsm (choose the right filetype!!!)

3. Close the workbook.

4. Activate the folder that contains the YourFile.xlsm file and create a folder named customUI.

5. Inside of that folder, use a text editor (such as Windows Notepad) to create a text file named customUI.xml, with the following XML code (watch out for word wrap!):

<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
    <ribbon>
        <tabs>
            <tab idMso='TabHome'>
                <group id='Home' label='Flexfind'>
                    <button id='Button5' label='FlexFind' size='large' onAction='FlexFind2007' image='FlexFindIcon' />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

6. Open Windows Explorer and locate the YourFile.xlsm file.

7. Add a .zip extension to the file by pressing F2 and then changing the filename to YourFile.xlsm.zip. This way, you will be able to edit and view the actual contents of the Excel file by using your favorite file compression program. This includes adding/removing files to the zip container.

8. Drag the customUI folder you created in Step 4 into the YourFile.xlsm.zip file. (Windows treats ZIP files as if they were folders.)

9. Every Excel file (in the new file format) has a folder named rels. Doubleclick the rels folder within the ZIP file. This folder contains one file, named .rels.

10. Drag the .rels file to a location outside of the ZIP file (your Desktop, for example).

11. Open the .rels file (which is an XML file) with a text editor, such as Notepad.

12. Add the following line to the .rels file, before the </Relationships> tag:

<Relationship Type=”http://schemas.microsoft.com/office/2006/relationships/ui/extensibility” Target=”/customUI/customUI.xml”/>

13. Drag the .rels file back into the ZIP file, overwriting the original version.

14. Remove the .zip extension so that the file is back to its original name:

YourFile.xlsm.

If all went well, you see the new Ribbon group (Flexfind) when you open the workbook. If you’re lucky, your screen will look like Figure 8.

Luckily, there are tools that help you with putting the XML in the right place. One of them is the Office custom UI editor. This tool allows you to open an Office file, paste in the XML code (yes, you still have to create that!) and if needed add icons and save the file.

Fortunately, Excel 2007 still has some commandbars: the rightclick menus. You can still edit those using VBA.

Scope of Userinterface Changes

Another thing that has changed with Excel 2007 is the scope of User interface changes. With previous versions of Excel (97 to 2003), any menu and commandbar changes were application-wide. This has changed with Excel 2007. If you add ribbon customisations to a "normal" xlsm file, you will only see them when that particular file is the active workbook. The only way to get application wide changes to the ribbon is by creating an add-in from your file and installing the addin.

<<Previous        Next>>

    Subscribe in a reader

Use the contact page to issue questions or comments about this website.
Copyright © 2003-2007 JKP Application Development Services.