Building an add-in for Excel

Content

• Introduction
• Download
• Specification
• Core functionality
• The user interface
• Application events
• Related articles

Introduction

Excel is a very powerful application with thousands of options and functions to help you build your models and reports and analyses. Oftentimes your daily job could do with some additional functionality. Something Excel does not offer "out of the box".

With VBA, augmenting Excel with new functionality is possible. This article demonstrates how I created a small utility. It outlines all steps I used to create the tool. The principles I used in these steps may help you when set out to build your own add-in.

Download

I prepared a sample workbook for you to download.

If you like the ToC button, we offer an Insert ToC button on steroids in our RefTreeAnalyser add-in

Specification

An important step in any software development process is defining what your application is supposed to do, what it must be capable of. Let's try and put that together here.

Goal

The primary goal of the add-in is to provide:

• A simple way for an Excel user to create and maintain a Table Of Contents in a workbook
• A simple worksheet navigation helper on the ribbon (so you can quickly jump to other worksheets)

As you may imagine, this needs more detail before you can jump head-first into the VBA editor to start building this thing. We need more specs.

More detailed specification

Toc operation

• The ToC will be placed on a worksheet called ToC, with a table starting from cell C2
• The table will be Formatted as a Table
• The table will contain a list of all worksheet names in column C, a direct link to each sheet in column D and some space for remarks about each sheet in column E
• The remarks are retained when the table is updated, ensuring the remarks stay with their associated worksheet, based on the worksheet's name
• A screenshot of what the ToC might look like:
  

Ribbon

• The tool will have a custom ribbon tab, with one group, containing:
  • a dropdown listing all worksheets in the active workbook (when a sheet is picked, it is activated)
  • an "Update ToC" button which updates the table of content:
    

Other features

• If the user switches to a different workbook, the sheet list on the ribbon is automatically updated
• If the user switches to a different worksheet, the dropdown will display that sheet's name.

Are we ready to go?

Of course in real world software projects, lots of other things might be needed first, in no particular order:

• Get funding; someone has to pay your bills
• Do market research (if you plan to sell the thing, might be nice if there is a demand for it)
• Google. Twitter. Ask your neighbor. Make sure you are not trying to build something that already exists! Chances are someone was in the same situation as you and has a ready-made add-in you can just use.
• Planning: when should you be done with the project?

Other things to think about

Here are some more things that you might need which often have little to do with the core functionality of the add-in:

• Designing the user-interface
• Internationalisation
• Bugs to work around
• Installation
• Activation (if you are selling licenses, how do you ensure people actually pay)
• Demo version
• How do I issue updates

Time to start coding!

Core functionality

In my experience, add-ins for Excel have something in common when you are building them: the time you spend with the core functionality is only a small proportion of the entire time needed to complete the project. In fact, I think this goes for many software projects.

Creating the Workbook

An Excel add-in is nothing more than a workbook which has been saved as an add-in. You can edit an add-in directly and save it like any other workbook, but I prefer to do all development in a "normal" Excel workbook and then when I am done save-as my development workbook as an add-in.

So open a fresh, brand new workbook (I opened the VBA editor alongside the Excel window, showing just the project explorer):

Kind of smells like a new car, don't you think?

Inserting a module

The basis of this tool is inserting and updating a table of content worksheet named ToC. Since I opted to have a comment line next to each sheet's entry, we need to temporarily store those comments and (after refreshing the ToC) add them back where they belong: with their associated sheet names.

So first, insert a module:

and change the module name in the properties window:

Your VBA project should look like this now:

Double-click on modTOC to open the code window associated with it.

The core code

Without explaining in detail what the code does (I think you should be able to figure it out using the comment lines) this is what I have in that module:

As you may have noticed, this code doesn't work. Worse: it generates a compile error if you try to run UpdateTOC:

This is because there is a call to a function called "IsIn" which we have not inserted yet. IsIn is a function originally suggested to me a long time ago (when I myself was still a VBA freshman) by my fellow Excel MVP Bill Manville.

Functions module

Since we might need more generic functions, let's insert a new module called modFunctions. All it needs at the moment is this code:

Now try to run UpdateTOC, it should work!

The user interface

Add-ins need a way for the user to interact with them. They may need custom forms (Userforms), perhaps create worksheets for data entry and reporting, right-click menu's and other user interface additions. This add-in will work using some items on a tab on the ribbon.

Ribbon modifications

Our add-in will only have a very limited userinterface, everything to use the add-in will be located on the Ribbon. Creating a custom ribbon userinterface is not for the faint at heart. Luckily we have an excellent resource:

• The # 1 book on the ribbon: RibbonX: Customizing the Office 2007 Ribbon

Using the Custom UI Editor, I opened the workbook I am working on (I previously saved and closed the workbook from Excel!).

I inserted an Office 2007 CustomUI part into the workbook:

