Wednesday, 7 November 2012

List All Your VBA Procedures


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:

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
    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

    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
    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.



The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Read also

Did you like this post? If yes, then share it with your friends. Thank you!


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info