Wednesday, 31 October 2012

List Of Subs & Functions

The previous days I noticed that I have written a lot of VBA code until now. So, I decided to organize my workbooks in order to avoid rewriting the same VBA code. In order to achieve this I developed this small application which does the following: it creates a list of all opened workbooks; it populates a combo box and if you select a specific workbook that contains VBA code (but is not password protected) it creates a list with all the procedures that the workbook contains.

After workbook selection, a new sheet will be added and it will contain the following information (columns): No., Procedure Name, Procedure Type, Total Code Lines, Start Line In Module, Module Name, Module Type, Module Total Code Lines and Procedure Declaration

The application uses the Microsoft Visual Basic for Applications Extensibility 5.3 library in order to manipulate the Visual Basic Editor and its objects. However, even with this library it is not possible to manipulate password protected VBA projects (excluding of course “sendkeys” method). So, you can use it only with workbooks that you know their VBA password. I suppose that some of you who are writing VBA code will appreciate this application.

How to use it

The short video below demonstrates the application in use.

Download it from here

This file can be opened with Office 2007 or newer. It could be opened even with older Office versions that incorporate the compatibility pack from Microsoft. However, do not forget to enable macros before using it.

Read also

List All Your VBA Procedures

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