Home > Article index > Create add-ins > Menus

Creating an addin from an Excel macro

Pages in this article

Modifying the ribbon

You can manipulate the appearance of the ribbon. But not with VBA. See the figure below, with a screenshot of my RefTreeAnalyser tool's interface:

Ribbon of Excel, customised by adding a new tab
Ribbon of Excel, customised by adding a new tab

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!):

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:

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 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 and up 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.