Extending Microsoft Excel with custom functionality based on makros is easy to do even without any type of programming but just by recording and replaying the required steps. Sometimes, you need some functionality not possible to create by record/replay, so makros can also be programmed using Visual Basic.
Whatever concept you use to develop your custom functionality, you might want to access it through a simple button available in your menu bar (your ribbon) in your excel. Also you want it to be available everytime and not for a specific Excel workbook only.
While this sounds a pretty basic requirement, you need to combine multiple concepts to make this happen.
First: To enable permenantly available customizations, you need to use an Add-In.
Second: To extend the menu bar (the ribbons), you have to use makros.
In this post, I will describe those two steps in detail. The post is based on Office 2010 but you should be able to reproduce this with 2007 as well.
Creating an Add-In
To create a custom Add-In, you should create a new workbook (a new Excel file). Next, we need to develop the functionality we would like to provide.
You can do this by either recording a new Makro.
or by programming one using the Visual Basic Editor. For the latter, open the Visual Basic Editor (Strg+F11) and add a new module to your current excel workbook.
Note: It is important to add a new Marko (using a Sub definition in the Visual Basic Editor) and not to programm a new Function because only makros can be added later on to the tol bar. This is part of the second Excel concept you need to understand to extend your tool bar with custom functionality.
When you have finished your Excel makro, you can stop recording or close the Visual Basic Editor and save your Excel workbook as an Excel add in. To this, choose “Save as” from you file menu and select the file type xlam (xla in Excel version before 2010).
Excel uses the common Add-In directory by default. It is recommended to use this directory. Otherwise you need to configure Excel to load your new Add-In from the other directory. You need to restart Excel to force it to load the new Add-In.
As soon as your new Add-In is created, you can activate it in your Excel. To do so, go to File->Options->Add-Ins to open the Excel-Options dialog box.
At the bottom of this box, select Manage: “Excel-Add-Ins” and click on Go to…
A new dialog opens which allows you to activate and deactivate your Excel Add-Ins.
In this dialog, activate your new Add-In and click ok.
By this, your Add-In is permanently available in your Excel and you can use as much as you like to.
Adding Makro Functionality to your Excel Tool Bar
To manage and customize your tool bar, make a right click on your tool bar and choose to configure the tool bar.
In the new dialog, you can configure a new menu group and menu item for your custom makro. To do this, you need to select makros in the select box above the list of the available instruction.
Finally, when you have added your custom tool a menu group on the right, you can select it and click on the rename button to provide a different name and select an icon.