Looping through excel files in a folder to find workbooks with locked VBA project modules

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I am trying to build an excel model which will loop through all the workbooks in a folder and tell me which of them has password protected VBE modules.

The reason for this requirement is that I'm trying to use another excel VBA model to find workbooks that are challenging our server database via VBA. My module string-search code searches modules, but not password protected modules. Therefore, I wish to find workbooks with password protected VBE modules and remove them to another folder.

I have VBA code that I can add to a module of any workbook, and list the modules that are password protected.
I have tested this code below and it works very well.

VBA Code:
Sub Run_Macros()
     Call EnableReferences
     Call Prj_ProtectedNew
End Sub


Sub EnableReferences()
'Adds a reference to MS Visual Basic for Applications Extensibility 5.3
Dim Ref As Object, lRow As Long
If Not CheckRefEnabled("VBIDE") Then _
    ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
If Not CheckRefEnabled("VBScript_RegExp_55") Then _
    ThisWorkbook.VBProject.References.AddFromGuid "{3F4DACA7-160D-11D2-A8E9-00104B365C9F}", 5, 5
End Sub

Function CheckRefEnabled(sRefName As String) As Boolean
Dim Ref As Object
For Each Ref In ThisWorkbook.VBProject.References
    If Ref.Name = sRefName Then
        CheckRefEnabled = True
        Exit Function
    End If
Next Ref
End Function

Sub Prj_ProtectedNew()
'Macro that lists modules with password protection

'set a reference to MS Visual Basic for Applications Extensibility 5.3
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 5, 3

Dim vbPrj As VBProject
Dim vbCom As VBComponent

Set vbPrj = Application.VBE.VBProject
Set vbCom = Application.VBE.VBProject.VBComponents

Dim x As Integer

x = 1

On Error Resume Next

For Each vbPrj In Application.VBE.VBProjects
    If vbPrj.Protection = vbext_pp_locked Then
        For Each vbCom In vbPrj.VBComponents
            Worksheets("Sheet1").Cells(x, 1) = vbPrj.Filename & ":" & vbPrj.Name & "." & vbCom.Name
            x = x + 1
        Next
    End If
Next

End Sub

My next objective was to build a procedure which will loop through workbooks and essentially run the above code for each workbook in a folder, and this is where I am having difficulties.

Here is my current code build for this procedure...

VBA Code:
Sub CheckWbooksModProtection()

'Note: Set a reference to MS Visual Basic for Applications Extensibility 5.3

Dim x           As Integer
Dim Value       As String
Dim MasterWb   As Workbook
Dim vbPrj       As VBIDE.VBProject
Dim vbCom       As VBIDE.VBComponent

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set MasterWb = Workbooks("MasterWb.xlsm")
MasterWb.Application.EnableEvents = True

previousSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable

On Error Resume Next
ThisWorkbook.VBProject.References _
    .AddFromGuid "{0002E157-0000-0000-C000-000000000046}", Major:=5, Minor:=0

myfolder = "C:\Users\DougM\Downloads\Test folder\"

MasterWb.Worksheets("Output").Range("C4") = myfolder
MasterWb.Worksheets("Output").Range("B7:D" & Rows.Count) = ""

x = 7
Value = Dir(myfolder)
Do Until Value = ""
    If Value = "." Or Value = ".." Then
    Else
        If Right(Value, 3) = "xls" Or Right(Value, 3) = "xlt" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Or Right(Value, 4) = "xltm" Or Right(Value, 4) = "xlsb" Or Right(Value, 4) = "xlam" Then
            On Error Resume Next
            Workbooks.Open Filename:=myfolder & Value, ReadOnly:=True, IgnoreReadOnlyRecommended:=True, Password:="zzzzzzzzzzzz"
            
            Set vbPrj = Application.VBE.VBProject
            Set vbCom = Application.VBE.VBProject.VBComponents
            
            On Error Resume Next

            For Each vbPrj In Application.VBE.VBProjects
                If vbPrj.Protection = vbext_pp_locked Then
                    For Each vbCom In vbPrj.VBComponents
                       MasterWb.Worksheets("Output").Cells(x, 2) = vbPrj.Filename & ":" & vbPrj.Name & "." & vbCom.Name
                        x = x + 1
                    Next
                End If
            Next

            Workbooks(Value).Close False
            On Error GoTo 0
        End If
    End If
    Value = Dir
Loop

Application.AutomationSecurity = previousSecurity
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

This code simply doesn't add the filename/project name/module name to the output worksheet like the first macro does successfully.
I don't get any errors in the code; however, using F8 to go step by step, the code isn't detecting if a workbook has locked modules, and isn't cycling through modules either.

Would anyone please help me understand what I've done wrong?

Kind regards,

Doug.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,481
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can only password protect a project not a module. If it's protected for viewing, you shouldn't be able to view it or iterate it in code so that would raise an error, but your code is suppressing all errors.

Also, I'm not sure what this line is supposed to do:

Code:
Set vbPrj = Application.VBE.VBProject

as it makes no sense to me. There is no VBE.VBProject property.
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
You can only password protect a project not a module. If it's protected for viewing, you shouldn't be able to view it or iterate it in code so that would raise an error, but your code is suppressing all errors.

Also, I'm not sure what this line is supposed to do:

Code:
Set vbPrj = Application.VBE.VBProject

as it makes no sense to me. There is no VBE.VBProject property.

Hi Rory,

Thanks for your reply.

I found the code in question here: how do i know if vba project is protected?
I'm not sure what that line does either, and couldn't find any mention of it here: Programming In The VBA Editor

However, it seems to work when I'm just using it in one workbook and not trying to loop through lots of workbooks.
For instance, when I run the code on workbook with a protected project, I get the below output:

locked modules.PNG


It is listing everything in that project.

I noticed the cpearson source link uses:
VBA Code:
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")

So I had presumed that the below was a generic reference to the VBProject/VB Components, or maybe old syntax.

VBA Code:
Set vbPrj = Application.VBE.VBProject
Set vbCom = Application.VBE.VBProject.VBComponents

I'm wondering if the reason I can't use looping through workbooks is because when the workbooks.open event triggers, the resulting workbook doesn't have a reference to MS Visual Basic for Applications Extensibility 5.3 set. Not sure how I would add that reference via code to a workbook I've just opened! Would that even be possible?
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,481
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If a project is locked for viewing, your code should not be able to list its components. That would kind of defeat the purpose of locking it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,801
Messages
5,626,965
Members
416,212
Latest member
Ifemiide

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top