问题描述
我才发现，原来我可以创建VBA使用它可以创建COM类CreateObject方法的一些.NET类。这是pretty的冷静，但所创建的类是后期绑定的，所以你没有得到任何智能感知等。因此，我希望做的是编写VBA包装类和委托所有的方法调用到内部参考.NET对象
所以这非常适用于一个ArrayList对象的一切，但试图引用枚举。没有为VBA一个黑客，它允许你创建你自己的收藏和使用对于每个...语法来枚举的集合。下面是一个例子：
公共属性获取NewEnum（）作为的IUnknown
属性NewEnum.VB_UserMemId = -4
属性NewEnum.VB_MemberFlags =40
设置NewEnum =＆LT;＆LT;＆LT;引用传递给这里的枚举。
高端物业
大多数实现持有引用VBA Collection对象，并通过枚举Collection对象。然而，因为我拿着一个.net的ArrayList，我想传递出来。不过，我得到无效的过程调用或参数当我尝试。
我目前的尝试是这样的：
公共功能NewEnum（）作为的IUnknown
昏暗的枚举作为对象
设置枚举= internalList.GetEnumerator（）＆LT;＆LT;＆LT;＆LT;这里发生的错误。
设置NewEnum =枚举
端功能
我pretty的确保其可能使此工作，因为它能够直接地遍历该ArrayList收集未经包装。例如，
公用Sub TestCreateArrayList（）
昏暗的清单作为对象
设置列表=的CreateObject（System.Collections.ArrayList）
list.Add的项目。
list.Add其他项目。
昏暗的项目为Variant
每个项目在列表
Debug.Print项目
下一个
结束小组
我可以生活在没有对每一个功能，但它会很好，如果我能得到它的工作特别是当它看起来像它几乎没有。
编辑：它可能是值得一提的是实例化一个ArrayList，然后调用的GetEnumerator产生同样的错误，即使外面的包装类的。
进一步编辑：需要注意的是努力型的IUnknown的变量设置为GetEnumerator方法的结果仍然给出了同样的错误。
公用Sub TestCreateArrayList（）
昏暗的清单作为对象
设置列表=的CreateObject（System.Collections.ArrayList）
昏暗的迭代器作为IUnknown的
设置迭代= list.GetEnumerator（）＆其中;＆其中;＆其中;＆其中;这里发生的错误。
结束小组
最后编辑：由于下面我@注册表编辑器的意见能够得到这个工作，我想我会添加code：
私人internalList为对象
私人小组Class_Initialize（）
创建.NET数组列表中的内部数据存储。
设置internalList =的CreateObject（System.Collections.ArrayList）
结束小组
私人小组Class_Terminate（）
如果没有internalList是没有那么
在错误恢复下一页
internalList.Dispose
Err.Clear
结束如果
结束小组
公共功能NewEnum（）作为的IUnknown
属性NewEnum.VB_UserMemId = -4
昏暗的枚举作为的IUnknown
设置枚举= internalList.GetEnumerator（0，internalList.Count）
设置NewEnum =枚举
端功能
......其他包装方法省略
在使用
昏暗枚举作为对象
您声明COM对象。但是，COM实现了IEnumerator为 IEnumVARIANT ，它继承的IUnknown 直接，COM不乐意尝试设置的GetEnumerator 返回值到对象。相反，你可以使用
昏暗枚举作为IEnumVARIANT
编辑：请注意，虽然的ArrayList 工具的IEnumerable ，it重载的GetEnumerator 一个方法，它首页和计数。所以，你必须当你想调用不带参数的超负荷使用VBA中的没有关键字。
设置迭代器= list.GetEnumerator（没有，没有）
您可以看到，它的工作，因为如果你有你的原始行code表示添加两个项目到列表中，然后用电话，如
设置迭代器= 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.
这篇关于裹的.Net ArrayList的自定义VBA类GET迭代器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！