Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Create add-ins > Setup
Deze pagina in het Nederlands

Creating an addin from an Excel macro.

Create A Setup Utility

Manually installing an add-in is relatively simple:

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 & ".xla"
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:

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