Creating an addin from an Excel macro

Pages in this article

Create A Setup Utility

Manually installing an add-in is relatively simple:

From the Menu, select "Tools", "Add-ins..."
Click "Browse" and navigate to the folder containing the add-in file.
Select the add-in and click OK twice.

To ease this process for the user, setting up an add-in can also be done using code. Listing 9 shows how this can be done.

Listing 9

Option Explicit
Dim vReply As Variant
Dim AddInLibPath As String
Dim CurAddInPath As String
Const sAppName As String = "Name Manager"
Const sFilename As String = sAppName & ".xlam"
Const sRegKey As String = "FXLNameMgr"    ''' RegKey for settings

Sub Setup()
    vReply = MsgBox("This will install " & sAppName & vbNewLine & _
    "in your default Add-in directory." & vbNewLine & vbNewLine & _
    "Proceed?", vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        On Error Resume Next
        Workbooks(sFilename).Close False
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "\" & sFilename
            AddInLibPath = Application.LibraryPath & "\" & sFilename
            'User librarypath does not have a trailing path separator
            'AddInLibPath = Application.UserLibraryPath & sFilename
        Else
            'MAC syntax differs from Win
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename
        End If
        On Error Resume Next
        FileCopy CurAddInPath, AddInLibPath
        If Err.Number <> 0 Then
            SomeThingWrong
            Exit Sub
        End If
        With AddIns.Add(Filename:=AddInLibPath)
        .Installed = True
        End With
    Else
    vReply = MsgBox(prompt:="Install Cancelled", Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub

Sub SomeThingWrong()
    If Application.OperatingSystem Like "*Win*" Then
        vReply = MsgBox(prompt:="Something went wrong during copying" _
        & vbNewLine & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath & "\" _
        & vbNewLine & vbNewLine & "You can install " & sAppName _
        & " manually by copying the file" & vbNewLine & _
        sFilename & " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet, first do" _
        & " the copying from Windows Explorer." & vbNewLine _
        & "It gives you the opportunity to ALT-TAB back to Excel" _
        & vbNewLine & "to read this text." _
        , Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    Else
        vReply = MsgBox(prompt:="Something went wrong during copying" _
        & vbNewLine & "of the add-in to your add-in directory:" _
        & vbNewLine & vbNewLine & Application.LibraryPath _
        & vbNewLine & vbNewLine & "You can install " & sAppName & _
        " manually by copying the file" & vbNewLine & sFilename & _
        " to this directory yourself and installing the addin" _
        & vbNewLine & "using Tools, Addins from the menu of Excel." _
        & vbNewLine & vbNewLine & "Don't press OK yet," _
        & " first do the copying in the Finder." _
        & vbNewLine & "It gives you the opportunity to Command-TAB back to Excel" _
        & vbNewLine & "to read this text." _
        , Buttons:=vbOKOnly, Title:=sAppName & " Setup")
    End If
End Sub

Sub Uninstall()
    vReply = MsgBox("This will remove the " & sAppName & vbNewLine & _
    "from your system." & vbNewLine & _
    vbNewLine & "Proceed?", vbYesNo, sAppName & " Setup")
    If vReply = vbYes Then
        If Application.OperatingSystem Like "*Win*" Then
            CurAddInPath = ThisWorkbook.Path & "\" & sFilename
            AddInLibPath = Application.LibraryPath & "\" & sFilename
        Else
            'MAC syntax differs from Win
            CurAddInPath = ThisWorkbook.Path & ":" & sFilename
            AddInLibPath = Application.LibraryPath & sFilename
        End If
        On Error Resume Next
        Workbooks(sFilename).Close False
        Kill AddInLibPath
        DeleteSetting sRegKey
        MsgBox " The " & sAppName & " has been removed from your computer." _
        & vbNewLine & "To complete the removal, please select the " & sAppName _
        & vbNewLine & "in the following dialog and acknowledge the removal" _
        , vbInformation + vbOKOnly
        Application.CommandBars(1).FindControl(ID:=943, recursive:=True).Execute
    End If
End Sub

The setup code does the following:

Close the add-in (it might already be installed and maybe the user is installing a new version)
Set the path where the add-in file is currently located (same path as the path where the setup file resides)
Determine where Excel will keep its own add-ins
Copy the add-in file from the first path to the second
Check if any errors have occurred
Include the add-in file in the list if add-ins
Install the add-in.
The code shown above can easily be adapted to use for setting up your own addins, simply change the value of three constants: (sAppName, sFilename and sRegKey).

Also, the subroutine called "UnInstall" may be used to remove the add-in.

You can download a free utility that has put the above code to use.

Updating your addin automatically

Also, this article shows a way to enable automatic updating of your addin through your website.

Other setup programs

This page describes how add-ins are managed by Excel and also shows how to implement "Setup Factory" to create setup files for your add-ins:

Installing An Excel Add-in Using Setup Factory