Excel Add-Ins Install & Un-Install

The following code is the code we use for controlling the Install and Un-Install of our Excel Add-Ins. See our White Paper concerning ‘Customising Excel’. This may look similar to other code that has been posted elsewhere but when you think about it there are a limited number of ways you can perform certain operations. As far as we are concerned this code was original when we wrote it.

Option Explicit

' *********************************************************

' The Workbook code below requires a reference to

' Microsoft Visual Basic for Applications Extensibility 5.3

' *********************************************************

Private Sub Workbook_AddinInstall()

Dim vbCodeMod As CodeModule ' Code Module Object

Dim strModName As String ' Code Module Name

Dim arrProcs() As String ' Array of Procedure Names

Dim intNumProcs As Integer ' Number of Procedures

Dim cbpToolsMenu As CommandBarPopup ' Command Bar Object

Dim cbbNewMenuItem As CommandBarButton ' Command bar menu item

Dim intLC As Integer ' Loop Counter

Dim strActionName As String ' On Action name

Dim strCaption As String ' Menu Name / Caption

' Set AddIn Module name and get Procedure Names and numbers

strModName = "mdlAddIn"

arrProcs = ProcNames(strModName)

intNumProcs = UBound(arrProcs, 1)

' Delete menu items for each Procedure if they exist

On Error Resume Next

For intLC = 1 To intNumProcs

Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls(arrProcs(intLC)).Delete

Next intLC

On Error GoTo 0

' Find the Tools Menu and Exit if not found

Set cbpToolsMenu = Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30007)

If cbpToolsMenu Is Nothing Then

MsgBox "Cannot add menu item"

Exit Sub

' Add an item to the tools menu for each Proc found in AddIn module

' First item forces start of new group

Else

For intLC = 1 To intNumProcs

strActionName = strModName & "." & arrProcs(intLC)

strCaption = Replace(arrProcs(intLC), "_", " ", , , vbTextCompare)

Set cbbNewMenuItem = cbpToolsMenu.Controls.Add(Type:=msoControlButton)

With cbbNewMenuItem

.Caption = strCaption

.OnAction = strActionName

If intLC = 1 Then

.BeginGroup = True

End If

End With

Next intLC

End If

End Sub

Private Sub Workbook_AddinUninstall()

Dim vbCodeMod As CodeModule ' Code Module Object

Dim strModName As String ' Code Module Name

Dim arrProcs() As String ' Array of Procedure Names

Dim intNumProcs As Integer ' Number of Procedures

Dim intLC As Integer ' Loop Counter

Dim strCaption As String

' Set AddIn Module name and get Procedure Names and numbers

strModName = "mdlAddIn"

arrProcs = ProcNames(strModName)

intNumProcs = UBound(arrProcs, 1)

' Delete menu items for each Procedure

On Error Resume Next

For intLC = 1 To intNumProcs

strCaption = Replace(arrProcs(intLC), "_", " ", , , vbTextCompare)

Application.CommandBars("Worksheet Menu Bar").Controls("Tools").Controls(strCaption).Delete

Next intLC

On Error GoTo 0

End Sub