Content
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:

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:

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

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 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:

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:

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

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:

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:

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.