如何在vba集合中获取项目

Question

我是VBA的新手，当我试图从集合中获取项目时，我遇到了一个问题。

我有一个自定义类a，我有一个集合B=[A1,A2...]通过使用

B.add A

然后我有一个像C={1:B1, 2:B2...}这样的字典通过使用

C.add i, Bi

现在我想获得C(i)(j)，我构建代码如下，但它一直给我错误:对象不支持此属性或方法。

dim levels as variant
levels = C.items
dim level as variant
dim newA as A
for i = 0 to levels.count -1
level = levels(i)
for j = 0 to level.count -1
newA = level(j)
next
next

当我尝试将集合和类分配给变量时发生错误，即level = levels(I)和newA = level(j)

我知道我可以使用for each来构建循环，但我需要使用索引和对象本身(可能需要稍后修改集合内的对象)，所以想知道最好的方法是什么。谢谢!

Answer 1

这里有一个适合我的例子。
类A只有一个字段Public id As String

Sub Tester()
Dim C As Object, items, i As Long, objA As A

Set C = CreateObject("scripting.dictionary")

'populate dictionary with a couple of collections of Class A instances
C.Add 1, New Collection
C(1).Add GetAInstance("Id001")
C(1).Add GetAInstance("Id002")

C.Add 2, New Collection
C(2).Add GetAInstance("Id003")
C(2).Add GetAInstance("Id004")
C(2).Add GetAInstance("Id005")

'looping...
items = C.items
For i = LBound(items) To UBound(items)
For Each objA In items(i)
Debug.Print objA.id
Next objA
Next i

'direct access
Debug.Print C(1)(1).id '> "Id001"
Debug.Print C(2)(3).id '> "Id005"

C(2)(3).id = "New id"
Debug.Print C(2)(3).id '> "New id"
Set objA = C(2)(3) 'Set is required for object-type variables
Debug.Print objA.id '> "New id"
End Sub
'function to return an object of class A with supplied id
Function GetAInstance(idValue)
Dim rv As New A
rv.id = idValue
Set GetAInstance = rv
End Function