Code Snippets

List of Add-ins installed

This lists all the Excel add-ins that are installed and the folders they are saved in.

Public Sub Addins_List() 
Dim oVBAAddin As AddIn 
Dim oCOMAddin As COMAddIn 
Dim icount As Integer 
Dim istart As Integer 
   For icount = 1 To Application.Addins.Count 
      Set oVBAAddin = Application.AddIns(icount) 
      Range("A" & icount).Value = oVBAAddin.Name 
      Range("B" & icount).Value = oVBAAddin.FullName 
      Range("C" & icount).Value = oVBAAddin.Installed 
   Next icount 
   istart = icount 
   For icount = 1 To Application.COMAddIns.Count 
      Set oCOMAddIn = Application.COMAddIns(icount) 
      Range("A" & istart + icount).Value = oCOMAddIn.Description 
      Range("B" & istart + icount).Value = oCOMAddIn.progID 
      Range("C" & istart + icount).Value = oCOMAddIn.Connect 
   Next icount 
End Sub 

oVBAAddin.Name - read only returns the filename of the add-in 
oVBAAddin.FullName - read only returns the full path and filename of the add-in 
oVBAAddin.Installed - get/set whether the add-in is installed in the current session 
oVBAAddin.Path - read only returns the full folder path of the add-in 
oVBAAddin.CLSID - read only returns a unique identifier for the add-in 
oVBAAddin.Title - read only returns the string shown in the Add-in Manager (This is a hidden property) 

This lists all the Excel add-ins that are installed based on the registry.

Public Sub Addins_ListFromRegistry() 
Dim WshShell As Variant 
Dim svalue As String 
Dim skey As String 
Dim irowno As Integer 
Dim icount As Integer 
   Set WshShell = CreateObject("WScript.Shell") 
   irowno = 1 
   For icount = 0 To 20 
      If (icount = 0) Then 
         skey = "OPEN" 
      Else 
         skey = "OPEN" & icount 
      End If 
      svalue = "" 
      On Error Resume Next 
      svalue = WshShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\" & skey) 
      If (Len(svalue) > 0) Then 
         Range("B" & irowno).Value = svalue 
         Range("A" & irowno).Value = skey 
         irowno = irowno + 1 
      End If 
   Next icount 
End Sub 

Remove an Entry from Registry

Public Sub RemoveAnEntry() 
Dim WshShell As Variant 
Dim skey As String 
   skey = "OPEN3" 
   Set WshShell = CreateObject("WScript.Shell") 
   WshShell.RegDelete ("HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\" & skey) 
End Sub 

Application.LibraryPath

Returns the directory containing the built-in Excel add-ins.
Options > VBA Code > Folder Paths

Application.UserLibraryPath

Returns the directory to the location on the users computer where the COM add-ins are installed. Read Only string
Options > VBA Code > Folder Paths

Important

Do not use the "Auto_Open" event as it is only available for backwards compatibility.