Easy Way to Install Addins for Excel

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:

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.

averysagand.blogspot.com

Source: https://jkp-ads.com/Articles/excel-addin-installer.asp

Share this post