如何公开一个C＃类到VBA模块中的文档级外接？ [英] How to expose a C# class to a VBA module in a document-level add-in?

问题描述

这是一个假设情况。

我想看看是否有可能暴露一个C＃类VBA的文档中。-Level附加

I would like to find out if it's possible to expose a C# class to VBA in a document-level add-in.

下面是一个SSCCE：

Here's an SSCCE:

在VS PRO 2012我已经开始一个新项目，选择办公室 - > Excel 2010的工作簿。 （的请确保您选择Net框架版本4 的）

In VS PRO 2012 I have started a new project, Selected Office -> Excel 2010 Workbook. (make sure you select .Net framework ver 4)

我添加了一个的DateTimePicker 控件到工作表Sheet1。

I have added a DateTimePicker control to Sheet1.

我可以设置/获取 .value的属性关闭的DateTimePicker 没有问题的C＃解决方案中的控制。

I can set/get the .Value property off the DateTimePicker control within the C# solution without a problem.

的在调试：的在VBA中， .value的属性的不曝光。 （尝试 .OLEFormat.Object.Value 的）

While debugging: In VBA, the .Value property is not exposed. (tried .OLEFormat.Object.Value)

并非所有的属性都可以接触到VBA因为该ActiveX控制的DateTimePicker 是 MSForms 裹所以Excel识别它（兼容性）。

Not all properties can be exposed to VBA because the ActiveX control DateTimePicker is wrapped by MSForms so Excel recognizes it (compatibility).

我需要能够抓住从VBA包装的控制的实际值，，但我不知道如何去了解它（的它是否可以或不可以）...

I need to be able to grab the actual value of the wrapped control from VBA, but I am not sure how go about it (whether it's possible or not)...

我知道该控件本身支持事件，但是这不是我想要走的道路。我希望能够抢到了一个控制静态/电流值。

I know that the control itself supports events but this is not the path I want to take. I want to be able to grab the static/current value off a control.

结果

这是我想怎么能够做到：

This is what I would like to be able to do:

• 一类添加到我的C＃解决方案

• Add a class to my C# solution

揭露它，所以它的重新创建的从VBA像昏暗的OBJ新MyExposedClass

Expose it, so it's recreatable from VBA like Dim obj as new MyExposedClass

然后让 MyExposedClass 商店参考的DateTimePicker ，因为它出现在C＃（所有属性可用）

then have MyExposedClass store reference to the DateTimePicker as it appears in C# (all properties available)

然后我可以定义一个函数的GetValue（字符串控件名称）返回从C＃POV <值/ p>

then I can define a function GetValue(string controlName) which returns the Value from C# POV

结果

于是我找到的 此解决方案 +（的这个），这似乎与应用程序级的工作加载项，但它不以文档级加载工作。

So I found this solution + (this one)that seems to work with an application-level add-in but it does not work with a document-level add-in.

<击>当我调试我的解决方案和开放的VBA的对象浏览器我可以看到，引用会自动添加到的 微软的Visual Studio 2008工具的Office执行引擎9.0类型库 的，但我不认为我可以添加一个额外的类吧...

当我打开VBE的引用也有加入该项目，但在/调试没有多余的引用我的解决方案的文件夹中有一个 ExcelWorkbook1.dll 这样如何，即使连接到解决方案？

When I open the references in VBE there are no extra references added into the project but in the /debug folder of my solution there is a ExcelWorkbook1.dll so how it that even attached to the solution?

所以我的问题是：

我怎样才能暴露类文档级用C＃来扩展默认情况下，.NET控件访问属性的范围添加为Excel？

How can I expose a class in a document-level add-in for Excel using C# to extend the range of properties accessible by default on .Net controls?

更新：

这是我迄今为止最接近的，但只有它让你暴露喜欢表，工作簿宿主项，图表等，它可以让你调用的方法，但这样我要这进一步调查，并与一些反馈

This is the closest I got so far but it only allows you to expose the host item like Worksheet, Workbook, Chart etc. It allows you to call the methods though so I am going to investigate this further and come back with some feedback

从VBA在文档级自定义调用代码

的如何：公开代码VBA在Visual C＃项目

的演练：从调用VBA代码在Visual C＃项目

推荐答案

您将需要创建一个公共接口暴露类VBA，这对我的作品作为文档级别的插件。

You would need to create a public interface to expose the class to VBA, this works for me as a document level addin.

1. 打开一个新的Excel工作簿，复制以下到模块

1. Open a new Excel workbook and copy the following into a MODULE

Sub CallVSTOMethod()
Dim dt As Date
Dim VSTOSheet1 As DocLevelAddin.Sheet1
    Set VSTOSheet1 = GetManagedClass(Sheet1)
    dt = VSTOSheet1.GetDatePickerVal
End Sub

保存Excel作为TestProj.xlsm并关闭。

Save Excel as "TestProj.xlsm" and close.

在Excel工作表Sheet1 DateTimePicker控件从wihin VS添加到图纸，双击创建ValueChanged事件并更新Sheet1.cs代码读

On the Excel Sheet1 add the DateTimePicker control to the sheet from wihin VS, double click to create a ValueChanged event and update the code in Sheet1.cs to read

private DateTime dtVal;
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
    dtVal = dateTimePicker1.Value;
}

