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

Ron de Bruin's website
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:

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

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:

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

Comments

All comments about this page:

Comment by: gerdami (5-5-2015 11:13:36) deeplink to this comment

Great tutorial!
Thank you.

Comment by: Felipe Costa Gualberto (5-5-2015 15:22:47) deeplink to this comment

Great tutorial!

"How I wish the designers of (Excel) VBA had been smart enough to decide up front whether all object collections, lists and arrays would start at a zero index or at a 1 index. But they didn't so we're stuck with the inconsitencies resulting from that."
+1

Comment by: D. Dunn (8-5-2015 19:03:29) deeplink to this comment

How would I update a control on the ribbon? For example I'd like to add a label to the ribbon to display the name of the active worksheet. It's redundant, I know, :-) but should be able to translate to other controls.

Comment by: Jan Karel Pieterse (9-5-2015 11:43:53) deeplink to this comment

Hi Jango,

You would use the getLabel callback of the label control.

So in RibbonX:

And in VBA:

Public Sub Labelcontrol1_getLabel(control as IRibbonControl, ByRef returnedVal)
'
' Code for getLabel callback. Ribbon control labelControl
'
returnedVal = ActiveSheet.Name
End Sub

Comment by: Doug Glancy (11-5-2015 05:33:49) deeplink to this comment

One thing I've found is that it's good to give ribbon variables unique names, e.g., g_ribbon_myAddin. At first I just called them all g_ribbon, but had some crashes when there was more than one open project using that variable name. The problem went away once I made the variable names unique.

Comment by: Jan Karel Pieterse (11-5-2015 09:09:06) deeplink to this comment

Hi Doug,

Thanks. I expect you're referring to the id attributes in the RibbonX code?

Comment by: Doug Glancy (12-5-2015 03:28:08) deeplink to this comment

No, I'm just talking about the name of the global ribbon variable, e.g., your "moribbon". I haven't really tested to confirm the problem. I just know that when I started giving each ribbon its own variable name the crashing stopped.

Comment by: Jan Karel Pieterse (12-5-2015 08:09:13) deeplink to this comment

Hi Doug,

That is really odd. I have never seen conflicts between identical variable names in different VBA projects in Excel.

Perhaps this is more about not having unique callback subroutine names?

I have seen crashes when using the trick to recapture the ribbon instance using win API's when the pointer address wasn't actually stored at initialisation so the wrong address was used.

Comment by: Doug Glancy (13-5-2015 04:52:17) deeplink to this comment

It could be a callback name issue as easily as a variable name issue, since I changed the callback names to match the new, unique, variable names.

I do use the API trick but it doesn't seem like that's the issue.

Like you, I'd never seen a variable name conflict, but the ribbon does seem to create some unique circumstances. For instance I take all the code that would normally be in the addin's Workbook_Open event and put it in the Ribbon_OnLoad event. Otherwise I get another type of conflict, as described in one of my first posts: http://yoursumbuddy.com/a-workbook-hooker-with-no-ribbon-related-fatalities/.

Comment by: Jan Karel Pieterse (13-5-2015 13:16:15) deeplink to this comment

Hi Doug,

Well, I've seen the API trick cause havoc. The onload callback name must also be unique to any other ribbonX loaded in Excel at that time, otherwise it'll call the wrong routine and the pointer you stored is outdated.

Comment by: Doug Glancy (14-5-2015 17:11:15) deeplink to this comment

Jan Karel, sounds like you've identified the issue. I changed both the variable and callback names at the same time and I do use the pointer storage trick.

Thanks for clearing that up!

Comment by: Jeff Weir (29-5-2015 00:32:48) deeplink to this comment

Hi Jan Karel. Suggestion: Add err.clear before the End Function, to avoid this triggering error handling in the calling sub.

Comment by: Jan Karel Pieterse (1-6-2015 08:42:02) deeplink to this comment

Hi Jeff,

The err.clear has little to do with triggering an error handler (or not). It is the On Error Resume Next in the function that avoids the trigger of an error handler higher up the call stack. Hence Err.Clear is not needed, unless you are using Err.Number = 0 to find out whether an error has been triggered somewhere.

Comment by: Jeff weir (1-6-2015 11:08:53) deeplink to this comment

Sorry, I wasn't very clear in my comment. Agreed, the err.clear makes no difference to this sub. However, in my specific case, lots of my routines have an error handler like this:

errhandler:
If Err.Number <> 0 Then
'error handling code
end if

...so for my needs, I've added the err.clear to your function, so it doesn't inadvertantly trigger that error handler in the calling sub.

Comment by: Jan Karel Pieterse (1-6-2015 11:12:28) deeplink to this comment

Hi Jeff,

Are you saying there is no exit sub or function above your error handler label? That'd be odd?

Comment by: Jeff Weir (1-6-2015 11:37:05) deeplink to this comment

Correct, I don't have an exit above the error handler. Ah...I didn't realise my approach wasn't that common, and of course the lack of err.clear in your routine is only a problem for me because of my different approach.

I use Craig Hatmakers' approach

