an Excel Add-in that installs itself

Content

Introduction
Manually installing an add-in in Excel
How does Excel manage the list of add-ins
How To Install An Excel Add-in using VBA code
Triggering the install question
Download

Introduction

As you probably know, I offer a number of tools for your daily Excel work:

RefTreeAnalyser The best tool to dissect your formulas and find out where the data for a formula comes from
FlexFind a tool with which you can do search and replace in all of Excel's objects, not just cells
Name Manager The best tool to work with range names
Other free tools

There is a problem however. Many users struggle with getting the add-in installed. To make that easier I created a separate Excel file (quite a number of years ago) to install the add-in.

But you can make it even easier by incorporating the setup code in your add-in. In this article I show some VBA code that will ease that process: As soon as the user opens the add-in and enables macros, the add-in offers to install itself.

Manually installing an add-in in Excel

In modern Excel, even the first step to manually install an add-in is a difficult one: How do I get Excel to show the add-ins dialog? There are quite some steps involved:

Click File
Click Options
Click the Add-ins tab
Select Excel Add-ins from the drop-down
Click the Go... button.

Here are these steps in an animated gif:

Opening the add-ins dialog takes many steps

And even then, if your add-in file is not in the right location it is not listed in that dialog. The user has to click the browse button and look for it. Even if they find the add-in, Excel then pops up a difficult question if the add-in happens to be in their add-ins list already:

Browsing to an add-in is difficult

How does Excel manage the list of add-ins

Under the hood, Excel uses the registry and a special folder to manage which Add-ins are displayed in the Add-ins dialog and which are installed.

To build the list in the dialog, Excel looks in a couple of places:

1. The Add-ins folder:

C:\Users\[YourUserName]\AppData\Roaming\Microsoft\AddIns

You can get to this folder quickly by typing this into the address box: %appdata%\Microsoft\addins

or altenatively (the exact path depends on your Office version and whether or not you are using 64 bit Office):

C:\Program Files (x86)\Microsoft Office\Root\Office16\Library\

C:\Program Files\Microsoft Office\Root\Office16\Library\

Any Add-ins in these folders are automatically included in the Add-ins dialog.

2. The registry

For Add-ins in a different location from the ones shown above, Excel will look in the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\XX.0\Excel\Add-in Manager

For Excel 365 this is:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Add-in Manager

Keys are added there when you click the browse button to locate an Add-in. There is a value there for each Add-in which was browsed for. The value needed is simply the path to the Add-in and its name, e.g. :

C:\Users\piete\Documents\RefTreeAnalyser\RefTreeAnalyserXL.xlam

Registry with Excel's list of add-ins

Which Add-ins are Selected

In another location in the registry, Excel notes what Add-ins are selected (checked in the add-ins dialog). It does so using a number of values in this part of the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options

For each selected Add-in, Excel adds an entry in this location, successively called "OPEN", "OPEN1", "OPEN2",...

Registry showing Excel's installed Add-ins
Registry showing entries for Add-ins that are selected.

These keys each contain the name of the add-in to be opened (and sometimes some command-line parameters). If an add-in is not in the add-ins folder, the full path is included.

Note that these registry keys are updated AFTER closing Excel.

How To Install An Excel Add-in using VBA code

I've written some simple code to enable an add-in to pop up a question like this:

Add-in that asks to install itself

The following VBA code triggers this message box:

Option Explicit
Option Private Module
Const GCSAPPREGKEY As String = "DemoAddInInstallingItself"
Const GCSAPPNAME As String = "DemoAddInInstallingItself"

Public Function IsInstalled() As Boolean
    Dim oAddIn As AddIn
    On Error Resume Next
    If ThisWorkbook.IsAddin Then
        For Each oAddIn In Application.AddIns
            If LCase(oAddIn.FullName) <> LCase(ThisWorkbook.FullName) Then
            Else
                If oAddIn.Installed Then
                    IsInstalled = True
                    Exit Function
                End If
            End If
        Next
    Else
        IsInstalled = True
    End If
End Function

