Excel Add-in (.xla, *.xlam)

To distribute the Excel function or procedure to user by keep it with Microsoft Excel program without opening the Excel file with function. Excel Add-in is a good choice, first, we have to convert our function to add-in and then install it in Microsoft Excel.

1. To create the Excel Add-in, this is simply save your workbook with macro in it and then save the Workbook as an Add-In (*.xla or *.xlam) in any path.

2. You can add a command to call the function from the ribbon by adding this code in the Workbook (Workbook_AddInInstall and Workbook_AddinUninstall), then save your Add-in file.

 
Option Explicit

Private Sub Workbook_AddInInstall()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
     
    On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left.
    Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete 'Test is name of control to show on ribbon
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "Test" 'names the menu item
        With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "Test Excel Add-in 1" 'adds a description to the menu item
            .OnAction = "module1.Test1" 'runs the specified macro
            .FaceId = 59 'assigns an icon to the dropdown
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Test Excel Add-in 2"
            .OnAction = "Module2.Test2"
            .FaceId = 64
        End With
    End With
    
    On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
    
On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
    On Error GoTo 0
End Sub

3. Install this Add-in to Microsoft Excel by follow this instruction
   3.1 Go to Excel Options > Add-Ins to open the Add-Ins dialog
   3.2 In this dialog, go to Manage Excel Add-ins > click Go...
   3.3 Browse to your Add-in from item 2 > OK
   3.4 Back to Add-Ins dialog > OK
   3.5 Excel Add-in is already installed to Microsoft Excel with the command bar to run macros in this Add-in