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

问题描述

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

  Dim clr作为mscoree.CorRuntimeHost 
设置clr =新mscoree.CorRuntimeHost 
 clr.Start 
 Dim域作为mscorlib.AppDomain 
 clr.GetDefaultDomain域
 Dim myInstanceOfDotNetClass作为对象
设置myInstanceOfDotNetClass = domain.CreateInstanceFrom（ SomeDotNetAssembly.dll， Namespace.Typename）。展开
调用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-在本地运行时/

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

解决方案

默认策略是阻止CLR 4从CLR 2中继承旧代码：

 设置clr =新的mscoree.CorRuntimeHost 
  

要启用旧版执行，您可以在<$ c $文件夹中创建文件 excel.exe.config c> excel.exe 位于：

 <？xml版本= 1.0编码= utf-8吗？ 
< configuration> 
< startup useLegacyV2RuntimeActivationPolicy = true> 
< supportedRuntime版本= v4.0 /> 
< / startup> 
< / configuration> 
  

或者您可以调用本机函数 CorBindToRuntimeEx 而不是 New mscoree.CorRuntimeHost ：

 私有声明PtrSafe函数CorBindToRuntimeEx Lib mscoree（_ 
 ByVal pwszVersion作为LongPtr，_ 
 ByVal pwszBuildFlavor作为LongPtr，_ 
 ByVal startupFlags作为long，__ 
 ByVal startupFlags作为Long，_ 
 ByRef长期作为_ ，_ 
 ByRef riid为long，__ 
 ByRef ppvObject为mscoree.CorRuntimeHost）作为long 
 
私有声明PtrSafe函数VariantCopy Lib oleaut32（dest，src）作为Long 
 
 
''
'使用CLR 4创建一个.Net对象，无需注册。 '
 
函数CreateInstance（程序集作为字符串，typeName作为字符串）作为变量
常量CLR $ = v4.0.30319 
 
静态域，为mscorlib。 AppDomain 
如果域为空，则
昏暗的主机为mscoree.CorRuntimeHost，hr& ;, T&（0到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）
如果hr And- 2然后err.Raise hr 
 
 host.Start 
 host.GetDefaultDomain domain 
 End if 
 
 VariantCopy CreateInstance，domain.CreateInstanceFrom（assembly，typeName）展开
结束函数
  

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

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

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

I need to make it work with .NET 4.

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/

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

解决方案

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

Set clr = New mscoree.CorRuntimeHost

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>

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