在宏中调用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屋！