VSTO & .NET & Excel

October 22, 2010

Improve Your VBA code with TM VBA Inspector

Filed under: Excel, Tools — Dennis M Wallentin @ 1:28 am

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!

Kind regards,
Dennis

Advertisements

8 Comments »

  1. Looks interesting Dennis, thanks for the review and hats of to Thomas for his work.

    Ross

    Comment by ross — October 22, 2010 @ 11:41 am

    • Last night I recommended it to a local friend who is Office educator for a local company. We agreed that it’s a nice learning tool.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 22, 2010 @ 11:55 am

    • Ross,

      You’re welcome.

      I had much fun buildung the add-in. If you get one or another hint it was worth the trouble I had.

      Code well and have fun!

      Thomas

      Comment by Thomas Möller — October 30, 2010 @ 5:35 pm

  2. This looks pretty cool, I will definitely give it a spin! There are a few code analysis tools available in .NET, which are great, and I always thought it would be interesting to write one for VBA, but never got to doing it, because it’s no small task. Congrats to Thomas, and I am very interested in trying it out.

    Comment by Mathias — October 28, 2010 @ 7:23 am

    • You’re welcome 🙂

      Agreed, it’s not a small task and I also think that the lack of culture for having such tools in the Office group also is part of the explanation.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 28, 2010 @ 1:20 pm

    • Thank you for the flowers. 😉

      The fact that there are no inexpensive tools for analysing VBA-Code was the motivation for me to write such a tool.
      I had much fun and learned many things – and the journey is not over yet. 🙂

      Code well and have fun!

      Thomas

      Comment by Thomas Möller — October 30, 2010 @ 5:42 pm

  3. @ Dennis

    Thank you very much for your kind words and for promoting the TM VBA-Inspector.

    Building add-ins is fun for me so building this add-in was a great fun. 😉

    @ everyone

    You can do me a favor: If you have any issues with the TM VBA-Inspector don’t hesitate to send me an email with a screenshot of the error message. In the error message you should see the name of the module which caused the problem. I would love to get a copy of this module (if possible) so I can reproduce the error and fix it.

    TIA

    Thomas

    Comment by Thomas Möller — October 30, 2010 @ 5:28 pm

    • Thomas,

      You’re welcome and because there exist a small group of useful tools including Yours I just thought it was the best I could do 🙂

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — October 30, 2010 @ 6:30 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: