VBA and VSTO Can Be Friends (Part II)

VBA code can easily be called from VSTO.  Here are the basic steps.

  1. Create a document with VBA code in it.
  2. Declare a public function or subroutine in the VBA code behind the document.
  3. Figure out a trusted location to create a VSTO project from
  4. Create a new VSTO project using the document with the VBA code in it--be sure to create the project in a trusted location.
  5. From VSTO code, write the code "Me.Application.Run("MethodName", param1, param2, ...)

So let's walk through this in more detail.

First, launch Microsoft Word 2007 and create a new blank document.  Type some random text into the document.  Then record a vba macro into the word document--something like selecting a word in the document and bolding it.  If you haven't recorded macros in Word 2007 yet, you may be wondering where you start the macro recorder from.  Go to the Office menu (the big round button in the top left corner of the Word window), drop down the menu and click Word Options.  Then from the Popular tab of the Words Options dialog, click the Show Developer tab in Ribbon button.  This shows the developer tab of the ribbon.  On the developer tab of the Ribbon is a Record Macro button.  Click the Record Macro button.  Name the macro "MyMacro" and make sure it records into the current document by dropping down the Store Macros In combo box and picking the active document rather than normal.dot.  Click OK to start recording the macro.

image

Now do something interesting in the document then click the Stop Recording button in the developer tab of a ribbon.

Now that you have a macro recorded called MyMacro in the current blank document, you need to save the document into a trusted location.  What are trusted locations you might ask?  These are paths that Office trusts and allows macros to run from.  You must save your document to a trusted location if you want to run the macro the next time the document runs.  You must also store a document that has VSTO code in it that talks to VBA in a trusted location or the VBA code won't be allowed to run.  (You don't need to do this in general for documents with VSTO code behind them, only documents that have both VSTO and VBA code in them as our example will have when we are done).

To figure out what the trusted locations are, bring up the Word Options dialog again from the Office menu.  Click on the Trust Center tab on the left then click on the Trust Center Settings button.  In the Trust Center click on Trusted Locations tab as shown below.

image

Let's add a trusted location where we will create our hybrid VSTO and VBA project.  Click the Add New Location button then create a new trusted location--we'll create a folder called
"c:\vstotemp" and make sure you check the checkbox so that subfolders will also be trusted.  Under this directory, we'll create our VSTO project.

image

Now that you have a trusted location (make sure that you created a folder called vstotemp or whatever you choose to call it) save your document with macros in it to the root of that directory.  Another new Office 2007 thing to remember is that you can't just save the file in the default "docx" format if it has macros in it.  You need to choose Save As from the Office menu and pick "Other file formats".  Then, in the Save as document, drop down the Save as type to pick Word Macro-Enabled Document as shown below.  We'll call the document vstoandvba.

image

Now that you've saved the document, close it, then to prove to yourself you did everything right, reopen the document, go to the developers tab and click the Macros button, then rerun MyMacro to make sure it still runs.

So where are we?

  • We created a word document, turned on the developer tab, and recorded a macro into the word document called MyMacro
  • We worked with the trust center to make c:\vstotemp a trusted location including subfolders.
  • We saved our word document to c:\vstotemp\vstoandvba.docm by picking save as and saving in the macro-enabled file format (docm extension)

Next time, I'll show you how to create a VSTO project in the trusted location using the vstoandvba.docm file, and show you that VSTO code can call VBA code associated with the same document.