我正在为 VBE 编写 COM 加载项，其中一项核心功能涉及在单击命令栏按钮时执行现有的 VBA 代码。
代码是用户编写的单元测试代码，在一个标准 (.bas) 模块中，如下所示:

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

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

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();
    }
}

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);
    }
}

Outlook.Application.TestMethod1

最佳答案

更新 3:

我在 MSDN forums: Call Outlook VBA sub from VSTO 上找到了这篇文章.

显然它使用 VSTO，我尝试将其转换为 VBE AddIn ，但在使用 x64 Windows 时遇到了注册类问题:

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

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:

'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

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

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

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
    }
}

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");
            }
        }
    }
}

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

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

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?

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);
}
}
}
}

Public Sub TestMsg()

MsgBox ("Hello Stackoverflow")

End Sub

关于c# - 如何在没有 Application.Run 的情况下从 VBE 加载项运行宏？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31954364/