Visual Studio Tools for Office and Visual Basic for Applications interoperability

One of the cool new features in VSTO v3 is interoperability between VSTO and VBA code in your Office document. The basic steps are pretty simple:

  1. Create a Word document with some VBA code. It doesn’t really matter what it just as long as it has some code.
  2. Save the document as a macro enabled document so it has the DOCM extension.
  3. Create a new VSTO project.
  4. Instead of new blank document base it on the just created document.
  5. In the property sheet set the EnableVBaCaller property to true.
  6. Add a public function to the ThisDocument class.

Setting the EnableVBaCaller property actually adds some an extra property to the existing VBA code with the name CallVSTOAssembly. This CallVSTOAssembly property is basically a proxy to the ThisDocument object letting you call all public functions from your VBA code.

It works pretty well except for a few things.

Every time you compile and run the Word document opened is overwritten so any changes to the VBA code there are also overwritten. Now this is expected behavior but in this case a bit annoying as this is the only place you can edit the VBA code. In fact from within VS2008 there is no way to get to the VBA code at all. So this means opening the project version of the Word document outside if VS2008 using Word itself and copy and pasting all code changes made. And if you do this you need to make sure the document isn’t open in VS2008 or Word will complain loudly [:(].

Another problem I had was with the CallVSTOAssembly property. With all the cut and paste actions of VBA code I actually screwed up and added the code for a CallVSTOAssembly property from another solution. The result was no complaints or warnings but a CallVSTOAssembly property looking for the wrong class, actually the wrong namespace. And the result was that all interop came to a grinding halt. As the expected CallVSTOAssembly property was there it took me a while to realize that the namespace was wrong. So if you find that the CallVSTOAssembly property doesn’t seem to work and offers no IntelliSense when editing your VBA code just remove it and toggle the EnableVBaCaller to false and back to true to have VS2008 reinsert the correct code [:)].

See http://msdn2.microsoft.com/en-us/library/bb386306.aspx for more info in VSTO VBA interoperability.

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>