How can I run a macro from a VBE add-in, without A

Question

I'm writing a COM add-in for the VBE, and one of the core features involves executing existing VBA code upon clicking a commandbar button.

The code is unit testing code written by the user, in a standard (.bas) module that looks something like this:

Option Explicit
Option Private Module

'@TestModule
Private Assert As New Rubberduck.AssertClass

'@TestMethod
Public Sub TestMethod1() 'TODO: Rename test
    On Error GoTo TestFail

    'Arrange:

    'Act:

    'Assert:
    Assert.Inconclusive

TestExit:
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

So I have this code that gets the current instance of the host Application object:

protected HostApplicationBase(string applicationName)
{
    Application = (TApplication)Marshal.GetActiveObject(applicationName + ".Application");
}

Here's the ExcelApp class:

public class ExcelApp : HostApplicationBase<Microsoft.Office.Interop.Excel.Application>
{
    public ExcelApp() : base("Excel") { }

    public override void Run(QualifiedMemberName qualifiedMemberName)
    {
        var call = GenerateMethodCall(qualifiedMemberName);
        Application.Run(call);
    }

    protected virtual string GenerateMethodCall(QualifiedMemberName qualifiedMemberName)
    {
        return qualifiedMemberName.ToString();
    }
}

Works like a charm. I have similar code for WordApp, PowerPointApp and AccessApp, too.

The problem is that Outlook's Application object doesn't expose a Run method, so I'm, well, stuck.

---

How can I execute VBA code from a COM add-in for the VBE, without Application.Run?

This answer links to a blog post on MSDN that looks promising, so I tried this:

public class OutlookApp : HostApplicationBase<Microsoft.Office.Interop.Outlook.Application>
{
    public OutlookApp() : base("Outlook") { }

    public override void Run(QualifiedMemberName qualifiedMemberName)
    {
        var app = Application.GetType();
        app.InvokeMember(qualifiedMemberName.MemberName, BindingFlags.InvokeMethod, null, Application, null);
    }
}

But then the best I'm getting is a COMException that says "unknown name", and the OUTLOOK.EXE process exiting with code -1073741819 (0xc0000005) 'Access violation' - and it blows up just as nicely with Excel, too.

---

UPDATE

This VBA code works, if I put TestMethod1 inside ThisOutlookSession:

Outlook.Application.TestMethod1

Note that TestMethod1 isn't listed as a member of Outlook.Application in VBA IntelliSense.. but somehow it happens to work.

The question is, how do I make this work with Reflection?

Answer 1

Update 3:

I found this post on MSDN forums: Call Outlook VBA sub from VSTO.

Obviously it uses VSTO and I tried converting it to a VBE AddIn, but ran into issues at work with x64 Windows with a Register Class issue:

COMException (0x80040154): Retrieving the COM class factory for component with CLSID {55F88893-7708-11D1-ACEB-006008961DA5} failed due to the following error: 80040154 Class not registered

Anyway this is the guys answer who reckons he got it working:

_{Start Of MSDN Forum Post}

I found a way! What could be triggered from both VSTO and VBA? The Clipboard!!

So I used the clipboard to pass messages from one environment to the other. Here is some few codes that will explain my trick:

VSTO:

'p_Procedure is the procedure name to call in VBA within Outlook

'mObj_ou_UserProperty is to create a custom property to pass an argument to the VBA procedure

Private Sub p_Call_VBA(p_Procedure As String)
    Dim mObj_of_CommandBars As Microsoft.Office.Core.CommandBars, mObj_ou_Explorer As Outlook.Explorer, mObj_ou_MailItem As Outlook.MailItem, mObj_ou_UserProperty As Outlook.UserProperty

    mObj_ou_Explorer = Globals.Menu_AddIn.Application.ActiveExplorer
    'I want this to run only when one item is selected

    If mObj_ou_Explorer.Selection.Count = 1 Then
        mObj_ou_MailItem = mObj_ou_Explorer.Selection(1)
        mObj_ou_UserProperty = mObj_ou_MailItem.UserProperties.Add("COM AddIn-Azimuth", Outlook.OlUserPropertyType.olText)
        mObj_ou_UserProperty.Value = p_Procedure
        mObj_of_CommandBars = mObj_ou_Explorer.CommandBars

        'Call the clipboard event Copy
        mObj_of_CommandBars.ExecuteMso("Copy")
    End If