Public Sub CheckInstall()
    Dim oAddIn As AddIn
    If GetSetting(GCSAPPREGKEY, "Settings", "PromptToInstall", "") = "" Then
        If Not IsInstalled Then
            If ThisWorkbook.Path Like Environ("TEMP") & "*" Or InStr(LCase(ThisWorkbook.Path), ".zip") > 0 Then
                MsgBox "It appears you have opened the add-in from a compressed folder" & vbNewLine & _
                       "(zip file) or from a temporary folder." & vbNewLine & vbNewLine & _
                       "You are advised to save the add-in file to a dedicated folder" & vbNewLine & _
                       "in your Documents folder and then open the add-in from that location." & vbNewLine & vbNewLine & _
                       "The add-in will now close.", vbExclamation + vbOKOnly, GCSAPPNAME
                ThisWorkbook.Close False
            End If
            If MsgBox("Do you wish to install '" & GCSAPPNAME & "' as an addin?", vbQuestion + vbYesNo, GCSAPPNAME) = vbYes Then
                If ActiveWorkbook Is Nothing Then AddEmptyBook
                Set oAddIn = Application.AddIns.Add(ThisWorkbook.FullName, False)
                oAddIn.Installed = True
                RemoveEmptyBooks
            ElseIf MsgBox("Do you want me to stop asking this question?", vbQuestion + vbYesNo, GCSAPPNAME) = vbYes Then
                SaveSetting GCSAPPREGKEY, "Settings", "PromptToInstall", "No"
            End If
        End If
    End If
End Sub

The key function here is the one called "CheckInstall".

The first thing that routine does is find out whether there is a registry key called "PromptToInstall" in the Settings section of the registry. If there is, it will not prompt to install. This is done to avoid annoying those people who are in the habit of only opening your add-in when they need it (like me!).

Next it calls the IsInstalled function. This function checks whether or not the add-in is already installed.

Next there are two checks concerning the location where the add-in file is stored. Most of my add-ins are available through my website and are zip downloads. If the user opens such a zip download directly and then opens the add-in, the xlam file will be stored in a temporary location (if they have unzipping software installed) or it will be in a folder that contains .zip in its name. Excel can open such files, but it cannot install add-ins which are in a zip folder. And WinZip deletes that folder in Temp as soon as it is closed. You then end up with an pointer in Excel to an installed add-in which does not have an accompanying xlam file. This has the following annoying side effect every time Excel starts:

Excel cannot find an add-in file

So that is why the code then shows a message box like this one:

Warning that the add-in is stored in the wrong place

If all is well and the user first unpacked the zip file, then the code asks whether or not the user wants to install the add-in:

Add-in that asks to install itself

If Yes is clicked, it runs the mere two lines of code needed to install the addin:

                If ActiveWorkbook Is Nothing Then AddEmptyBook
                Set oAddIn = Application.AddIns.Add(ThisWorkbook.FullName, False)
                oAddIn.Installed = True
                RemoveEmptyBooks

Well, two lines? As you can see there are four lines. The first one ensures at least one workbook window is open in Excel. The last one closes any workbooks that were opened by the add-in. Why? Because you cannot open the add-ins dialog when there is no active workbook and apparently this also prevents Excel from adding a new add-in to the list through VBA.

If you click No, another dialog pops up asking the user if he (she/them) wants to keep being asked about installing the add-in:

Add-in asks to stop asking to install itself

If you click Yes, the code stores that decision so it will not bother the user again.

For completeness' sake, here's the code to add an empty workbook and delete it again:

Option Explicit
Option Private Module

Dim moWB As Workbook

Sub AddEmptyBook()
'Adds an empty workbook if needed.
    If ActiveWorkbook Is Nothing Then
        Workbooks.Add
        Set moWB = ActiveWorkbook
        moWB.CustomDocumentProperties.Add "MyEmptyWorkbook", False, msoPropertyTypeString, "This is a temporary workbook added by " & GCSAPPNAME
        moWB.Saved = True
    End If
End Sub

Sub RemoveEmptyBooks()
    Dim oWb As Workbook
    For Each oWb In Workbooks
        If IsIn(oWb.CustomDocumentProperties, "MyEmptyWorkbook") Then
            oWb.Close False
        End If
    Next
End Sub

Function IsIn(col As Variant, name As String) As Boolean
    Dim obj As Object
    On Error Resume Next
    Set obj = col(name)
    IsIn = (Err.Number = 0)
End Function

Triggering the install question

The last bit to get this all to work is making sure the CheckInstall routine is called when your add-in is opened. I've placed this in the ThisWorkbook module:

Private Sub Workbook_Open()
CheckInstall
End Sub

Some users of Excel experience problems if you call routines directly from the Workbook_Open event. In such cases, use Application.Ontime to launch the routine needed (see below). Unsing OnTime gives Excel time to perform all of its startup chores before launching your installation routine:

Private Sub Workbook_Open()
Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!CheckInstall"
End Sub

Download

I've made available a sample file containing all relevant code.

Comments

All comments about this page:

Comment by: RAVI (2-11-2023 15:00:00) deeplink to this comment

HOW TO LINK SOLIDWORKS 3D DIMENSION DATA TO EXCEL

Comment by: Jan Karel Pieterse (2-11-2023 16:38:00) deeplink to this comment

Hi Ravi,

I don't know how to import Solidworks data into Excel!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.