Recently Thomas Möller (TM) made a blog entry about his free utility, TM VBA Inspector, at Microsoft Access Team’s blog. Since it looked interesting I downloaded it and tested it with Access 2010. I than made a comment at the blog about porting it to other tools in the Office suite, especially to Excel, as I find the add-in to be useful.
Thomas agreed on it and I helped him out by testing the add-in with Excel. Now it works with most of the tools in the Office suite and it can be downloaded from here: TM VBA Inspector
Because I find it to be useful I decided to write this article to spread the word about it. Once the tool is installed we can switch to the VBA IDE and select the command Add-ins from the main menu where we find a new entry as the below screen shot also shows:
To run the tool is quite easy; open the workbook in which You have code module to be evaluated and open the code module in the VBA IDE, select the command Add-ins > TM VBA-Inspector from the menu. When its main windows open You just select the command Refresh and then the code evaluation process starts. When done it shows something similar like the following screen shot:
By double-clicking an entry in the result list You switch directly to the appropriate place in the VBA code. There You can start update the identified error(s) or to improve the present code.
Before we start the code evaluation process we can do some changes in its settings by selecting the command Options from the tool’s main windows and then from the dialog we can uncheck/check a great number of various settings which also the screen shot below shows:
Make You selections and next click on the OK button to get back to its main windows. If wanted we can export the output of an evaluation process to a text file by selecting the Export command from its main windows. All in all, the add-in does its job very well and the outcome can also be quite learning.
Edit note (10/28/2010): In the latest version a new function has been implemented and that is to exclude modules from being reviewed by the tool if wanted.
Of course, I have a wish list that at present is a short list but I expect it to grow the more I use it:
- The tool use the Tool Window Form Border Style which act as the built-in forms do in the VBA IDE. Instead I would see that one of the Fixed Border Styles would have been used.
- After executing the evaluation process we get a nice list of recommendations in the main windows. It would have been great if we had access to a function that allowed us to easily replace the evaluated code with the recommendations. But I can understand if this will not be implemented as it requires a lot of time to implement it correctly.
Anyway, I suggest that You download the tool and test it out Yourself!