End Sub

VBA:

Create a class for Explorer events and trap this event:

Public WithEvents mpubObj_Explorer As Explorer

'Trap the clipboard event Copy
Private Sub mpubObj_Explorer_BeforeItemCopy(Cancel As Boolean)
Dim mObj_MI As MailItem, mObj_UserProperty As UserProperty

    'Make sure only one item is selected and of type Mail

    If mpubObj_Explorer.Selection.Count = 1 And mpubObj_Explorer.Selection(1).Class = olMail Then
        Set mObj_MI = mpubObj_Explorer.Selection(1)
        'Check to see if the custom property is present in the mail selected
        For Each mObj_UserProperty In mObj_MI.UserProperties
            If mObj_UserProperty.Name = "COM AddIn-Azimuth" Then
                Select Case mObj_UserProperty.Value
                    Case "Example_Add_project"
                        '...
                    Case "Example_Modify_planning"
                        '...
                End Select
                'Remove the custom property, to keep things clean
                mObj_UserProperty.Delete

                'Cancel the Copy event.  It makes the call transparent to the user
                Cancel = True
                Exit For
            End If
        Next
        Set mObj_UserProperty = Nothing
        Set mObj_MI = Nothing
    End If
End Sub

_{End Of MSDN Forum Post}

So the author of this code adds a UserProperty to a mail item and passes the function name that way. Again this would require some boiler plate code in Outlook and at least 1 mail item.

Update 3a:

The 80040154 Class not registered I was getting was because despite targeting x86 platform when I translated the code from VSTO VB.Net to VBE C# I was instantiating items, eg:

Microsoft.Office.Core.CommandBars mObj_of_CommandBars = new Microsoft.Office.Core.CommandBars();

After wasting several more hours on it, I came up with this code, that ran!!!

The VBE C# Code (from my answer make a VBE AddIn answer here):

namespace VBEAddin
{
    [ComVisible(true), Guid("3599862B-FF92-42DF-BB55-DBD37CC13565"), ProgId("VBEAddIn.Connect")]
    public class Connect : IDTExtensibility2
    {
        private VBE _VBE;
        private AddIn _AddIn;

        #region "IDTExtensibility2 Members"

        public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
        {
            try
            {
                _VBE = (VBE)application;
                _AddIn = (AddIn)addInInst;

                switch (connectMode)
                {
                    case Extensibility.ext_ConnectMode.ext_cm_Startup:
                        break;
                    case Extensibility.ext_ConnectMode.ext_cm_AfterStartup:
                        InitializeAddIn();

                        break;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void onReferenceItemAdded(Reference reference)
        {
            //TODO: Map types found in assembly using reference.
        }

        private void onReferenceItemRemoved(Reference reference)
        {
            //TODO: Remove types found in assembly using reference.
        }

        public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)
        {
        }

        public void OnAddInsUpdate(ref Array custom)
        {
        }

        public void OnStartupComplete(ref Array custom)
        {
            InitializeAddIn();
        }

        private void InitializeAddIn()
        {
            MessageBox.Show(_AddIn.ProgId + " loaded in VBA editor version " + _VBE.Version);
            Form1 frm = new Form1();
            frm.Show();   //<-- HERE I AM INSTANTIATING A FORM WHEN THE ADDIN LOADS FROM THE VBE IDE!
        }

        public void OnBeginShutdown(ref Array custom)
        {
        }

        #endregion
    }
}

The Form1 code that I instantiate and load from the VBE IDE InitializeAddIn() method:

namespace VBEAddIn
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Call_VBA("Test");
        }

