在 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# 互操作对象的部署是一场灾难，每次更新 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 和电子表格放在一起来调用函数的任何东西.

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

语法可能有误.

推荐答案

你可以这样做，但你必须注意 VBA 和 .Net 的区别.
首先，您必须创建一个实际的 DLL(.Net 程序集不是)，为此，请使用 此项目模板.再说一次，你必须知道如何编组东西.
VBA 只支持 stdcall 作为调用约定，它不能真正处理 DLL 函数的 Unicode.这本身还不错，因为 .Net 中 String 的默认封送处理是 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屋！