List All Your VBA Procedures

Share this

November 7, 2012

List All Your VBA Procedures

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.


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


Download

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


Read also


Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

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

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >