如何从VBE加载项运行宏，而不使用Application.Run？ [英] How can I run a macro from a VBE add-in, without Application.Run?
问题描述
我正在为VBE编写COM加载项，其中一个核心功能涉及在点击命令栏按钮时执行现有的VBA代码。
代码是用户在标准（.bas）模块中编写的单元测试代码，它看起来像这样：
选项显式
选项私人模块
'@TestModule
私人声明为新Rubberduck.AssertClass
'@TestMethod
Public Sub TestMethod1（）'TODO：重命名测试
出现错误GoTo TestFail
'安排：
'Act：
：
Assert.Inconclusive
TestExit：
退出Sub
TestFail：
Assert.Fail测试引发一个错误：＃&错误编号& - & Err.Description
End Sub
获取主机当前实例的代码应用程序 object：
protected HostApplicationBase（string applicationName）
{
Application =（TApplication）Marshal.GetActiveObject（applicationName +.Application）;
}
以下是 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）;
}
受保护的虚拟字符串GenerateMethodCall（QualifiedMemberName qualifiedMemberName）
{
return qualifiedMemberName.ToString（）;
}
}
我有类似的代码 WordApp ， PowerPointApp 和 AccessApp
问题是Outlook的 Application 对象不会暴露 / code>方法，所以我很好，卡住了。
如何执行VBA代码一个COM加载项为VBE，没有 Application.Run ？
VBE C＃代码（从我的回答< a href =http://stackoverflow.com/a/18564779/495455>在这里创建一个VBE AddIn回答）：
命名空间VBEAddin
{
[ComVisible（true），Guid（3599862B-FF92-42DF-BB55-DBD37CC13565），ProgId（VBEAddIn.Connect）]
public class Connect：IDTExtensibility2
{
private VBE _VBE;
private AddIn _AddIn;
#regionIDTExtensibility2 Members
public void OnConnection（object application，ext_ConnectMode connectMode，object addInInst，ref Array custom）
{
try
{
_VBE =（VBE）应用程序;
_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（参考引用）
{
// TODO：使用引用在程序集中找到的地图类型。
}
private void onReferenceItemRemoved（参考参考）
{
// TODO：删除在程序集中找到的类型。
}
public void OnDisconnection（ext_DisconnectMode disconnectMode，ref Array custom）
{
}
public void OnAddInsUpdate
{
}
public void OnStartupComplete（ref array custom）
{
InitializeAddIn（）;
}
private void InitializeAddIn（）
{
MessageBox.Show（_AddIn.ProgId +在VBA编辑器版本中加载+ _VBE.Version）;
Form1 frm = new Form1（）;
frm.Show（）; //< - 这里我正在安装一个形式，当添加从VBE IDE加载！
}
public void OnBeginShutdown（ref array custom）
{
}
#endregion
}
}
我实例化并从VBE IDE InitializeAddIn（）方法加载的Form1代码：
命名空间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（）;
//我希望这只在选择一个项目时运行
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;
//调用剪贴板事件复制
mObj_of_CommandBars.ExecuteMso（Copy）;
}
}
}
}
ThisOutlookSession代码：
公开WithEvents mpubObj_Explorer As Explorer
'陷阱剪贴板事件复制
Private Sub mpubObj_Explorer_BeforeItemCopy（Cancel As Boolean）
Dim mObj_MI作为MailItem，mObj_UserProperty作为UserProperty
MsgBox（mpubObj_Explorer_BeforeItemCopy事件工作！）
'确保只选择一个项目和类型Mail
如果mpubObj_Explorer.Selection.Count = 1和mpubObj_Explorer.Selection（1）.Class = olMail然后
设置mObj_MI = mpubObj_Explorer.Selection（1）
'检查自定义属性是否存在于选定的邮件中
对于每个mObj_UserProperty在mObj_MI.UserProperties
如果mObj_UserProperty.Name =JT然后
'将魔法发生？！
Outlook.Application.Test
删除自定义属性以保持干净
mObj_UserProperty.Delete
'取消复制事件。它使调用对用户透明
Cancel = True
退出对于
结束如果
接下来
设置mObj_UserProperty =没有
设置mObj_MI =没有
End If
End Sub
Outlook VBA方法：
Public Sub Test（）
MsgBox（Will it be called？）
End Sub
很遗憾，我很遗憾地通知你，我的努力没有成功。也许它从VSTO工作（我没有试过），但试图像一个狗取骨，我现在愿意放弃！
作为一个安慰，你可以在这个答案的修订历史中找到一个疯狂的想法（它显示了一种嘲笑Office对象模型的方式）来运行Office VBA
我将在离线时向您说明如何对RubberDuck GitHub项目做出贡献，我写了代码， a href =https://blogs.office.com/2012/09/13/introducing-spreadsheet-controls-in-the-new-office/ =nofollow noreferrer> Prodiance的工作簿关系图
您可能希望在完全关闭此代码之前检查此代码，然后再将它们包含在Office Audit和版本控制服务器中。 ，我甚至不能得到mpubObj_Explorer_BeforeItemCopy事件工作，所以如果你能在Outlook中正常工作，你可能会更好。（我在家使用Outlook 2013，所以2010可能不同）。
ps你可以考虑在逆时针方向跳过一条腿后，点击我的手指，同时顺时针摩擦我的头
**
请注意，支持 SendKeys 使用 ThisOutlookSession 的唯一其他已知方法不是：
https://groups.google.com/forum/?hl=zh-CN#!topic/microsoft.public.outlook.program_vba/cQ8gF9ssN3g - 即使Sue不是Microsoft PSS 她会问并发现其不受支持的。
OLD ...以下方法适用于Office Apps除了Outlook
问题是Outlook的Application对象不公开Run方法，所以我很好， 卡住。这个回答链接到MSDN的一个博客文章看起来很有前途，所以我试过这个...但OUTLOOK.EXE进程退出与代码-1073741819（0xc0000005）'访问冲突
问题是，如何使用Reflection？代码我使用适用于Excel（应该适用于Outlook相同），使用 .Net参考： Microsoft.Office.Interop.Excel v14（不是ActiveX COM参考）：
使用System;
using Microsoft.Office.Interop.Excel;
命名空间ConsoleApplication5
{
class Program
{
static void Main（string [] args）
{
RunVBATest ）;
}
public static void RunVBATest（）
{
应用程序oExcel = new Application（）;
oExcel.Visible = true;
工作簿oBooks = oExcel.Workbooks;
_Workbook oBook = null;
oBook = oBooks.Open（C：\\temp\\ Book1.xlsm）;
//运行宏。
RunMacro（oExcel，new Object [] {TestMsg}）;
//退出Excel并清理（它更好地使用Jake Ginnivan的VSTOContrib）。
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）;
//比较你的电话看起来有点麻烦，你使用的应用程序的实例吗？
//Application.GetType().InvokeMember(qualifiedMemberName.MemberName，BindingFlags.InvokeMethod，null，Application，null）;
}
}
}
}
2 ）确保将宏代码放在一个模块（全局BAS文件）中。
Public Sub TestMsg
$ b MsgBox（Hello Stackoverflow）
End Sub
3）确保您启用了对VBA项目对象模型的宏安全性和信任访问：
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?
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:
这篇关于如何从VBE加载项运行宏，而不使用Application.Run？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
