迭代集合 (VBA)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24962346/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Iterating over a collection (VBA)
提问by user2521067
Hello I am new to VBA and I am trying to iterate over a collection i've made and execute an action for each value.
您好，我是 VBA 的新手，我正在尝试遍历我创建的集合并为每个值执行一个操作。
Here is my code:
这是我的代码：
Sub makeRowsMatch()
Dim rows As VBA.Collection
Set rows = New VBA.Collection
Dim i As Integer
Dim j As Integer
Dim y As Integer
For i = 2 To 22203
For j = 2 To 121
If Cells(i, 2).Value <> Cells(j, 38) Then
rows.Add (i)
End If
Next j
Next i
For Each y As Object in rows
rows(y).Delete
Next y
End Sub
I keep on getting an error in the line:
我不断收到错误消息：
For Each y As Object in rows
It keeps on getting highlighted yellow, but when I remove the above line is get the following error:
它一直以黄色突出显示，但是当我删除上面的行时出现以下错误：
For Each control variable must be Variant or Object
Could someone explain why it is not letting me iterate over the values? The whole issue of declaring variables is new to me. Thanks!
有人可以解释为什么它不让我迭代这些值吗？声明变量的整个问题对我来说都是新的。谢谢！
回答by djikay
Declare yas Variant, i.e.:
声明y为Variant，即：
Dim y As Variant
and change your loop like this:
并像这样改变你的循环：
For Each y In rows
Then the loop will work or, at least, it won't give you an error at thatpoint.
然后循环会工作，或者至少，它不会给你一个错误该点。
But the next problem/error is inside the loop. This line:
但下一个问题/错误在循环内部。这一行：
rows(y).Delete
will give you an error "Object required", or something like that, because rows(y)return an Integer, so you can't call Deleteon it. There's some confusion here because rowson its own means the Rowsrange of the active sheet. But you also named your collection as rows, so there's a naming conflict.
会给你一个错误“需要对象”，或者类似的东西，因为rows(y)返回一个整数，所以你不能调用Delete它。这里有一些混乱，因为rows它本身意味着Rows活动表的范围。但是您也将您的集合命名为rows，因此存在命名冲突。
Rename your rowsto, for example, myrowsand it should then all work. However, I don't know what you're trying to do with the Deleteline, so I can't advise further.
rows例如，将您的名称重命名为，myrows它应该可以正常工作。但是，我不知道你想用这Delete条线做什么，所以我不能提供进一步的建议。
回答by Ken White
You've got a conflict in your declarations and use. This line declares yas an Integer, clearly:
您的声明和使用存在冲突。这一行声明y为一个Integer，清楚地：
Dim y As Integer
But you then try to use it as an Object, either with
但是你然后尝试将它用作Object, 或者
For Each y As Object in rows
or
或者
For Each y in rows
Change the declaration to
将声明更改为
Dim y As Object
(I'm not that familiar with VBA. If the above doesn't work, change the declaration to Dim y As Variantinstead.)
（我对 VBA 不太熟悉。如果上述方法不起作用，请将声明Dim y As Variant改为。）
