Multiple Add-ins

I’m creating several add-ins that will all use the same top-level menu item. The add-ins will be installed as needed. So someone may have just one and someone else may have half a dozen. The right answer, I think, is to have one controlling add-in that handles the menus (and not much else), but I’ve already started down another path, so I’m going to see where that leads me.

To identify which add-ins use my top-level menu, I use a CustomDocumentProperty. I can then create a function to determine if any other add-ins are loaded.

Public Function AISAppExists() As Boolean
    Dim bReturn As Boolean
    Dim ai As AddIn
    bReturn = False
    For Each ai In Application.AddIns
        If ai.Name <> ThisWorkbook.Name Then
            If ai.Installed Then
                On Error Resume Next
                    bReturn = Workbooks(ai.Name).CustomDocumentProperties(gsCDPAPP).Value
                On Error GoTo 0
                If bReturn Then Exit For
            End If
        End If
    Next ai
    AISAppExists = bReturn
End Function

In my CreateToolbar procedure:

If Not AISAppExists Then
    Set cButtonMain = Application.CommandBars(1).Controls.Add(msoControlPopup, , , FindHelp)
    cButtonMain.Caption = gsCBMAIN
    Set cButtonMain = Application.CommandBars.FindControl(, , gsCBTAG1)
End If

That gives me a cButtonMain object under which I can build my menu. If a sister add-in exists, then go get the top level menu item. Otherwise, create a new one.

I use two global constants for tagging the controls, gsCBTAG1 and gsCBTAG2. gsCBTAG1 is the same for all related add-ins and is only put on the top level menu item. gsCBTAG2 is different for all related add-ins and is used to tag controls one level below the top level menu item.

In DeleteToolbars:

If Not AISAppExists Then
    Set ctl = Application.CommandBars.FindControl(, , gsCBTAG1)
    If Not ctl Is Nothing Then
    End If
    Set ctls = Application.CommandBars.FindControls(, , gsCBTAG2)
    If Not ctls Is Nothing Then
        For i = 1 To ctls.Count
        Next i
    End If
End If

If this is the last app, then delete the top level menu item and be done with it. If there is still a related add-in open, then delete all of the second level menu items for this add-in. I don’t tag menu items lower than that because deleting a higher level automatically deletes the lower level.

So the first add-in creates the top level menu item and all subsequent add-ins use it. The last add-in to be unloaded deletes the top level menu item and all previous ones just deleted their portion. It seems to work, but your comments are welcome.

One problem is code duplication. I have very similar code in my toolbar modules in all of the add-ins. A change to any of that code means changing it in several places.

Posted in Uncategorized

4 thoughts on “Multiple Add-ins

  1. “One problem is code duplication.”

    Keep all the related code in its own module. When you have to change it, do so in one add-in, then replace the module in the other add-ins with the modified module. Not perfect, but not too much of a bother.

  2. Sounds very confusing to me. Have you ever looked at the source code for my PUP add-in? It’s one add-in that controls about 50 other add-ins. They are loaded as needed, and unloaded when subsequent ones are loaded.

    Let me know if you want the password.

  3. Dick,

    Like John said, it sounds very confusing. Also, in every version of Excel where I have tried to have 2 add-ins communicate with each other, sooner or later I have got an error along the lines of “the callee has disconnected and cannot be reached.”

    My approach to multiple add-ins with overlapping menus is the following. A table contains information about all the menus needed by a particular add-in. This table can be an Excel range or an array. The latter is used for Word and PowerPoint add-ins.

    I have designated one add-in as the “master” for code that is common across all my add-ins. This code includes commandbar, help, version, registration and website handling. If I have to make any changes to those routines, the changes always start with this one add-in. The commandbar, i.e., menu, handler is all in one class module. Each add-in’s Open and BeforeClose event routines simply create an instance of the menu class and call the appropriate method (CreateMenus or DeleteMenus) with the appropriate argument(s).

    [I’ve had to add two additional modules (one a form, the other a standard module) to get add-ins to play nice across the 2003-2007 divide.]

    CreateMenus goes through every row in the table passed to it as an argument. Each row contains information about one menu item, including the menu hierarchy, which includes the following: Application commandbar name, (optional) name of the ‘top level’ menu within the application commandbar, (optional) name of ‘pop up’ submenu under the ‘top level’ menu, and the menu item itself.

    The code adds any menus / submenus as needed until it can create the menu item itself. By making the top level menu optional, the same code can create items in context menus (such as the Cell commandbar that is accessed by a right click on a cell).

    The DeleteMenus uses the same table as the CreateMenus routine to reverse the process. It deletes the menu item. Then, if the submenu has zero children, it deletes the submenu. Then, if the ‘top level’ menu has zero children it deletes it.

    Also, the menu handling code includes a mechanism whereby it creates menu items that work in an application add-in (using the OnAction property) or a COM add-in (event driven).

    To create different menu setups for different clients / environments requires just a change in the entries in the table in the respective add-ins. No code changes needed.

    IMO, clean, self-contained, easy to maintain, and all add-ins play nice.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.