Problem The previous week I presented a The previous week I presented a tool that can loop through an open workbook (that contains unprotected VBA code) and create a list of all VBA procedures. This week I took this tool some steps further ending up with an application that contains probably the most demanding VBA code that I have written until now. So, what’s the buzz, you may wonder. Well, in short, this application does the following:

You select a folder.

You enter some passwords (that correspond to the VBA project passwords of the files contained in the selected folder).

The code loops through all the files in the folder (and sub-folders). Then it creates a list with all the Excel files that may contain VBA code (i.e. xlsm, xlsb, xltm, xls and xlt files). Finally, it opens every file, enters the VBA password, creates a list with all the VBA procedures and closes the file.

For security reasons VBA doesn’t provide any property that allows you to enter a password to unlock the VBA project. If you think that the above procedure sounds like a trivial task you should reconsider your thoughts! Solutions I found two workarounds to this problem: I found two workarounds to this problem:

“Sendkeys” is quite unreliable method and should be avoided . The reason is quite simple: if you are sending keystrokes to Excel and Excel’s window is not the active window that moment, the method fails. Moreover, you may accidentally cause malfunctions to the application in which the active window corresponds. I have included below two possible solutions using this method: 1. The “ Sendkeys ” method. This method sends keystrokes in an open window. I have underlined in a previous post thatThe reason is quite simple: if you are sending keystrokes to Excel and Excel’s window is not the active window that moment, the method fails. Moreover, you may accidentally cause malfunctions to the application in which the active window corresponds. I have included below two possible solutions using this method:

Option Explicit



Sub Sample()



'---------------------------------------------------------------------------------------

'Two methods to unlock a password protected VB project using the sendkeys method.

'Requires a reference to the Microsoft Visual Basic for Applications Extensibility 5.3

'library in order to work properly.



'Written by: Christos Samaras

'Date: 07/11/2012

'e-mail: xristos.samaras@gmail.com

'site: https://myengineeringworld.net/////

'---------------------------------------------------------------------------------------



UnprotectVBAPassword1 Workbooks("Book1.xlsm"), "pass1"

UnprotectVBAPassword2 Workbooks("Book2.xlsm"), "pass2"



End Sub



'1st method

Sub UnprotectVBAPassword1(WB As Workbook, ByVal Password As String)



'Check if VB project is already unlocked.

If WB.VBProject.Protection <> 1 Then

Exit Sub

End If



WB.Activate

SendKeys "%{F11}" 'Switch to VBA editor

SendKeys "^r" 'Set focus to Explorer

SendKeys "{TAB}" 'Tab to locked project

SendKeys "~" 'Press Enter

SendKeys Password 'Send the password

SendKeys "~" 'Press Enter



If WB.VBProject.Protection = vbext_pp_locked Then

MsgBox "Failed to unlock", vbExclamation

End If



End Sub



'2nd method

Sub UnprotectVBAPassword2(WB As Workbook, ByVal Password As String)



Dim vbProj As VBIDE.VBProject



WB.Activate

Set vbProj = WB.VBProject



'Check if VB project is already unlocked.

If vbProj.Protection <> 1 Then Exit Sub



Set Application.VBE.ActiveVBProject = vbProj



'"Send" the password.

SendKeys Password & "~~"

Application.VBE.CommandBars(1).FindControl(ID:=2578, recursive:=True).Execute

SendKeys "{ESC}"



End Sub

The “API road” . What is this? Well, in this application, it is a combination of various 2.. What is this? Well, in this application, it is a combination of various windows API functions in order to “find” a particular window (the VBA project properties window in this case) and manipulate it using the SendMessage function . It is obvious that since we are trying to manipulate the VB Editor, we should go one step further from the limits of the VBA language. So, we are forced to use API functions, some of which are the following: FindWindow, FindWindowEx, SetForegroundWindow, SendMessage, PostMessage and Sleep.

Demonstration video

Who will use this?

Well, anyone who writes VBA code in Excel and wants to organize his/her work. So, if you have a folder will all your workbooks and enter the passwords that correspond to your VBA projects you can create a useful index of all the VBA procedures that are contained in the files that of the selected folder. More precisely, the application creates a new sheet that contains the files of the folder and for each file it creates a new sheet with all the VBA procedures of the corresponding file. Bear in mind that if you have files that require a password during opening, you must unlock them first and then run this application, although I have included some error handling for this occasion.

IMPORTANT NOTE : This application is NOT a VBA cracking software. It goes without saying that you must know the passwords of the VBA projects before using it. Furthermore, if some of you try to enter an enormous amount of passwords (hoping that you will find the correct one) the most possible scenario is that the Excel might crash. In my case, I tested the application with a folder containing around 323 Excel files and 6 passwords. It worked smoothly and without crashing Excel. Furthermore, if some of you try to enter an enormous amount of passwords (hoping that you will find the correct one) the most possible scenario is that the Excel might crash. In my case, I tested the application with a folder containing around 323 Excel files and 6 passwords. It worked smoothly and without crashing Excel. Downloads The file can be opened with Excel 2007 or newer. Please enable macros before using it. Read also

The short video below demonstrates the application in use.