VBA code to determine if a specific excel-based tool is used as add-in and locked?

[Ram Malladi]

Hello to the group members!

Can someone help me with VBA code for determining if an excel file has 'specific excel-based add-ins' (i have a list of specific add-ins) loaded and password protected? Any help is appreciated.

Note: I have the below code to determine if macros stored under VBAProject are locked or not. But this code is not able to determine if there are add-ins that are locked (as these add-ins are not stored under VBAProject but just outside of VBAProject).

--------------

Sub wsht_macro_protect(Ana_Name, sht_Count, pro_Prot, sht_Prot_cnt, sht_Prot)
  Dim sh As Worksheet
  Dim wb As Workbook
  
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.AskToUpdateLinks = False


  Set wb = Workbooks(Ana_Name) 'Input workbook name here or use do ...loop
  'Initialization
  sht_Count = 0
  sht_Prot_cnt = 0
  sht_Prot = ""
  pro_Prot = ""
  
  wb.Activate
  sht_Count = Sheets.Count
  
  If wb.VBProject.Protection = vbext_pp_locked = True Then
    pro_Prot = "Protected"


  End If
  
  For Each sh In wb.Worksheets
      If sh.ProtectContents = True Then
        sht_Prot_cnt = sht_Prot_cnt + 1
        sht_Prot = "Protected"


    End If
    
  Next sh
  
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.AskToUpdateLinks = True
  
End Sub

------------------

Any help is appreciated.

Thank you
Ram.

[snb]

And when you have got this inventory, what's next ?

[p45cal]

You may be able to compare some of the properties with your list, eg.:

Sub blah()
Set yyy = Application.AddIns
For i = 1 To yyy.Count
  Debug.Print yyy(i).Title, yyy(i).Name, yyy(i).Installed
Next i
End Sub

You'd probably have to open the file first though.

[Ram Malladi]

Thank you, p45cal.

yyy = application.AddIns is pulling only *.XLAM type of add-ins only.

Is there any command to capture *.xla type of add-ins also?

Thank you, Ram.

[Ram Malladi]

snb, thank you for the message. Just need to list the add-ins used in files. Thank you.

[p45cal]

try changing:
Set yyy = Application.AddIns
to:
Set yyy = Application.AddIns2
(Excel 2010 and later)