He doesn't use Exit Function or Exit Sub to bypass the routine but rather just wraps his error handling code in an error number test because he feels that's closest to the spirit of Structured Programming Techniques. But it's probably also furtherest from what most VBA developers do in the wild.

So he/I use something like this

Select Case err.number
Case = 0 'Do nothing
Case x
Case y
End Select

Comment by: Jan Karel Pieterse (1-6-2015 12:32:55) deeplink to this comment

Hi Jeff,

That is indeed an unusual approach. Doesn't mean the approach is wrong :-)

Comment by: Jan Albert (20-8-2015 14:45:06) deeplink to this comment

Dank je wel Jan Karel!

Comment by: David Barton (22-11-2015 16:13:00) deeplink to this comment

Hi
This is a very nicely explanation of using call backs. Has helped me a lot and I will use it in the future.

Only one comment. I think that the getSelectedItem Index needs to be modified to that the correct sheet name is display when there are hidden sheets in the workbook. Perhaps something like:

     Dim lCount As Long
     Dim obj As Object

     For Each obj In Sheets

         If obj.visible = xlSheetVisible Then lCount = lCount + 1

         If obj.Name = ActiveSheet.Name Then
             returnedVal = lCount - 1
             Exit Sub
         End If

     Next

Many thanks for creating and maintaining this site

Thanks
David

Comment by: Jan Karel Pieterse (23-11-2015 17:12:50) deeplink to this comment

Hi David,

Thanks!

Comment by: Lorraine Gouws (14-12-2015 14:01:51) deeplink to this comment

Hi, I recently met you at the Global Training Camp at Microsoft in London. You very kindly advised me on creating an Addin to customise my ribbon across several versions of Excel.

The issue I'm having is that the Addins tab doesn't appear and I need it to display 3rd Party Addins, so in the CustomUI I created a custom Addins tab and it displays the new tab, but none of the groups are visible:

<tab id="MyAddInsTab" label="Addins" InsertAfterMso="TabAddins" getVisible="GetVisible" tag="ribaddin">
<group idMso="GroupAddInsMenuCommands" />
<group idMso="GroupAddInsToolbarCommands" />
<group idMso="GroupAddInsCustomToolbars" />
</tab>

I was hoping my 3rd Party Addins would appear in the menu commands group.

I've searched all morning on the internet on how to display 3rd Party Addins using CustomUI, but I can't find an answer.
Are you able to advise?

Many thanks
Lorraine

Comment by: Jan Karel Pieterse (14-12-2015 15:49:39) deeplink to this comment

Hi Lorraine,

I would expect the customisations to appear automatically on that add-ins tab without needing any ribbon xml code in an add-in, as this is something Excel handles itself as soon as any custom commandbar is added to the userinterface.

Comment by: WILLIAM FREUND (18-10-2016 18:15:32) deeplink to this comment

The link on the "Building an Add-in For Excel" page that reads "I prepared a sample workbook for you to download" takes me to your "Downloads" page, however I am unable to locate an item in the list of Downloads that "sounds like" it refers to .... whoops, never mind, I just took another look at the download list and noticed the item called "SheetTools" with the notation that reads "this is the file that accompanies my article on 'How to build an Excel Add-in'.

Incidentally, I have been advised (by women) that my inability to locate things that they (the women) have subsequently been able to locate for me is a common condition known as "man looking". :-)

Keep up the good work lads and ladies,
Bill Freund

Comment by: Jan Karel Pieterse (19-10-2016 08:14:13) deeplink to this comment

Hi Bill,

LOL glad you were able to locate the file. I'll add a direct download link though.

Comment by: Neva (5-4-2017 19:19:25) deeplink to this comment

In this article:
https://jkp-ads.com/Articles/buildexceladdin.asp

About midway down you state the following:
"Note that I added calls to InvalidateRibbon, a subroutine in modRibbonX..."

Question:
Where is "modRibbonX" located in the project referenced by this article?

Thank you for your time.
~Neva.

Comment by: Jan Karel Pieterse (6-4-2017 11:20:29) deeplink to this comment

Hi Neva,

Look at the demo workbook that accompanies this article, which you can download from here:

https://jkp-ads.com/downloadscript.asp?filename=sheettools.zip

Comment by: Hasan (4-1-2018 09:27:26) deeplink to this comment

Hi Jan,

would it be possible to use the WindowActivate event instead of WorkbookActivate - just in case there are multiples windows of the same workbook?

Hasan

Comment by: Jan Karel Pieterse (4-1-2018 09:34:43) deeplink to this comment

Hi Hasan,

Of course! Good suggestion.

Comment by: Anthony (24-1-2018 04:48:04) deeplink to this comment

Nice code to hold the onLoad pointer. But is the following safe in 64bit?

ThisWorkbook.Worksheets("Sheet1").Range("RibbonPointer").Value = ObjPtr(moRibbon)

What if ObjPtr is > than 2^48 (floating point). Would a CSTR help?

Also, what about storing it in an Application named ranges using XL4 -- then it is truly transient.

