如何从Excel VBA调用.NET方法？

一尘不染

如何从Excel VBA调用.NET方法？

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

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。

这适用于.NET CLR 2程序集（最高.NET Framework版本3.5）。

我需要使其与.NET 4一起使用。

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

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

阅读 756

2020-05-19

共1个答案

一尘不染

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

Set clr = New mscoree.CorRuntimeHost

要启用旧版执行，您可以excel.exe.config在以下位置的文件夹中创建文件excel.exe：

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

或者，您可以调用本机函数CorBindToRuntimeEx而不是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

2020-05-19