Pages in this article
-
Preparations
-
Book or add-in
-
Menus
-
Toolbars
-
Limit access
-
Protect code
-
Initialise
-
Save Settings
-
UI Languages
-
Setup
-
Conclusion
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