VSTO to VBA and Back Again

In this post I´m going to quickly describe a method to call a VBA 6 macro in a Word document from your VSTO add-in and the opposite, calling a method in your VSTO from a Word macro.

The method for the latter was supplied in this post for which I can´t take any credit but I daresay it deserves a second publication since it took me a while to find it, so here it is:

Invoke a macro from VSTO

Calling a named macro from the VSTO is no biggie, simply use the Run-method in the Application object:

application.Run(macroName);

Unfortunately the errors you get for missing macros and such are quite depressing but still, it’s possible.

Invoke a method from VBA/Macro

This is a bit harder but still fully possible. What we do is that we create a class publicly visible and implementing a COM-interface. This class is then returned by an overloaded method in our VSTO-base class.

Step 1 – Creating our class

using System.Runtime.InteropServices;
using stdole;

/// <summary>Interface for the COM-object, this is what the VBA actually will see/work against</summary>
[ComVisible (true)]
[InterfaceType (ComInterfaceType.InterfaceIsIDispatch)]
public interface IMacroMessages : IDispatch {
    void SendMessage (string message);
}

/// <summary>Implementation of the COM-interface</summary>
[ComVisible (true)]
[ClassInterface (ClassInterfaceType.None)]
public class MacroMessages : IMacroMessages {
    public void SendMessage (string message) {

        // Our implementation in VSTO
    }
}

Step 2 – Make the COM-visible to VBA

By overloading a method in the ThisAddIn.cs we can supply the Office-environment with a COM-object, simply overload the following method:

public partial class ThisAddIn {
    public static MacroMessages MacroMessages { get; set; }

    protected override object RequestComAddInAutomationService () {
        return MacroMessages ?? (MacroMessages = new MacroMessages ());
    }

    private void ThisAddIn_Startup (object sender, System.EventArgs e) { }

    private void ThisAddIn_Shutdown (object sender, System.EventArgs e) { }

    #region VSTO generated code
    // ...
    #endregion
}

Step 3 – Invoke from VBA

Now we can use the following code to invoke our method from any macro in VBA as long as our VSTO add-in is loaded:

Dim addin As COMAddIn
Dim comObject As Object

' Load the VSTO addin
Set addin = Application.COMAddIns("Your addin name")

' Fetch the COM object, unfortunatly we´re not able to get any intellisense here as it´s loaded runtime
Set comObject = addin.Object

' Invoke the method
comObject.SendMessage "This is the message we pass to the method"

As mentioned, I take no credit for inventing this code, it’s simply rewritten from the post linked above.

Enjoy!

Update 2013-12-12:
Clarified namespaces and ThisAddIn class.