Building an add-in for Excel

Content

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:
    Mockup of the ToC

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:
      The Ribbon

Other features

  • If the user switches to a different workbook, the sheetlist 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):
A new Excel workbook and its VBA project in 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 sheetnames.

So first, insert a module:

Inserting a module

and change the module name in the properties window:

The Name property of the module

Your VBA project should look like this now:

The VBAProject with one module

Double-click on modTOC to open the codewindow 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:

'-------------------------------------------------------------------------
' Module    : modMain
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (jkp-ads.com)
' Created   : 30-4-2015
' Purpose   : The main routines of the tool
'-------------------------------------------------------------------------
Option Explicit

Public Sub UpdateTOC()
'-------------------------------------------------------------------------
' Procedure : UpdateTOC
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse (jkp-ads.com)
' Created   : 30-4-2015
' Purpose   : Creates (or updates) a Table of Content sheet
'-------------------------------------------------------------------------
    Dim oSh As Object
    Dim oToc As Worksheet
    Dim vRemarks As Variant
    Dim lCt As Long
    Dim lRow As Long
    Dim lCalc As Long
    Dim bUpdate As Boolean
    bUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    lCalc = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Check if worksheet ToC exists, if not, insert one
    If Not IsIn(Worksheets, "ToC") Then
        With Worksheets.Add(Worksheets(1))
            .Name = "ToC"
        End With
        Set oToc = Worksheets("ToC")
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayHeadings = False
    Else
        Set oToc = Worksheets("ToC")
        'We have an existing ToC, store the entire table in an array
        'so we can restore comments later on
        vRemarks = oToc.ListObjects(1).DataBodyRange
    End If
    'Check for a table on the ToC sheet, if missing, insert one
    If oToc.ListObjects.Count = 0 Then
        oToc.Range("C2").Value = "Werkblad"
        oToc.Range("D2").Value = "Snelkoppeling"
        oToc.Range("E2").Value = "Opmerkingen"
        oToc.ListObjects.Add xlSrcRange, oToc.Range("C2:E2"), , xlYes
    End If
    On Error Resume Next
    'Empty the table
    'Ignore errors in case the table already is empty

    oToc.ListObjects(1).DataBodyRange.Rows.Delete
    For Each oSh In Sheets
        If oSh.Visible = xlSheetVisible Then
            lRow = lRow + 1
            oToc.Range("C2").Offset(lRow).Value = oSh.Name
            oToc.Range("C2").Offset(lRow, 1).FormulaR1C1 = _
                "=HYPERLINK(""#'""&RC[-1]&""'!A1"",RC[-1])"
            oToc.Range("C2").Offset(lRow, 2).Value = ""
            'Restore the comment for this sheet
            For lCt = LBound(vRemarks, 1) To UBound(vRemarks, 1)
                If vRemarks(lCt, 1) = oSh.Name Then
                    oToc.Range("C2").Offset(lRow, 2).Value = vRemarks(lCt, 3)
                    Exit For
                End If
            Next
        End If
    Next
    oToc.ListObjects(1).Range.EntireColumn.AutoFit
    Application.Calculation = lCalc
    Application.ScreenUpdating = bUpdate
End Sub

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

Compile error, something's missing

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:

Option Explicit

Function IsIn(vCollection As Variant, ByVal sName As String) As Boolean
'-------------------------------------------------------------------------
' Procedure : IsIn Created by Jan Karel Pieterse
' Company   : JKP Application Development Services (c) 2005
' Author    : Jan Karel Pieterse
' Created   : 28-12-2005
' Purpose   : Determines if object is in collection
'-------------------------------------------------------------------------
    Dim oObj As Object
    On Error Resume Next
    Set oObj = vCollection(sName)
    If oObj Is Nothing Then
        IsIn = False
    Else
        IsIn = True
    End If
    If IsIn = False Then
        sName = Application.Substitute(sName, "'", "")
        Set oObj = vCollection(sName)
        If oObj Is Nothing Then
            IsIn = False
        Else
            IsIn = True
        End If
    End If
End Function

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 various excellent resources:

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:

Inserting a customUI part

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:

Custom UI Editor

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:

Option Explicit

Sub rxJKPSheetToolscustomUI_onLoad(ribbon As IRibbonUI)
End Sub