        private void Call_VBA(string p_Procedure)
        {
            var olApp = new Microsoft.Office.Interop.Outlook.Application();
            Microsoft.Office.Core.CommandBars mObj_of_CommandBars;

            Microsoft.Office.Core.CommandBars mObj_of_CommandBars = new Microsoft.Office.Core.CommandBars();
            Microsoft.Office.Interop.Outlook.Explorer mObj_ou_Explorer;
            Microsoft.Office.Interop.Outlook.MailItem mObj_ou_MailItem;
            Microsoft.Office.Interop.Outlook.UserProperty mObj_ou_UserProperty;

            //mObj_ou_Explorer = Globals.Menu_AddIn.Application.ActiveExplorer
            mObj_ou_Explorer = olApp.ActiveExplorer();

            //I want this to run only when one item is selected
            if (mObj_ou_Explorer.Selection.Count == 1)
            {
                mObj_ou_MailItem = mObj_ou_Explorer.Selection[1];
                mObj_ou_UserProperty = mObj_ou_MailItem.UserProperties.Add("JT", Microsoft.Office.Interop.Outlook.OlUserPropertyType.olText);
                mObj_ou_UserProperty.Value = p_Procedure;
                mObj_of_CommandBars = mObj_ou_Explorer.CommandBars;

                //Call the clipboard event Copy
                mObj_of_CommandBars.ExecuteMso("Copy");
            }
        }
    }
}

The ThisOutlookSession Code:

Public WithEvents mpubObj_Explorer As Explorer

'Trap the clipboard event Copy
Private Sub mpubObj_Explorer_BeforeItemCopy(Cancel As Boolean)
Dim mObj_MI As MailItem, mObj_UserProperty As UserProperty

MsgBox ("The mpubObj_Explorer_BeforeItemCopy event worked!")
    'Make sure only one item is selected and of type Mail

    If mpubObj_Explorer.Selection.Count = 1 And mpubObj_Explorer.Selection(1).Class = olMail Then
        Set mObj_MI = mpubObj_Explorer.Selection(1)
        'Check to see if the custom property is present in the mail selected
        For Each mObj_UserProperty In mObj_MI.UserProperties
            If mObj_UserProperty.Name = "JT" Then

                'Will the magic happen?!
                Outlook.Application.Test

                'Remove the custom property, to keep things clean
                mObj_UserProperty.Delete

                'Cancel the Copy event.  It makes the call transparent to the user
                Cancel = True
                Exit For
            End If
        Next
        Set mObj_UserProperty = Nothing
        Set mObj_MI = Nothing
    End If
End Sub

The Outlook VBA Method:

Public Sub Test()
MsgBox ("Will this be called?")
End Sub

Very sadly, I regret to inform you that my efforts were unsuccessful. Maybe it does work from VSTO (I haven't tried) but after trying like a dog fetching a bone, I am now willing to give up!

Never the less as a consolation you can find a crazy idea in the Revision History of this answer (it shows a way of Mocking an Office Object Model) to run Office VBA unit tests that are private with parameters.

I will speak to you offline about contributing to the RubberDuck GitHub project, I wrote code that does the same thing as Prodiance's Workbook Relationship Diagram before Microsoft bought them out and included their product in Office Audit and Version Control Server.

You may wish to examine this code before dismissing it entirely, I couldn't even get the mpubObj_Explorer_BeforeItemCopy event to work, so if you can get that working normally in Outlook you might fare better. (I'm using Outlook 2013 at home, so 2010 might be different).

ps You would think after hopping on one leg in an anti-clockwise direction, clicking my fingers while rubbing my head clockwise like Workaround Method 2 in this KB Article that I would have nailed it... nup I just lost more hair!

---

Update 2:

Inside your Outlook.Application.TestMethod1 can't you just use VB classics CallByName method so you dont need reflection? You'd need to set a string property "Sub/FunctionNameToCall" before calling the method containing the CallByName to specify what sub/function to call.

Unfortunately users would be required to insert some boiler plate code in one of their Module's.

---

Update 1:

This is going to sound really dodgy, but since Outlooks' object model has fully clamped down its Run method you could resort to... SendKeys (yeah I know, but it will work).

Unfortunately the oApp.GetType().InvokeMember("Run"...) method described below works for all Office Apps except Outlook - based on the Properties section in this KB Article: https://support.microsoft.com/en-us/kb/306683, sorry I didn't know that until now and found it very frustrating trying and the MSDN article misleading, ultimately Microsoft has locked it:

** Note that SendKeys is supported and the only other known way using ThisOutlookSession is not: https://groups.google.com/forum/?hl=en#!topic/microsoft.public.outlook.program_vba/cQ8gF9ssN3g - even though Sue isn't Microsoft PSS she would've asked and found out its unsupported.

---

OLD... The below method works with Office Apps except for Outlook

