[Excel VBA] How to Automatically Install an Excel Add-in (.xlam) Using a Macro

When distributing your own macros as an “Add-in (.xlam file),” asking users to manually install them can be difficult and requires detailed instructions.

If you have a macro that automatically handles everything from installation to activation, users can start using your tool immediately with just one click.

This article explains how to use VBA to automatically install (register) a specific Excel add-in, activate it, and even run a setup macro inside it.

VBA Sample Code for Automatic Installation

This macro performs the following steps:

1. Registers an add-in file (.xlam) located in the same folder as this Excel file.
2. Activates the registered add-in (Installed = True).
3. Runs a specific setup macro inside the add-in (e.g., SetupRibbon).

' Install and activate a specific add-in
Sub InstallMyToolAddin()

    '== Define constants and variables ==
    Const ADDIN_NAME As String = "MyToolBox.xlam" ' Target add-in file name
    Dim addinFilePath As String
    Dim targetAddin As AddIn
    
    ' Generate the full path of the add-in file
    addinFilePath = ThisWorkbook.Path & "\" & ADDIN_NAME
    
    '== Check if the add-in file exists ==
    If Dir(addinFilePath) = "" Then
        MsgBox "Add-in file not found." & vbCrLf & ADDIN_NAME, vbCritical
        Exit Sub
    End If

    '== Register the add-in to Excel and get it as an object ==
    ' Setting CopyFile to False keeps the file in its original location
    Set targetAddin = Application.AddIns.Add(Filename:=addinFilePath, CopyFile:=False)
    
    '== Activate the add-in (Check the box) ==
    targetAddin.Installed = True
    
    '== Run the initialization macro inside the add-in (Optional) ==
    ' Assumes a macro like SetupRibbon exists
    ' Application.Run "'" & ADDIN_NAME & "'!SetupRibbon"

    MsgBox ADDIN_NAME & " has been installed and activated.", vbInformation

End Sub

Explanation of the Code

1. Const ADDIN_NAME

Const ADDIN_NAME As String = "MyToolBox.xlam"

We declare the target add-in file name as a constant. If the file name changes, you only need to update this one line.

2. Check File Existence (Dir)

If Dir(addinFilePath) = "" Then ...

We use the Dir function to check if the add-in file actually exists at the specified path. Stopping the process if the file is missing prevents errors.

3. Register the Add-in (Application.AddIns.Add)

Set targetAddin = Application.AddIns.Add(Filename:=addinFilePath, CopyFile:=False)

This line registers the add-in with Excel (adds it to the installation list).

• Application.AddIns.Add: The method to add an add-in to the list.
• Filename:=addinFilePath: Specifies the full path of the add-in file.
• CopyFile:=False: When set to False, Excel refers to the file in its current location instead of copying it to the default library folder. This is the common setting for distributed tools.

4. Activate the Add-in (.Installed = True)

targetAddin.Installed = True

This command enables the add-in. Executing this is equivalent to checking the box in the Add-ins dialog. Setting it to False will disable the add-in.

5. Run an Internal Macro (Application.Run)

Application.Run "'" & ADDIN_NAME & "'!SetupRibbon"

This command runs a specific macro (in this example, SetupRibbon) written inside the add-in after activation. This is useful if you want to perform initial setup, such as adding a custom ribbon menu, during installation.

Note: The format for specifying the macro name is 'AddinFileName'!MacroName. It is safe to enclose the add-in name in single quotes in case it contains spaces.

Summary

In this article, we covered how to automate the installation and activation of Excel add-ins using VBA.

1. Use Application.AddIns.Add to add the file to the add-in list.
2. Use the .Installed = True property to activate it.
3. Use Application.Run to execute macros inside the add-in.

By including this macro in your distribution file as an installer, users can start using your tools with a single click without worrying about file locations or manual setup.