仍然在Sheet1.cs，添加一个公共方法返回dtVal

Still in Sheet1.cs, add a public method to return dtVal

public DateTime GetDatePickerVal()
{
    return dtVal;
}

另外添加如下Sheet1.cs

Also add the following to Sheet1.cs

protected override object GetAutomationObject()
{
    return this;
}

以上的公共部分类工作表Sheet1中Sheet1.cs添加以下

Above public partial class Sheet1 in Sheet1.cs add the following

[System.Runtime.InteropServices.ComVisible(true)]
[System.Runtime.InteropServices.ClassInterface( 
    System.Runtime.InteropServices.ClassInterfaceType.None)]

现在你需要创建一个公共接口的方法。在Sheet1.cs右击选择重构，提取接口和检查公用方法GetDatePickerVal

Now you need to create a public interface for the method. In Sheet1.cs right click select Refactor, Extract Interface and check the public method GetDatePickerVal

请界面市民和COM可见

Make the interface public and COM visible

[System.Runtime.InteropServices.ComVisible(true)]
public interface ISheet1
{
    DateTime GetDatePickerVal();
}

双击Sheet1.cs所以Excel工作表中是可见的。选择任一单元格，打开属性窗口，更改属性ReferenceAssemblyFromVbaProject =真

Double click Sheet1.cs so the Excel sheet is visible. Select any cell to open the properties window and change property ReferenceAssemblyFromVbaProject = true

在Excel中，您可能需要转到信托中心设置并添加V​​S解决方案文件夹和子文件夹作为受信任位置

In Excel you may need to goto Trust Centre Settings and add the VS Solution folder and sub folders as trusted location

运行在Excel中模块项目和代码将通过暴露GetDatePickerVal方法返回dateTimePicker的。

Run the project and the code in the Excel MODULE will return the dateTimepicker through the exposed GetDatePickerVal method.

Sheet1.cs：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace DocLevelAddin
{
    [System.Runtime.InteropServices.ComVisible(true)]
    [System.Runtime.InteropServices.ClassInterface(
        System.Runtime.InteropServices.ClassInterfaceType.None)]
    public partial class Sheet1 : DocLevelAddin.ISheet1
    {
        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
        }

        private void Sheet1_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.dateTimePicker1.ValueChanged += new System.EventHandler(this.dateTimePicker1_ValueChanged);
            this.Startup += new System.EventHandler(this.Sheet1_Startup);
            this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);

        }

        #endregion

        private DateTime dtVal;
        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            dtVal = dateTimePicker1.Value;
        }

        public DateTime GetDatePickerVal()
        {
            return dtVal;
        }

        protected override object GetAutomationObject()
        {
            return this;
        }

    }
}

ISheet1.cs：

using System;
namespace DocLevelAddin
{
    [System.Runtime.InteropServices.ComVisible(true)]
    public interface ISheet1
    {
        DateTime GetDatePickerVal();
    }
}

这篇关于如何公开一个C＃类到VBA模块中的文档级外接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！