如何从 Excel VBA 调用 .NET 方法? [英] How to call .NET methods from Excel VBA?

问题描述

我找到了一种直接从 VBA 代码调用 .NET 2 代码的方法:

I found a way to call .NET 2 code directly from VBA code:

Dim clr As mscoree.CorRuntimeHost
Set clr = New mscoree.CorRuntimeHost
clr.Start
Dim domain As mscorlib.AppDomain
clr.GetDefaultDomain domain
Dim myInstanceOfDotNetClass As Object
Set myInstanceOfDotNetClass = domain.CreateInstanceFrom("SomeDotNetAssembly.dll", "Namespace.Typename").Unwrap
Call myInstanceOfDotNetClass.ExecuteSomeDotNetMethod

我使用工具 -> 引用将 mscoree.tlb 和 mscorlib.tlb 的引用添加到 Excel VBA.

I added references to mscoree.tlb and mscorlib.tlb to Excel VBA using Tools -> References.

这适用于 .NET CLR 2 程序集，最高可达 .NET 框架版本 3.5.

This works for .NET CLR 2 assemblies, up to .NET framework version 3.5.

我需要让它与 .NET 4 一起使用.

I need to make it work with .NET 4.

我知道 .NET CLR4 引入了另一种与版本无关的创建运行时实例的方法，并且我发现了一个用 C++ 编写的代码示例:http://dev.widemeadows.de/2014/02/04/hosting-the-net-4-runtime-in-a-native-process/

I understood that .NET CLR4 introduced another, version agnostic, way of creating an instance of the runtime and I have found a code example written in C++: http://dev.widemeadows.de/2014/02/04/hosting-the-net-4-runtime-in-a-native-process/

我的 Excel VBA 技能不足以翻译那几行代码.

My Excel VBA skills are not enough to translate those few lines of code.

推荐答案

默认策略是阻止 CLR 4 从 CLR 2 中执行遗留代码:

The default policy is preventing the CLR 4 from excuting the legacy code from the CLR 2 :

Set clr = New mscoree.CorRuntimeHost

要启用遗留执行，您可以在excel.exe所在的文件夹中创建文件excel.exe.config:

To enable the legacy execution, you can either create the file excel.exe.config in the folder where excel.exe is located:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

或者你可以调用原生函数 CorBindToRuntimeEx 而不是 New mscoree.CorRuntimeHost :

Or you can call the native function CorBindToRuntimeEx instead of New mscoree.CorRuntimeHost :

Private Declare PtrSafe Function CorBindToRuntimeEx Lib "mscoree" ( _
    ByVal pwszVersion As LongPtr, _
    ByVal pwszBuildFlavor As LongPtr, _
    ByVal startupFlags As Long, _
    ByRef rclsid As Long, _
    ByRef riid As Long, _
    ByRef ppvObject As mscoree.CorRuntimeHost) As Long

Private Declare PtrSafe Function VariantCopy Lib "oleaut32" (dest, src) As Long


''
' Creates a .Net object with the CLR 4 without registration.  '
''
Function CreateInstance(assembly As String, typeName As String) As Variant
  Const CLR$ = "v4.0.30319"

  Static domain As mscorlib.AppDomain
  If domain Is Nothing Then
    Dim host As mscoree.CorRuntimeHost, hr&, T&(0 To 7)
    T(0) = &HCB2F6723: T(1) = &H11D2AB3A: T(2) = &HC000409C: T(3) = &H3E0AA34F
    T(4) = &HCB2F6722: T(5) = &H11D2AB3A: T(6) = &HC000409C: T(7) = &H3E0AA34F

    hr = CorBindToRuntimeEx(StrPtr(CLR), 0, 3, T(0), T(4), host)
    If hr And -2 Then err.Raise hr

    host.Start
    host.GetDefaultDomain domain
  End If

  VariantCopy CreateInstance, domain.CreateInstanceFrom(assembly, typeName).Unwrap
End Function

这篇关于如何从 Excel VBA 调用 .NET 方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！