在EXCEL VBA中使用C＃DLL [英] Using a C# dll inside EXCEL VBA

问题描述

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

我有一个通过COM互操作暴露的C＃DLL。它正在工作，但显然C＃互操作对象的部署是一场灾难，每次更新DLL时都需要重新生成。

所以我想知道如何使用这个C＃DLL中的函数，如下所示：
或者只要将DLL和电子表格放在一起就可以调用函数。

 声明函数getString Lib<我的DLL的路径> （）as string 
 
 sub test（）
 range（A1）。value = getString 
 End Sub 
  

语法可能是错误的。

解决方案

但您必须了解VBA和.Net的差异。

首先，您必须创建一个实际的DLL（.Net程序集不是），为此，请使用此项目模板。
那么再一次，你必须知道如何组织东西。

VBA只支持stdcall作为调用约定，它不能真正处理Unicode的DLL函数。这是不错的，因为.Net中String的默认调度是VBA所期望的（指向Ansi char的指针）。另外，stdcall是我用于导出的默认调用约定。

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

将其放在您使用我的模板创建的项目中：

  [ComVisible（true） ，ClassInterface（ClassInterfaceType.AutoDual）] 
 public class Sample 
 {
 public string Text 
 {
 [return：MarshalAs（UnmanagedType.BStr）] 
得到; 
 [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调用： p>

 声明函数CreateDotNetObject Lib如果可以从Excel访问程序集或程序集的完整路径_ 
 ByVal Text As String）作为对象
 
子测试（）
 
 Dim instance As Object 
 
 Set instance = CreateDotNetObject（Test 1）
 Debug.Print instance.Text 
 
 Debug.Print instance.TestMethod 
 
 instance.text =abc 123VBA中的大小写不正确的工作原理
 
 Debug.Print instance.Text 
 End Sub 
  

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

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.

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.

解决方案

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.

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 };
   }
}

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屋！