The problem is that Outlook's Application object doesn't expose a Run method, so I'm, well, stuck. This answer links to a blog post on MSDN that looks promising, so I tried this ... but OUTLOOK.EXE process exits with code -1073741819 (0xc0000005) 'Access violation'

The question is, how do I make this work with Reflection?

1) Here is the code I use that works for Excel (should work for Outlook just the same), using the .Net reference: Microsoft.Office.Interop.Excel v14 (not the ActiveX COM Reference):

using System;
using Microsoft.Office.Interop.Excel;

namespace ConsoleApplication5
{
class Program
{
static void Main(string[] args)
{
    RunVBATest();
}

public static void RunVBATest()
{
    Application oExcel = new Application();
    oExcel.Visible = true;
    Workbooks oBooks = oExcel.Workbooks;
    _Workbook oBook = null;
    oBook = oBooks.Open("C:\\temp\\Book1.xlsm");

    // Run the macro.
    RunMacro(oExcel, new Object[] { "TestMsg" });

    // Quit Excel and clean up (its better to use the VSTOContrib by Jake Ginnivan).
    oBook.Saved = true;
    oBook.Close(false);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
}

private static void RunMacro(object oApp, object[] oRunArgs)
{
    oApp.GetType().InvokeMember("Run",
        System.Reflection.BindingFlags.Default |
        System.Reflection.BindingFlags.InvokeMethod,
        null, oApp, oRunArgs);

    //Your call looks a little bit wack in comparison, are you using an instance of the app?
    //Application.GetType().InvokeMember(qualifiedMemberName.MemberName, BindingFlags.InvokeMethod, null, Application, null);
}
}
}
}

2) make sure you put the Macro code in a Module (a Global BAS file)..

Public Sub TestMsg()

MsgBox ("Hello Stackoverflow")

End Sub

3) make sure you enable Macro Security and Trust access to the VBA Project object model:

Answer 2

Try this thread, it looks like Outlook is different but then I think you know this already. The hack given maybe sufficient.

Create your code as Public Subs and put the code in the ThisOutlookSession class module. You can then use Outlook.Application.MySub() to call your sub named MySub. Of course change that for the correct name.

Social MSDN: < Application.Run > equivalent for Microsoft Outlook

Answer 3

EDIT - This new approach uses a CommandBar control as a proxy and avoids the need for events and tasks, but you can read more about the old approach further below.

var app = Application;
var exp = app.ActiveExplorer();
CommandBar cb = exp.CommandBars.Add("CallbackProxy", Temporary: true);
CommandBarControl btn = cb.Controls.Add(MsoControlType.msoControlButton, 1);
btn.OnAction = "MyCallbackProcedure";
btn.Execute();
cb.Delete();

It's worth noting that Outlook seems to only like ProjectName.ModuleName.MethodName or MethodName when assigning the OnAction value. It didn't execute when it was assigned as ModuleName.MethodName

Original Answer...

SUCCESS - It seems that Outlook VBA and Rubberduck can talk to each other, but only after Rubberduck can trigger some VBA code to run. But without Application.Run, and without any methods in ThisOutlookSession having DispIDs or anything that resembles a formal Type Library, it's hard for Rubberduck to call anything directly...

Fortunately, the Application event handlers for ThisOutlookSession allow us to trigger an event from a C# DLL/Rubberduck, and we can then use that event to open up the lines of communication. And, this method doesn't require the presence of any pre-existing items, rules or folders. It's achievable solely by editing the VBA.

I'm using a TaskItem, but you could probably use any Item that triggers the Application's ItemLoad event. Likewise, I'm using the Subject and Body attributes, but you could choose different properties (in fact, the body attribute is problematic because Outlook seems to add white-space, but for now, I'm handling that).

Add this code to ThisOutlookSession

Option Explicit

Const RUBBERDUCK_GUID As String = "Rubberduck"

Public WithEvents itmTemp As TaskItem
Public WithEvents itmCallback As TaskItem

Private Sub Application_ItemLoad(ByVal Item As Object)
  'Save a temporary reference to every new taskitem that is loaded
  If TypeOf Item Is TaskItem Then
    Set itmTemp = Item
  End If
End Sub

Private Sub itmTemp_PropertyChange(ByVal Name As String)
  If itmCallback Is Nothing And Name = "Subject" Then
    If itmTemp.Subject = RUBBERDUCK_GUID Then
      'Keep a reference to this item
      Set itmCallback = itmTemp
    End If
    'Discard the original reference
    Set itmTemp = Nothing
  End If
