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.
This workbook, as well as this one, contain VBA code that uses the Microsoft Visual Basic for Applications Extensibility 5.3 library to manipulate the VBA Editor and its objects.
Such a code can be dangerous if used by amateur users.
Therefore, I decided to lock both workbooks.
In this way, someone can use the workbooks to get the necessary information without messing up with the code.
I can guarantee to you that both workbooks do not do anything suspicious
However, the final decision is up to you.
I usually preview the VBA code of Files I download from Internet.
My company requires that for security reasons.
Unfortunately you protected your code so I cannot use it.
Guess I have to google the snippets and make my own solution.
But thanks for trying.