Sub rxJKPSheetToolsbtnInsertTOC(control As IRibbonControl)
End Sub

'Callback for rxJKPSheetToolsbtnSheets getItemCount
Sub rxJKPSheetToolsbtnSheets_Count(control As IRibbonControl, ByRef returnedVal)
End Sub

Public Sub rxJKPSheetToolsbtnSheets_getItemLabel(control As IRibbonControl, Index As Integer, ByRef returnedVal)
End Sub

'Callback for rxJKPSheetToolsbtnSheets getSelectedItemIndex
Sub rxJKPSheetToolsbtnSheets_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
End Sub

Sub rxJKPSheetToolsbtnSheets_Click(control As IRibbonControl, id As String, Index As Integer)
End Sub

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:

Private Sub Workbook_Activate()
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
End Sub

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:

Class module

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

Public WithEvents App As Application

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

The top-left dropdown of the classes code window

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

Private Sub App_NewWorkbook(ByVal Wb As Workbook)

End Sub

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

The lefthand dropdown, showing the available events

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

Private Sub App_SheetActivate(ByVal Sh As Object)
    InvalidateRibbon
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    InvalidateRibbon
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    InvalidateRibbon
End Sub

Private Sub Class_Terminate()
    Set App = Nothing
End Sub

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

Sub InvalidateRibbon()
    moRibbon.Invalidate
End Sub

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:

Option Explicit

'Variable to hold instance of class clsApp
Dim mcApp As clsApp

Public Sub Init()
    'Reset mcApp in case it is already loaded
    Set mcApp = Nothing
    'Create a new instance of clsApp
    Set mcApp = New clsApp
    'Pass the Excel object to it so it knows what application
    'it needs to respond to
    Set mcApp.App = Application
End Sub

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:

Private Sub Workbook_Open()
    Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!Init"
End Sub

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:

Save-As

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

Save As type

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

Back button

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

Perhaps these articles are also of interest:


Comments

Showing last 8 comments of 53 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (26-10-2020 09:41:00) deeplink to this comment

Hi Gordon,

Thanks you, good to hear the article has been useful for you!


Comment by: Mohashin Bhyian (8-9-2021 06:12:00) deeplink to this comment

Many thanks for the nice and detailed tutorial. I have tried this add-in. I am facing one problem.

Let's consider, I do not open any saved files from my computer. Now if I open a brand new Excel workbook (which is not still saved on PC), then This add-in is not working. I tried a hundred times to solve this but fail to figure this.

If after opening any saved files from my computer, then If I open a brand new Excel workbook (which is not still saved on PC), Then this is working very fine.

Can you please kindly help with this?


Comment by: Jan Karel Pieterse (13-9-2021 10:07:00) deeplink to this comment

Hi Mohashin,

What issue does the addin show exactly? An error message? If so, which?


Comment by: Mohashin Bhyian (19-1-2022 09:27:00) deeplink to this comment

Basically, this is not showing any error message, Excel file is closing automatically. This is tough to explain. That's why I have created a video for you. Please kindly see the below link.

https://www.youtube.com/watch?v=8F6JygFs9Yo


Comment by: Jan Karel Pieterse (24-1-2022 11:48:00) deeplink to this comment

Hi Mohashin,

I have fixed the issue, please download the sheettools zip file again and replace the xlam on your system


Comment by: Mohashin Bhyian (26-2-2022 15:18:00) deeplink to this comment

Dear JKP, I have tested in many ways. This is working fine. Also, I have tested in another way just for my learning.

Say, I copy the code of "Function IsIn()" from "modFunctions" module and paste it inside the "modRibbonX" module. Then I save it and close all excel and open a Brand new excel file, then this addin is not working. Can you please advise what is the reason?

If again I do copy-paste and make it to original then also This is not working. This made me very surprised.

Only I delete the addin and replace it with your addin, then only again it is working.


Comment by: Jan Karel Pieterse (26-2-2022 15:51:00) deeplink to this comment

Hi Mohashin,

Send your not working file (email) and I will have a look.


Comment by: Mohashin Bhyian (5-3-2022 06:42:00) deeplink to this comment

Dear Sir, I have sent the mail to "info@jkp-ads.com" with a modified Add-in file and screenshot. Thanks.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].