使用EXCEL VBA里面一个C＃DLL [英] Using a C# dll inside EXCEL VBA

问题描述

我遇到了一个小问题，这里需要你们的帮助。

I am running into a little problem here and need you guys' help.

我要通过COM互操作暴露一个C＃DLL。它的工作好了，但显然C＃互操作对象的部署是一个灾难，你需要regasm每次更新的DLL的时间。

I have a C# DLL exposed through COM interop. It is working alright, but apparently the deployment of C# interop object is a disaster and you need to regasm every time you update the DLL.

所以，我想知道我怎么可以使用函数从该C＃像下面的DLL：
或者什么，我可以只把DLL和S preadsheet一起调用的函数。

So I am wondering how I can use the functions from this C# DLL like the following: Or anything that I can call the functions by just putting the DLL and the spreadsheet together.

Declare Function getString Lib "<PATH of my DLL>" () as string

sub test()
   range("A1").value = getString
End Sub

语法可能是错误的。

Syntax might be wrong.

推荐答案

您可以做到这一点，但你必须要知道的VBA和.Net的差异。结果
首先，你必须创建一个实际的DLL（.NET程序集都没有），要做到这一点，使用<一个href=\"https://sites.google.com/site/robertgiesecke/Home/uploads/csharpprojecttemplateforunmanagedexports\">this项目模板。
话又说回来，你必须要知道如何编组的东西。结果
VBA仅支持stdcall调用的调用约定，它可以真正处理不统一code的DLL函数。这是不坏本身作为默认编组为净字符串是VBA期待（指针到一个ANSI字符）。此外，STDCALL是我使用的默认输出调用约定。

You can do that, but you have to be aware of the differences of VBA and .Net.
First, you have to create an actual DLL (.Net assemblies are not), to do that, use this project template. Then again, you have to be aware of how to marshal stuff.
VBA only supports stdcall as calling convention and it can't really deal with Unicode for DLL functions. This isn't bad per se, as the default marshaling for String in .Net is what VBA is expecting (a pointer to an Ansi char). Also, stdcall is the default calling convention that I use for exports.

我将利用一个示例我最近创建另一个线程SO：

I'll reuse a sample I've create recently for another SO thread:

在你使用我的模板创建了一个项目将这个：

Put this in a project you created using my template:

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class Sample
{
   public string Text
   {
      [return: MarshalAs(UnmanagedType.BStr)]
      get;
      [param: MarshalAs(UnmanagedType.BStr)]
      set;
   }

   [return: MarshalAs(UnmanagedType.BStr)]
   public string TestMethod()
   {
      return Text + "...";
   }
}

static class UnmanagedExports
{
   [DllExport]
   [return: MarshalAs(UnmanagedType.IDispatch)]
   static Object CreateDotNetObject(String text)
   {
      return new Sample { Text = text };
   }
}

这是如何把它从VBA调用：

This is how to call it from VBA:

Declare Function CreateDotNetObject Lib "The full path to your assembly or just the assembly if it is accessible from Excel" _
  (ByVal text As String) As Object

Sub test()

  Dim instance As Object

  Set instance = CreateDotNetObject("Test 1")
  Debug.Print instance.Text

  Debug.Print instance.TestMethod

  instance.text = "abc 123" ' case insensitivity in VBA works as expected'

  Debug.Print instance.Text
End Sub

这篇关于使用EXCEL VBA里面一个C＃DLL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！