VBA Code


Installing and Uninstalling

The Add-ins collection contains all the add-ins that are available (and not just those that are installed).
You can refer to them explicitly using their name or by using a numerical index.

Addins("Analysis ToolPak").Installed = True | False 
Addins("Analysis ToolPak - VBA").Installed = True | False
Addins(1).Installed = True | False

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.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext