This post may contain affiliate links. Please read my disclosure for more info:

Build your own Add-in: Calculation Timer

Build your own Excel Add-in

Build your own Excel Add-in

In this post, I will show you, step-by-step, how to create an Add-in with its own custom Ribbon.  Rather than just creating an example Add-in which says, “Hello World!”, we will build something useful – a calculation timer.  The process of creating any Add-in will be similar, so you can apply these principles to your own Add-ins.

 

The Ribbon

The Ribbon was introduced in Excel 2007.  Many users, myself included, were in uproar.  This completely changed our way of working and broke a lot of our custom Add-ins.  But that was a while ago, and now I can barely remember a time before the Ribbon.

The Ribbon is coded in the XML language and forms part of the Excel file itself.  As a result, it is not possible to create the Ribbon on-the-fly just using VBA.  A number of other articles for creating custom Ribbons use the Custom UI Editor, but I’ve always used Andy Pope’s Ribbon Editor Add-in.  I initially used this tool because it doesn’t require an install, it’s an Excel Add-in.  This means you don’t need to beg permission from your line manager or IT department to use it.

If a Ribbon has been set-up the right way,  we can change the label, visibility, actions, etc. through VBA.   But, for this post, we will focus on creating a reasonably static Ribbon, dynamic control of the Ribbon can be the topic of a future post.

 

Step 1: Ribbon X Visual Designer Add-in

The first step is to go to Andy Pope’s website and download the relevant version (currently the 32-bit or 64-bit versions) of the Ribbon Editor Add-in (known as Ribbon X Visual Designer) http://www.andypope.info/vba/ribboneditor_2010.htm.

Once downloaded, unzip the file.  Then, double-click the RibbonEditor.xlam file to open it.

Ribbon Editor File

Depending on your security level, you may need to enable Macros.

Ribbon Editor Enable Macros

Within the Developer Tab, you will now find a new section called Ribbon Designer.

Ribbon Editor in Developer Ribbon

To load an existing file for editing, click on Loader.  However, as we are creating a new Ribbon, you can select Designer for now.  Click Excel from the drop-down menu.  If you wish your Add-in to be backward compatible with Excel 2007, then select the Excel under the CustomUI section, rather than the CustomUI14 section (which works with Excel 2010 and newer).

Ribbon Editor Excel CustomUI

The RibbonX Visual Designer will now load.  There are 3 main sections:

  • The elements which you can be added to your Ribbon (left side box)
  • The layout of the current Ribbon (excluding other Add-ins) (middle box)
  • The Ribbon and Button options (right box)

Ribbon Editor Interface

 

Step 2: Building the Ribbon

For our example, we will create one tab, with one group and four buttons (for your add-in change this to your requirements).

Select Tab from the list of Elements (left box), select Tabs from the current Ribbon structure (middle box), then click the Insert Item Button (the arrow from left to right – No 3 below).  A new Tab is created.  In the Properties change the Label and Id of the newly created Tab.  The Id should be a unique name, such as CalcTimer_Tab0001.

Ribbon Editor Create Tab

Next, click on the newly created tab.  Its name should change to the label you just created.

In the same way as above, add a Group into the new tab.  Give the group a new label and Id.

Next, add 4 buttons into the Group created above. Remembering to enter the label and a unique id.  For the buttons you will also want to add an onAction value.

The onAction is the name of the macro which will run when the button is clicked, so each onAction should be unique.  I have gone with the following as they are similar to the procedures in the macro (See Step 4 & 5 below):

  • CalcTimerFull_Click
  • CalcTimerRange_Click
  • CalcTimerRecalculation_Click
  • CalcTimerSheet_Click

Ribbon Editor Full Ribbon

For each button, select an image from the imageMso Icon Gallery.  I have selected CDAudioStartTime (it has a nice little clock and play symbol, which roughly reflects the purpose of the button).  Note, it is also possible to add your own images, using the image property box.

Click Save As, then save the file ensuring you select Macro-Enabled Workbook (*.xlsm) from the Save as type box.

 

Step 3: Add the Ribbon VBA code

The final step in the RibbonX Visual Designer is to click on the Callback VBA Stubs tab, then copy the code.  Now we can close the RibbonX Visual Designer.

Ribbon Editor VBA Stubs

Open the file created in Section 2 above.  Your new Ribbon should appear.

Ribbon Editor - New Ribbon

In the Visual Basic Editor (ALT + F11) add a Module and paste the code copied from above.

Ribbon Editor - Pasted VBA Code

 

Step 4: Add the Calculation Timer VBA code

The code for the Calculation Timer can be found here: Excel 2010 Performance: Improving Calculation Performance.  There are two sections of code half way down the document, copy and paste both sections of code into a new Module.

 

Step 5: Referencing the Calculation Timer from the Ribbon

Within the Calculation Timer VBA code there are four Macros:

  • RangeTimer()
  • SheetTimer()
  • RecalcTimer()
  • FullcalcTimer()

Call these Macros from your Ribbon VBA code as shown in the image below:

Ribbon Editor - Linked to Macros

 

Step 6: Test the Ribbon, save as an Add-in and install

In Excel, create a formula in a worksheet, then click the Ribbon buttons to test them out.  You should have a message box displaying the calculation time.

Ribbon Editor - Calculation Timer Working

Finally save the file as an Excel Add-in (ensure the file extension is .xlam).  Excel will default to saving the file in the default Add-ins location: C:\Users\[user_name]\AppData\Roaming\Microsoft\AddIns, but you can save the file elsewhere if you wish.

Ribbon Editor - Save as Add-in

Close the Macro-Enabled Workbook, as you no longer need this file.

Finally, to install your Add-in, click: File -> Options -> Add-ins.  Select Excel Add-ins from the Manage: drop down and click Go….

From the Add-ins window select Browse, find your Add-in and click OK.  Your Add-in should now be installed and ready to use.

 

Conclusion

You have just created your own Add-in from scratch.  I’m sure it wasn’t as difficult as you thought it might be.  Experiment a bit more with the Ribbon X Visual Designer, there are loads more settings and option in there.  Also, if you have any other Add-ins why not open those in the Ribbon X Visual Designer and see the settings which have been applied.

Leave a Reply

Your email address will not be published. Required fields are marked *