Share via

Excel: Add the Analysis Group to a custom ribbon

  • Article

The way I figured this out was to search for the file name FUNCRES.XLAM referenced in the supertipof the Data Analysis button after the Add-In was installed. Then I opened the file using the Custom UI Editor Tool to view the XML. I saved an example file in GitHub.

  1. You can reference the action but not the controls of custom xlam e.g. onAction="FUNCRES.XLAM!ShowATPDialog".

  2. You can add the button to your custom group with the following XML. FYI, the Add-In must still be loaded for the button to work.

XML Code

<button 
    id="btnDataAnalysisTools"
    label="Data Analysis"
    imageMso="XmlMapProperties"
    onAction="FUNCRES.XLAM!ShowATPDialog"
    size="large"
    screentip="Data Analysis Tools"
    supertip="Tools for financial and scientific data analysis. Referenced from FUNCRES.XLAM"
/>

Example video

https://i.stack.imgur.com/tktGn.gif

2a. The following options are available if you want to load an Add-In at runtime. Reference

  • Application.RegisterXLL
    • This is a method which can be called from VBA which loads an XLL at a specific location and registers the functions and commands contained in the XLL.
  • AddIns.Add
    • This is a method which can be called from VBA which loads any type of add-in (XLL, XLA or XLAM). After loading the add-in, perform step 3 to open it.
  • AddIn.Installed = true
    • Once you have a reference to a loaded add-in, set AddIn.Installed = true to cause the add-in to be opened.
    • Note that add-ins that are known when Excel is started with the /automation switch will already be marked as "Installed", but they are not opened. In this case, Set Installed = false before setting Installed = true

VBA Code

Private Sub Workbook_Open()
    Dim success As Boolean
    Dim myAddIn As AddIn

    ' Load XLL
    success = Application.RegisterXLL("c:\myaddins\myxll.xll")

    ' Load and install new XLAM
    Set myAddIn = Application.AddIns.Add("c:\myaddins\myxlam.xlam")
    myAddIn.Installed = True

    ' Load known XLAM
    For Each myAddIn In AddIns
        If myAddIn.Name = "myknownaddin.xlam" Then
            myAddIn.Installed = False
            myAddIn.Installed = True
        End If
    Next
End Sub