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

问题描述

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

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

公共属性 Get NewEnum() As IUnknown属性 NewEnum.VB_UserMemId = -4属性 NewEnum.VB_MemberFlags = "40"设置 NewEnum = <<<在此处传递对枚举器的引用.最终财产

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

我目前的尝试是这样的:

公共函数 NewEnum() As IUnknownDim enumerator 作为对象Set enumerator = internalList.GetEnumerator() <<<<<<这里发生错误.设置 NewEnum = 枚举器结束函数

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

公共子测试CreateArrayList()变暗列表作为对象Set list = CreateObject("System.Collections.ArrayList")列表.添加一个项目".列表.添加另一个项目".将项目变暗为变体对于列表中的每个项目调试.打印项目下一个结束子

没有 For Each 功能我也能活下去，但如果我能让它工作就好了，尤其是当它看起来几乎就在那里的时候.

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

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

公共子测试CreateArrayList()变暗列表作为对象Set list = CreateObject("System.Collections.ArrayList")Dim iterator As IUnknown设置迭代器 = list.GetEnumerator() <<<<<<这里发生错误.结束子

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

私有内部列表作为对象私有子类_Initialize()' 为内部数据存储创建一个 .Net Array 列表.Set internalList = CreateObject("System.Collections.ArrayList")结束子私有子类_Terminate()如果不是 internalList Is Nothing Then出错时继续下一步内部列表.处置错误清除万一结束子公共函数 NewEnum() 作为 IUnknown属性 NewEnum.VB_UserMemId = -4Dim enumerator As IUnknownSet enumerator = internalList.GetEnumerator(0, internalList.Count)设置 NewEnum = 枚举器结束函数' ...省略了其他包装方法

解决方案

使用时

 Dim enumerator As Object

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

Dim enumerator As IEnumVARIANT

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

Set iterator = list.GetEnumerator(Nothing, Nothing)

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

之类的调用

Set iterator = list.GetEnumerator(1, 3) ' 3 越界

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

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.

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

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

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

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.

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

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 

Final Edit: 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

解决方案

When you use

 Dim enumerator As Object

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

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