在宏中调用Excel加载项功能 [英] Call Excel Add-In function in macro
问题描述
我正在为Excel 2013开发外接程序,并在Excel外接程序中创建了如下功能
I am developing Add-in for Excel 2013 and I have created a function in Excel Add-In as below
public string ExcelReturnString()
{
return "This is the string: hi";
}
我已经使用下面的代码来调用该函数,但是会引发错误.
I have used below code to call the function, but it throws an error.
Application.Run(ExcelReturnString)
如何在宏中调用外接程序功能?
How can I call the Add-in function in macro?
推荐答案
这是最直接的事情,但这是完成任务的方式.我将尽可能地明确,因为尝试进行此操作的前两三遍,我错过了很多.
This is about the farthest thing from straight-forward, but this is how you accomplish the task. I'm going to be as explicit as possible, because the first two or three times I tried to do this, I missed a LOT.
首先,当创建承载ExcelReturnString()
的类时,需要使用具有以下属性的接口来装饰该类,然后还为每个要公开的方法标记这些属性.为了这个示例,我制作了附加类"TestExcelAddIn":
First, when you create the class that hosts ExcelReturnString()
, you need to decorate the class with an interface that has the following attributes and then also tag the attributes for each method you want to expose. I made the add-in class "TestExcelAddIn" for the sake of this example:
using System.Data;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace TestExcelAddIn
{
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IStringGetter
{
string ExcelReturnString();
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class StringGetter : IStringGetter
{
public string ExcelReturnString()
{
return "This is the string: hi";
}
}
}
然后,在主类中,与项目中的"Excel"相关联,您必须按以下方式覆盖RequestComAddInAutomationService
.同样,我包括所有内容,因此您知道哪个班级是什么(当我第一次阅读它时就没有).
Then, in the main class, associated with "Excel" in your project, you have to override RequestComAddInAutomationService
in the following manner. Again, I am including EVERYTHING so you know which class is which (I didn't when I first read it).
namespace TestExcelAddIn
{
public partial class ExcelTest
{
private StringGetter myAddIn;
protected override object RequestComAddInAutomationService()
{
if (myAddIn == null)
myAddIn = new StringGetter();
return myAddIn;
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
#endregion
}
}
现在,VBA准备以以下方式使用此方法:
Now VBA is ready to consume this method in the following manner:
Sub Test()
Dim addin As Office.COMAddIn
Dim automationObject As Object
Dim returnString As String
Set addin = Application.COMAddIns("TestExcelAddIn")
Set automationObject = addin.Object
returnString = automationObject.ExcelReturnString
End Sub
您本可以给我100年的时间来解决这个问题,而我不会.实际上,将其上的Rosetta石头归功于MSDN:
You could have given me 100 years to figure this out, and I would not have. Actually credit MSDN for the Rosetta stone on it:
https://msdn.microsoft .com/en-us/library/bb608621.aspx?f = 255& MSPPError = -2147217396
这篇关于在宏中调用Excel加载项功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!