在VBA中使用.NET HashTable返回类型 [英] Using .NET HashTable Return Type in VBA

问题描述

我已经在VB.NET中创建了一个.NET库，并且有一个函数可以返回 HashTable 的对象.

I have created a .NET library in VB.NET and there is a function that returns an object of HashTable.

我一直在寻找如何在Excel VBA中访问HashTable对象中的元素，但是找不到解决方案.我是VBA的新手，请原谅.我已经搜索过，但找不到出路.

I have been searching for how to access the elements in the HashTable object in Excel VBA but can't find a solution. I am new to VBA so pardon me. I have searched but can't find a way out.

例如，经过这样的事情，我不知道如何访问数据.

For instance, after something like this, I don't know how to access the data.

   Dim hashData As Object
   Set hashData = obj.getHashData

请帮助

推荐答案

Dim hashData As Object
Set hashData = obj.getHashData

如果 getHashData 返回一个 HashTable ，则 hashData 是一个后期绑定的 HashTable ，您可以调用其成员，包括其 Item 属性:

If getHashData is returning a HashTable, then hashData is a late-bound HashTable, and you can invoke its members, including its Item property:

Dim value As Variant
value = hashData.Item("key")

您不会在针对 Object 的后期绑定成员调用上获得编译时验证，因此您需要特别注意拼写错误，因为 Option Explicit 不能涉及后期绑定时可以节省您的时间.有关可以调用哪些成员，请参考上面链接的 HashTable 文档.

You're not getting compile-time validation on the late-bound member calls against Object, so you need to be particularly careful for typos, because Option Explicit cannot save you when late binding is involved. Refer to the HashTable documentation linked above for what members you can invoke.

添加对 mscorlib.tlb 的引用(您可以在 C:\ Windows \ Microsoft.NET \ Framework \ v4.0.30319 下找到它，或引用等效项从 \ Framework64 中获取，如果您的Excel是64位-库的位需要与主机应用程序的位匹配)通常可以提早绑定，但是尽管该库对COM可见，但它是有意的可以从托管(.net)代码中使用，因此您可以从接口访问这些对象-具体类型不会直接公开任何成员:

Adding a reference to mscorlib.tlb (you'll find it under C:\Windows\Microsoft.NET\Framework\v4.0.30319, or reference the equivalent from \Framework64 if your Excel is 64-bit - bitness of the library needs to match the bitness of the host application) would normally allow for early binding, but while this library is COM-visible, it's intended to be used from managed (.net) code, so you're accessing these objects from interfaces - the concrete types don't expose any members directly:

知道 Hashtable 实现了 IDictionary 接口，如果我们声明 IntelliSense > hashData作为IDictionary :

Knowing that Hashtable implements the IDictionary interface, we can use early binding and get compile-time validation and IntelliSense if we declare hashData As IDictionary:

Dim hashData As mscorlib.IDictionary
Set hashData = New mscorlib.Hashtable
hashData.Add "foo", 42
Debug.Print hashData.Item("foo") 'prints 42

请注意， Item 属性作为默认成员公开:

Note that the Item property is exposed as the default member:

这意味着您可以隐式地拥有 Item 成员调用，就像对任何标准VBA集合对象所做的一样:

This means you can have the Item member call implicit, exactly as you could do with any standard VBA collection object:

Dim hashData As mscorlib.IDictionary
Set hashData = New mscorlib.Hashtable
hashData.Add "foo", 42
Debug.Print hashData("foo") 'prints 42

早期绑定的代码更容易编写，尤其是当您不熟悉所涉及的类型时.但是，如果项目引用的是64位框架，并且您的宏需要在32位Excel上运行，则您将希望坚持后期绑定以避免绑定问题.

Early-bound code is much easier to write, especially when you're not familiar with the types involved. However if the project is referencing the 64-bit framework and your macros need to run on 32-bit Excel, you'll want to stick to late binding to avoid binding issues.

还请注意，使用 For Each 循环迭代 Hashtable 对象将不起作用，因为枚举器在VBA中的工作方式与在.NET中的工作方式有关; Keys 和 Values 集合是实现 ICollection 接口的对象，因此对其进行迭代也将是不平凡的:循环将不起作用，尽管您可以设置 For i = 0到hashData.Keys.Count-1 ，但是无法在索引 i处获得该项目来自 ICollection .

Also note, iterating the Hashtable object with a For Each loop isn't going to work, because of how enumerators work in VBA vs how they work in .NET; the Keys and Values collections are objects implementing the ICollection interface, so iterating them will be non-trivial as well: a For Each loop won't work, and while you can set up a For i = 0 To hashData.Keys.Count - 1, you can't get the item at index i from an ICollection.

但是我们知道 ICollection 继承了 IEnumerable ，并且 IEnumerable 确实与 For Each ，因此我们可以将 Keys 集合 cast 到 IEnumerable ，并像这样迭代所有键和值:

But we know that ICollection inherits IEnumerable, and IEnumerable does work with For Each, so we can cast the Keys collection to IEnumerable, and iterate all keys and values like so:

Dim hashData As mscorlib.IDictionary
Set hashData = obj.getHashData

Dim hashKeys As mscorlib.IEnumerable
Set hashKeys = hashData.Keys

Dim k As Variant
For Each k In hashKeys
    Debug.Print k, hashData(k) 'outputs the key and its associated value
Next

问题在于，您无法使用后期绑定代码或没有引用 mscorlib.tlb 来 cast 到 IEnumerable .后期绑定以某种方式看不到 GetEnumerator 成员，因此会引发错误438:

The problem is that you can't cast to IEnumerable with late-bound code or without a reference to mscorlib.tlb, and late binding somehow won't see the GetEnumerator member, so this raises error 438:

Dim hashKeys As Object
Set hashKeys = hashData.Keys

Dim k As Variant
For Each k In hashKeys ' error 438, hashKeys isn't exposing the enumerator
    Debug.Print k, hashData(k) 
Next

结论:如果需要VBA代码在32位和64位主机上运行，​​则必须跳过循环才能使后期绑定的代码起作用.如果您使用的是64位主机，我建议您尽早使用64位框架，并为该32位主机分发一个单独的宏副本，该副本引用32位框架.分发有些麻烦，但要比使后期绑定的代码正常工作要容易得多.

Conclusion: if you need the VBA code to run on both 32 and 64 bit hosts, you'll have to jump through hoops to get late-bound code to work. I would recommend working early-bound with the 64-bit framework if you're on a 64-bit host, and distributing a separate copy of the macro that references the 32-bit framework for 32-bit hosts. A bit of a pain to distribute, but less painful than getting late bound code to work.

这篇关于在VBA中使用.NET HashTable返回类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！