在VBA中创建一个可循环的容器类 [英] Create a loopable container class in VBA

问题描述

我一直在尝试清理代码，使其与Excel对象模型更加相似，并且我想知道是否有可能在VBA中创建可循环"容器类，例如类似于您的操作方式:

I have been trying to clean up my code a bit and make it more similar to the Excel object model, and I was wondering if it is possible to create a "loopable" container class in VBA, e.g. similar to how you can do:

Dim Sheet As Worksheet
For Each Sheet In ThisWorkbook.Worksheets
   ' ...
Next Sheet

我想为我自己的容器使用此功能.

I want this functionality for my own container.

假设我创建了自己的名为Container的类，其中包含某些类ItemType的项目(在此示例中，这可以是空类):

Say I create my own class called Container which contains items of some class ItemType (this can simply be an empty class for this example):

' Class Container
' The container contains items of a class I will call ItemType

Private Type MContainer
  Items As Collection ' Could also be implemented in terms of an array
End Type

Private This As MContainer

Public Property Get Item(ByVal Index As Long) As ItemType
Attribute Item.VB_UserMemId = 0 'Makes it so I can access elements like a Collection
  Set Item = This.Items(Index)
End Property

Public Function Add() As ItemType
  This.Items.Add
  Set Add = This.Items(This.Items.Count)
End Function

Private Sub Class_Initialize()
  Set This.Items = New Collection
End Sub

然后我想使用For Each...遍历容器中的项目，但这不起作用.请参阅以下示例，了解我理想情况下的工作方式:

I then want to loop through the items in my container with the For Each..., but this doesn't work. See the following example for how I ideally want it to work:

Public Sub MyMethod()

  Dim Stuff As New Container
  Stuff.Add

  Dim Element As ItemType
  For Each Element In Stuff ' <- This will not work
    ' Do something
  Next Element

End Sub

最后一个For循环是我要进行的工作.这可能吗?基本上，问题是我无法在我的Container类上调用For Each，类似于使用例如Excel.Sheets类.这可以在VBA中实现吗?

The final For loop is what I am looking at making work. Is this possible? Basically the issue is that I can't call For Each on my Container class similar to how you can with e.g. the Excel.Sheets class. Is this possible to achieve in VBA?

推荐答案

将此添加到您的班级

Public Function NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
    Set NewEnum = Items .[_NewEnum]
End Function

这篇关于在VBA中创建一个可循环的容器类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！