(The fact that any of this is necessary shows the contempt that Microsoft has for VBA.)

Comment by: Jan Karel Pieterse (25-1-2018 09:47:08) deeplink to this comment

Hi Anthony,

Very valid remark, I don't really now to be honest!

Comment by: Anthony (25-1-2018 12:01:16) deeplink to this comment

I changed the code to set the value to
"'" & Cstr(ObjPtr(moRibbon))

And then Clnglng the value back if Win64.

Seems to work Excel 64. (The issue is how memory is arranged, and whether in practice only the lower 48 bits is used.)

I am also thinking of using the XL4 approach to store it as an application Name,

SET.NAME("Ribbon Handle", ...)

(The ribbon API is appalling. Rather than just set a property, you catch an event to get a handle to fire an initialize event which can be caught to return the value!! Nothing learnt from the existing elegant APIs. At the very least they could provide Application.RibbonHandle. But they won't, because they do not care. Wot's hot and wot's not. VBA, the soul of Excel, is not.)

Comment by: Jan Karel Pieterse (25-1-2018 14:06:44) deeplink to this comment

Hi Richard,

Are you absolutely sure the conversion to Excel works properly if the address is larger than a 32 Long? I'm not sure whether CStr does that properly?

Agree with your comments about the Ribbon.
Seems internally they rigged the callbacks into VBA by using Application.OnTime given what happens if you are debugging code and the ribbon starts firing callbacks.

Comment by: Anthony (26-1-2018 00:42:18) deeplink to this comment

Yes, tested along lines of

dim x as longptr
x = 1
x = x * 256 * 256 * 256 * 256 * 256 * 256 * 256
print cstr(x)

Comment by: Jan Karel Pieterse (26-1-2018 19:44:56) deeplink to this comment

Hi Anthony,

Thanks. WOuld you be willing to show the full working code?

Comment by: Grzegorz (2-2-2018 14:02:44) deeplink to this comment

Hi, I want to use your way to invalidateRibbon, however:
When I add your add-in to Excel addins, then whenever I want to open any XLS files, my excel dont respond, I have to close it by manager task.
It works perfectly only if I run Excel first, then excel file...

Comment by: Jan Karel Pieterse (8-2-2018 07:19:45) deeplink to this comment

Hi Grzegorz,

One thing that is very, very important when you use this method is that you must make sure you do not try to use a pointer which is wrong. If you save the file with an old pointer value and then re-open it, Excel will probably crash because you try to assign the ribbon object using the wrong value for the pointer. So add a before-save event which clears the pointer from the cell and add a check for the content of the cell too.

Comment by: sifar (1-9-2019 10:27:00) deeplink to this comment

Is this line required?

sName = Application.Substitute(sName, "'", "")

Comment by: Jan Karel Pieterse (2-9-2019 11:31:00) deeplink to this comment

Hi sifar,

This line is there to make sure leading and trailing apostrophe's are removed prior to the check as those apostrophe's are likely just surrounding the actual name.

Comment by: @chernipeski (17-5-2020 03:52:00) deeplink to this comment

So I am to ask my user base to go into developer mode and put this code into every new workbook they create?

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

Surely you jest?

Comment by: Jan Karel Pieterse (18-5-2020 11:43:00) deeplink to this comment

Hi chernipeski,

I'm not sure I understand your remark?

Comment by: @chernipeski (20-5-2020 00:54:00) deeplink to this comment

(1) I code my add-in
(2) I publish my add-in on the company network, Internet, or send it in a message
(3) My user selects my Add-In
(4) My user opens a new Workbook
(5) how does your initialization code get into their brand-new Workbook?

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

Comment by: Jan Karel Pieterse (20-5-2020 10:42:00) deeplink to this comment

Hi chernipeski,

The article does not explain how to update VBA code using VBA code. To do so, have a look at this article by Chip Pearson: http://www.cpearson.com/Excel/vbe.aspx

Comment by: @chernipeski (29-5-2020 11:08:00) deeplink to this comment

That's a cool article. Ok here's the situation. I have a VBA add-in. It is an event handler that captures a double-click on any cell in the user's workbook. Works great when I'm running my own source workbook. When I publish the xlam file and my customer uses my add-in, and double-click's on a cell nothing at all happens. This is because the add-in never initializes the add-in module that establishes the event handler. All the events are captured by the module my client's workbook establishes when he creates that. My add-in modules are completely ignored.

What does my user have to do to make the double-click event handler in the Add-In he bought from me begin to work? The answer should be NOTHING. He bought the handler, it should handle things on it's own. But for sake of discussion, what do you all suggest my customer should do to make my event handler work in his workbooks?

BTW here it is: www.facebook.com/datepickr

Comment by: Jan Karel Pieterse (29-5-2020 12:05:00) deeplink to this comment

Hi Gary,

Perhaps this: https://jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp

Comment by: Gordon A Morris (25-10-2020 22:46:00) deeplink to this comment

Your section on application events using class modules was excellent. It solved a problem with triggering events in an add-in which I had been working on for to long! Many thanks for a clear and accurate tutorial.

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.