Problem
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.
If you think that the above procedure sounds like a trivial task you should reconsider your thoughts! For security reasons VBA doesn’t provide any property that allows you to enter a password to unlock the VBA project.
I found two workarounds to this problem:
Solutions
I found two workarounds to this problem:
1. The “Sendkeys” method. This method sends keystrokes in an open window. I have underlined in a previous post that “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:
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: [email protected]
'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
2. The “API road”. 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
The short video below demonstrates the application in use.
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.
Downloads
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Hi, Brian,
Thank you for the suggestions!
It’s been almost a decade since this spreadsheet was developed.
The last few years, whenever I have some free time, I am trying to update the older posts, as well as the code snippets/spreadsheets.
So, when I revisit this post, I will definitely consider your suggestions!
Best Regards,
Christos
This is an amazing tool, and a great bit of programming.
I have two suggestions.
1. The following line in routine UnlockVBAProject
sOutput(lRow, 8) = TotalCodeLinesInVBComponent(VBComp)
greatly slows down execution for large modules because it gets redone for every procedure found. Just do this once before the procedure search loop and save it in a variable, and it will run much faster.
2. Insert the following line near the same place as the line above, and it will give a more useful display of what the program is currently doing.
frmUnlockAll.lblUserMessage = VBProj.Filename & vbCrLf & “Module Name: ” & vbMod & vbCrLf & “Procedure: ” & sProcName
You might also consider adding .xla and .xlam to the file types that the program will process.