End Sub

Private Sub itmCallback_PropertyChange(ByVal Name As String)
  If Name = "Body" Then

    'Extract the method name from the Body
    Dim sProcName As String
    sProcName = Trim(Replace(itmCallback.Body, vbCrLf, ""))

    'Set up an instance of a class
    Dim oNamedMethods As clsNamedMethods
    Set oNamedMethods = New clsNamedMethods

    'Use VBA's CallByName method to run the method
    On Error Resume Next
    VBA.CallByName oNamedMethods, sProcName, VbMethod
    On Error GoTo 0

    'Discard the item, and destroy the reference
    itmCallback.Close olDiscard
    Set itmCallback = Nothing
  End If
End Sub

Then, create a class module called clsNamedMethods and add the named methods you'd like to call.

    Option Explicit

    Sub TestMethod1()
      TestModule1.TestMethod1
    End Sub

    Sub TestMethod2()
      TestModule1.TestMethod2
    End Sub

    Sub TestMethod3()
      TestModule1.TestMethod3
    End Sub

    Sub ModuleInitialize()
      TestModule1.ModuleInitialize
    End Sub

    Sub ModuleCleanup()
      TestModule1.ModuleCleanup
    End Sub

    Sub TestInitialize()
      TestModule1.TestInitialize
    End Sub

    Sub TestCleanup()
      TestModule1.TestCleanup
    End Sub

And then implement the real methods in a standard module called TestModule1

Option Explicit
Option Private Module

'@TestModule
'' uncomment for late-binding:
'Private Assert As Object
'' early-binding requires reference to Rubberduck.UnitTesting.tlb:
Private Assert As New Rubberduck.AssertClass

'@ModuleInitialize
Public Sub ModuleInitialize()
    'this method runs once per module.
    '' uncomment for late-binding:
    'Set Assert = CreateObject("Rubberduck.AssertClass")
End Sub

'@ModuleCleanup
Public Sub ModuleCleanup()
    'this method runs once per module.
End Sub

'@TestInitialize
Public Sub TestInitialize()
    'this method runs before every test in the module.
End Sub

'@TestCleanup
Public Sub TestCleanup()
    'this method runs afer every test in the module.
End Sub

'@TestMethod
Public Sub TestMethod1() 'TODO Rename test
    On Error GoTo TestFail

    'Arrange:

    'Act:

    'Assert:
    Assert.AreEqual True, True

TestExit:
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

'@TestMethod
Public Sub TestMethod2() 'TODO Rename test
    On Error GoTo TestFail

    'Arrange:

    'Act:

    'Assert:
    Assert.Inconclusive

TestExit:
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

'@TestMethod
Public Sub TestMethod3() 'TODO Rename test
    On Error GoTo TestFail

    'Arrange:

    'Act:

    'Assert:
    Assert.Fail

TestExit:
    Exit Sub
TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub

Then, from the C# code, you can trigger the Outlook VBA code with:

TaskItem taskitem = Application.CreateItem(OlItemType.olTaskItem);
taskitem.Subject = "Rubberduck";
taskitem.Body = "TestMethod1";

Notes

This is a proof of concept, so I know there are some issues that need to be tidied up. For one, any new TaskITem that has a Subject of "Rubberduck" is going to be treated as a payload.

I'm using a standard VBA class here, but the class could be made to be static (by editing the attributes), and the CallByName method should still work.

Once the DLL is able to execute VBA code in this manner, there are further steps that can be taken to tighten the integration:

1. You could pass method pointers back to C#\Rubberduck using the AddressOf operator, and then C# could call those procedures by their function pointers, using something like Win32's CallWindowProc

2. You could create a VBA class with a default member, and then assign an instance of that class to a C# DLL property that requires a callback handler. (similar to the OnReadyStateChange property of the MSXML2.XMLHTTP60 object)

3. You could pass details using a COM object, like Rubberduck is already doing with the Assert class.

4. I haven't thought this one through, but I wonder if you defined a VBA class with PublicNotCreatable instancing, whether you could then pass that to C#?

And finally, while this solution does involve a small amount of boilerplate, it would have to play nice with any existing event handlers, and I haven't dealt with that.