用自定义 VBA 类包装 .Net ArrayList 获取迭代器 [英] Wrap .Net ArrayList with custom VBA class get iterator

问题描述

我刚刚发现我可以使用可以创建 COM 类的 CreateObject 方法从 VBA 创建一些 .Net 类.这很酷，但是创建的类是后期绑定的，所以你不会得到任何智能感知等.所以我希望做的是编写 VBA 包装类并将所有方法调用委托给 .Net 对象的内部引用.

I just discovered that I can create some .Net classes from VBA using the CreateObject method which can create COM classes. This is pretty cool but the created class is late bound so you don't get any intellisense etc. So what I hoped to do was write VBA wrapper classes and delegate all the method calls to an internal reference to the .Net object.

所以这对于一个 ArrayList 对象来说很有效，除了试图引用枚举器之外.VBA 有一个 hack，它允许您创建自己的集合并使用 For Each ... 语法来枚举您的集合.下面是一个例子:

So this works well for an ArrayList object for everything but trying to reference the enumerator. There is a hack for VBA which allows you to create your own collections and use the For Each ... syntax to enumerate your collections. The following is an example:

Public Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"
    Set NewEnum = <<< pass a reference to the enumerator here.
End Property

大多数实现都持有对 VBA Collection 对象的引用，并为 Collection 对象传递枚举器.但是，由于我持有一个 .Net ArrayList，我想将其传递出去.但是，当我尝试时，我得到无效的过程调用或参数".

Most implementations hold a reference to a VBA Collection object and pass the enumerator for the Collection object. However since I am holding a .Net ArrayList I'd like to pass that out. However I get "Invalid procedure call or argument" when I try.

我目前的尝试是这样的:

My current attempt is this:

Public Function NewEnum() As IUnknown
    Dim enumerator As Object
    Set enumerator = internalList.GetEnumerator()    <<<< Error occurs here.
    Set NewEnum = enumerator
End Function

我很确定它可以完成这项工作，因为可以在没有包装器的情况下直接迭代 ArrayList 集合.例如

I'm pretty sure that its possible to make this work because it is possible to iterate the ArrayList collection directly without the wrapper. E.g.

Public Sub TestCreateArrayList()
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")

    list.Add "an item."
    list.Add "another item."

    Dim Item As Variant
    For Each Item In list
        Debug.Print Item
    Next
End Sub

我可以在没有 For Each 功能的情况下生活，但如果我能让它工作，尤其是当它看起来快要完成的时候，那就太好了.

I can live without the For Each functionality but it would be nice if I could get it to work especially when it seems like its almost there.

可能值得一提的是，实例化 ArrayList 然后调用 GetEnumerator 会产生相同的错误，即使在包装类之外也是如此.

Its probably worth mentioning that instantiating an ArrayList and then calling GetEnumerator yields the same error even outside of a wrapper class.

进一步请注意，尝试将 IUnknown 类型的变量设置为 GetEnumerator 方法的结果仍然会出现相同的错误.

Further edit: Note that trying to set a variable of type IUnknown to the result of the GetEnumerator method still gives the same error.

Public Sub TestCreateArrayList()
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")

    Dim iterator As IUnknown
    Set iterator = list.GetEnumerator()   <<<< error occurs here.
End Sub 

最终感谢@RegEdit 在下面的评论，我能够让它工作并认为我会添加代码:

Final Thanks to @RegEdit's comments below I was able to get this to work and thought I'd add the code:

Private internalList As Object

Private Sub Class_Initialize()
    ' create a .Net Array list for the internal data store.
    Set internalList = CreateObject("System.Collections.ArrayList")
End Sub

Private Sub Class_Terminate()
    If Not internalList Is Nothing Then
        On Error Resume Next
        internalList.Dispose
        Err.Clear
    End If
End Sub

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
        Dim enumerator As IUnknown
        Set enumerator = internalList.GetEnumerator(0, internalList.Count)
        Set NewEnum = enumerator
End Function

' ... other wrapped methods elided

推荐答案

使用时

 Dim enumerator As Object

您正在声明一个 COM 对象.但是COM将IEnumerator实现为IEnumVARIANT，它直接继承了IUnknown，COM不乐意尝试将GetEnumerator返回值设置成对象.相反，您可以使用

you are declaring a COM object. But COM implements IEnumerator as IEnumVARIANT, which inherits IUnknown directly, and COM is not happy trying to set the GetEnumerator return value into an Object. Instead, you can use

Dim enumerator As IEnumVARIANT

请注意，虽然 ArrayList 实现了 IEnumerable，但 它重载了 GetEnumerator 方法，该方法采用 index 和计数.所以当你想调用不带参数的重载时，你必须在 VBA 中使用 Nothing 关键字.

Note that although ArrayList implements IEnumerable, it overloads GetEnumerator with a method that takes index and count. So you have to use the Nothing keyword in VBA when you want to call the overload with no parameters.

Set iterator = list.GetEnumerator(Nothing, Nothing)

您可以看到它正在工作，因为如果您包含将两个项目添加到列表中的原始代码行，然后使用诸如

You can see that it's working, because if you include your original lines of code that add two items to the list, and then use a call such as

Set iterator = list.GetEnumerator(1, 3) ' 3 is out of bounds

您现在收到一个新的(预期的)错误，通知您偏移量/长度超出范围.

you now get a new (expected) error informing you that offset/length are out of bounds.

这篇关于用自定义 VBA 类包装 .Net ArrayList 获取迭代器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！