In my article “The most important changes to VBA in Excel 2010“ from 08/05/2009, I had already reported about some changes to the VBA object model in Excel 2010. Also, I had started the development of an application for parsing and analysing all VBA objects, methods, properties and constants of the VBA libraries included in the Office apps. Just recently, I got the time to finish the development of this application.
The application is freeware and can be downloaded at the end of this article. The output tables are protected, however the password can be found in code which is unprotected and can be viewed by everyone. Please note, that the workbook itself and the copyright sheet are also protected by a password and this password is not publicly accessible.
The application referencies to the TLI Library „TlbInf32.dll“ in the windows system folder providing functions for scanning type libraires. Important note: the TLI Library needs to be installed and correctly registered on your system. My application can not work without this library. And please note that this library is not included in the download archive.
Ok, I know that is certainly possible to implement an application for scanning and parsing type libraries in a more modern way; but for my purpose – just scan the VBA libraries and compare the results between the different version of the MS apps – this approach was quite sufficient.
The „Excel VBA Typelib Scanner“ is available in german, english and french and allows you to scan the VBA librairies for Excel, Access, PowerPoint and Outlook for the version 2010, 2007 and 2003. I will show hereinafter how to use the application for scanning and comparing the VBA objects in Microsoft Excel.
When opening the application for the first time with Excel VBA Macros enabled, you should see the copyright sheet. If you like, you can change the display language on the upper left of the sheet. Please use the buttons on the upper right side of the sheet for navigating through the workbook.
On the next page, you should find an overview for the comparison between the different Excel versions; however this sheet is empty as we have not scanned the VBA librairies yet. So, please click on the button “Next” to reach the sheet for scanning the Excel 2010 VBA Library. The next pages are intended for getting the results for the scans of Excel 2007 and Excel 2003.
Before a scan of the Excel 2010 VBA Objects can be done, you may select the application by clicking on the upper right button. After opening the file ope dialog, please select your installation folder for Microsoft Office 2010 and select “Excel.exe” from the subfolder “Office14”. The following screenshot shows you the file open dialog on my german system.
My application then checks the selected file if it can be scanned and then stores the path to the file in a cell on the top of the sheet. Then, you can launch the parsing dialog by clicking on on the button “Parse”.
A click on the blue arrow starts the scan. Optionally, you may change the options “Ignore interface methods” and “Parse method arguments” before starting the scan. While the scann is performed you should see the scanned objects on the right side and the scanned object elements on the left side of status line. When finished, you should see something similar to the following screenshot.
On the left side are displayed informations about the type and the attributes for the scanned item. I used following abreviations:
O = Object
P = Property
M = Method or Event
C = Constant
I = Interface method
H = Hidden
R = Read only
O = Instanciation using Set
In case the option „Parse method arguments“ was set, the output sheet also shows the full prototype of scanned methods and events. For comparing the different Excel versions, you may also scan each version. For doing so, please navigate to the corresponding sheet, select the executable file and proceed in the same manner as described here for Excel 2010. I’m assuming, of course, that also the corresponding versions are installed. Then, you can navigate to the sheet for comparing the different versions. Here, you should click on “Update” for calling the appropriate dialog.
The dialog provides an option for seleting the version which should be compared to the other ones and some options for filtering objects, methods, properties and constants. Like in the previous dialog for scanning, click on the blue arrow for starting. The next screenshot shows the result for a comparison between Excel 2010, Excel 2007 and Excel 2003.
The software also implements some additional feetures for exporting the results to a new sheet or for selection the language and scan delays in the options dialog.
Some few words about the code included in the software: you should find 5 modules and 3 UserForms in the VBA Project. The module MLP_Api contains some Windows API imports, for example for calling the file open dialog. The module MLP_Sheet contains functions called by buttons on the different sheets. The module MLP_Parser contains all functions used to perform the scan and the module MLP_Compare contains all functions related to the copmparison. The following screenshots shows the VBA Editor of my german Excel 2003 version.
Finally, please note that this application is an experiment in VBA and therefore does not claim to be free of errors or to be usable under any Windows operating systems. I have developed this application with Excel 2003 and on a Windows Server 2008, German. In case you will find an error or if you like to suggest some features for a next version, please write me at vba (at) maninweb (dot) de.
Over the next days, I will put the results of my scans for the VBA Libraries for the different Microsoft Office applications on my server. This may be interesting for the people who have not the TLI Library installed or don’t like to install it. Finally, the link for downloading the application:
Some interesting links:
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German