Using this part ensures the add-in shows up in the Excel ribbon for any version. Then I wrote this xml code in the window (well, I copied more than half of it from some example and modified it to fit my needs, like any programmer would do):

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxJKPSheetToolscustomUI_onLoad">
    <ribbon>
        <tabs>
            <tab id="rxJKPSheetTools" label="SheetTools">
                <group id="rxJKPSheetToolsGeneral" label="Sheet Tools">
                    <dropDown id="rxJKPSheetToolsbtnSheets"
                        getItemCount="rxJKPSheetToolsbtnSheets_Count"
                        getItemLabel="rxJKPSheetToolsbtnSheets_getItemLabel"
                        onAction="rxJKPSheetToolsbtnSheets_Click"
                        sizeString="MMMMMMMMMMMMMMMMMMMMMMMM"
                        label="Sheets"
                        getSelectedItemIndex="rxJKPSheetToolsbtnSheets_getSelectedItemIndex"
                    />
                <button id="rxJKPSheetToolsbtnInsertToc"
                    label="Table Of Contents"
                    size="large"
                    onAction="rxJKPSheetToolsbtnInsertToc"
                    imageMso="CustomTableOfContentsGallery" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The Custom UI editor looks like this after entering this code:

A number of the items you see in this xml are so-called callbacks. That means Excel is expecting VBA code for them in the workbook in question. You can click the right-most button on the toolbar of the Custom UI editor screen to have it generate the empty Sub ... End Sub stubs for you.

The xml contains these callbacks:

• onLoad (called when the workbook loads)
• getItemCount (returns the number of items for a dropdown control)
• getItemLabel (fetches the nth item for the dropdown)
• onAction (called when a button is clicked or a dropdown selection is made)
• getSelectedItemIndex (called to fetch the item in the dropdown that needs to be selected)

You may have noticed the convoluted routine names (like "rxJKPSheetToolsbtnSheets_Click"). Especially the "rxJKPSheetTools" part. Callback names must be unique in an Excel instance. And I really mean Unique. No other workbook open in Excel can have a sub name that coincides with any of your call backs. Otherwise you risk that the routine in the other workbook is called by the ribbon! This is why I prefix the routine names with something that uniquely identifies the workbook that contains the RibbonX code.

After copying the code stubs from the UI editor, I inserted a new module called modRibbonX and pasted the stubs into it:

Application events

In the current state of our add-in, the ribbon is loaded and populated with the worksheets in the active workbook. But that is all; if we switch to a different workbook, we end up with the wrong set of sheets. There is nothing in the code that handles updating the ribbon.

Necessary events

So what do we need? We want the ribbon to be updated as soon as:

• I switch to another sheet
• I hide or unhide a sheet
• I switch to another workbook

Normally if you are writing code in a normal workbook, you would be adding code to the ThisWorkbook module. You would use events like these:

But these events only respond within that workbook. We need these events for any workbook. Events like that are called Application events.

A class module

So hold your braces, we're going to insert a Class module. Yes, we are! In order to capture application events we need to do that.

Chip Pearson has written an excellent article on Application events. I don't need to go there any more to figure out how to do this, but I encourage you to bookmark his page and head over one day to read it.

I've inserted a class module and named it clsApp:

At the top of the class module I declared an object variable using the special keyword "WithEvents":

This tells the compiler that the variable "App" will hold a pointer to an object that exposes events to us. In this case, the Excel Application. Nothing obvious appears to happen, but if you did this right, an item has been added to the left-hand dropdown of the codepane (Class and (General) are always there):

If you select "App", the VBA editor will automatically insert the default application event in your module:

That is very often the wrong one, we'll remove that later. But before doing so, click the right-hand dropdown:

That's right, it shows you all available events for the application object! Select the ones you need in turn until you get this:

Note that I added calls to InvalidateRibbon, a subroutine in modRibbonX that looks like this:

This simple "Invalidate" method tells Excel to completely redo the callbacks to populate our ribbon controls. As simple as that. I also added the Class Terminate event to ensure the App variable is neatly set to nothing when the class is terminated. Housekeeping is also important for VBA developers!

Getting the class to work

A class module is nothing more than a code blueprint. Unlike a normal module, it does not work out of the box. You have to load a copy of the class into memory. It is that copy (developers call it an "instance") that will do the work. This I did from a normal module which I inserted and called modInit.

At the top of that module, there is a module-level object variable called mcApp that will keep a pointer to the instance of the clsApp class. This is needed to ensure the class is not terminated as soon as the routine that created it reaches its End Sub statement:

I think the comments in this code should be clear enough. If not, perhaps now is the time to go to that Chip Pearson article I told you about...

Finally, we want this code to be called when the workbook opens so the add-in starts responding to those events immediately. So in ThisWorkbook I added:

I use the OnTime method, because that is an old trick that ensures Excel is completely done loading everything before Init is called. Sometimes Excel fires a workbook_open event even before it is done with its housekeeping. Well, this trick ensures Excel finishes first.

Creating the add-in

We've done the hard part of creating our tool, we're done programming. What is left to do is converting your workbook to an add-in and testing the tool.

Creating an add-in is as simple as clicking File, Save As:

In the save-as window, click the "Save As Type" dropdown and select "Excel Add-in (*.xlam)":

When you select Excel Add-in, Excel is kind enough to confuse you by changing the folder to the standard add-ins folder on your system. Since I NEVER want to save my add-ins there, this really annoyed me. Until someone pointed me at the fact that you can simply click the Back button to take you back to the original folder you last opened a file from (or saved a file to):

That's it, press Save and your add-in is ready for publication!

Related articles

Perhaps these articles are also of interest:

• A series about distributing macro's I've written quite some time ago.
• An article about Updating an add-in through the internet
• An article about installing add-ins using Setup Factory
• An article about fixing links to UDFs in your add-in
• An article about keeping your userform on top of Excel's windows