Excel VBA - 如何使用 For Each...Next 语句

Excel VBA - How to use the For Each...Next statement

提问by Dirk101

I'm currently learning VBA by doing, and I'm trying to understand the syntax of the For Each...Next statement. If the syntax is:

我目前正在边做边学 VBA，我正在尝试理解 For Each...Next 语句的语法。如果语法是：

For Each element In group
[statements]
[Exit For]
[statements]
Next [element]

What sort of variables can you use for "element" and "group", and do you define them? I ask because most of the examples I've found for the function don't define "element".

您可以将什么样的变量用于“元素”和“组”，您是否定义了它们？我问是因为我为该函数找到的大多数示例都没有定义“元素”。

Say I have a column full of "a", "b", "c", "d" and "N/A", and I want to determine the number of times each appear in the column.

假设我有一列充满“a”、“b”、“c”、“d”和“N/A”，我想确定每个列出现在该列中的次数。

So far I have the following:

到目前为止，我有以下几点：

Sub Count()

Dim lastRow, aCount, bCount, cCount, dCount, NACount As Long

lastRow = Range("A" & Rows.Count).End(xlUp).Row
aCount = 0
bCount = 0
cCount = 0
dCount = 0
NACount = 0

For Each MyCell In Range("A1:A" & lastRow)
    If MyCell.Value = "a" Then
        aCount = aCount + 1
    ElseIf MyCell.Value = "b" Then
        bCount = bCount + 1
    ElseIf MyCell.Value = "c" Then
        cCount = cCount + 1
    ElseIf MyCell.Value = "d" Then
        dCount = dCount + 1
    ElseIf MyCell.Value = "b" Then
        bCount = bCount + 1
    Else
        NACount = NACount + 1
    End If
Next

End Sub

This give a "Type mismatch" error for If MyCell.Value = "a" Then, though I already know that I must be using a wrong group or array.

If MyCell.Value = "a" Then尽管我已经知道我必须使用错误的组或数组，但这会给 一个“类型不匹配”错误。

Any help for such a beginner issue is greatly appreciated.

非常感谢对此类初学者问题的任何帮助。

回答by David Zemens

First things first:

第一件事：

Say I have a column full of "a", "b", "c", "d" and "N/A", and I want to determine the number of times each appear in the column.

假设我有一列充满“a”、“b”、“c”、“d”和“N/A”，我想确定每个列出现在该列中的次数。

You shouldjust use the CountIffunction :)

您应该只使用该CountIf功能:)

But as a learning exercise... Your code is raising an error most likely because of an "Error" value in the sheet/cell being counted.

但作为学习练习......您的代码很可能因为正在计算的工作表/单元格中的“错误”值而引发错误。

You could fix it like

你可以把它修好

If Cstr(MyCell.Value) = "a"...

Make sure to do this for the other conditionals, too, or you could do something like:

确保也对其他条件执行此操作，或者您可以执行以下操作：

For Each myCell In Range("A1:A" & lastRow)
Dim clVal As String
clVal = CStr(myCell)
    If clVal = "a" Then
        aCount = aCount + 1
    ElseIf clVal = "b" Then
        bCount = bCount + 1
    ElseIf clVal = "c" Then
        cCount = cCount + 1
    ElseIf clVal = "d" Then
        dCount = dCount + 1
    ElseIf clVal = "b" Then
        bCount = bCount + 1
    Else
        NACount = NACount + 1
    End If
Next

WHY

为什么

Because MyCellis undeclared, it can be an error type. You can't do a string comparison of an error and a string, so that's raising the Type Mismatch error. You can use the CStrfunction to cast MyCell.Valueas a string and that can avoid the error.

因为MyCell是未声明的，所以它可能是一个错误类型。您不能对错误和字符串进行字符串比较，因此会引发类型不匹配错误。您可以使用该CStr函数将MyCell.Value其转换为字符串，这样可以避免错误。

A note on declaring your variables...

关于声明变量的说明...

You should declare ALL of your variables and type them appropriately. VBA does not support multiple implicit inline declarations like:

您应该声明所有变量并适当地键入它们。VBA 不支持多个隐式内联声明，例如：

Dim lastRow, aCount, bCount, cCount, dCount, NACount As Long

Dim lastRow, aCount, bCount, cCount, dCount, NACount As Long

This is functionaly equivalent to:

这在功能上等同于：

Dim lastRow   'As Variant
Dim aCount    'As Variant
Dim bCount    'As Variant
Dim cCount    'As Variant
Dim dCount    'As Variant
Dim naCount As Long

Which is probably not what you were expecting. Instead, do:

这可能不是您所期望的。相反，请执行以下操作：

Dim lastRow as Long, aCount as Long, bCount as Long, _ cCount as Long, dCount as Long, NACount As Long

Dim lastRow as Long, aCount as Long, bCount as Long, _ cCount as Long, dCount as Long, NACount As Long

You should also declare ALL of your variables, always, including your loop iterators, like MyCell. One way to enforce this is to always put Option Explicitat the top of your module. This forces variable declaration, and as such, raises a compile error if you have misspelled variable name somewhere, etc. (this is super-common, actually, so always use Option Explicit!, it will save you a lot of hair-pulling).

您还应该始终声明所有变量，包括循环迭代器，例如MyCell. 强制执行此操作的一种方法是始终放在Option Explicit模块的顶部。这会强制变量声明，因此，如果您在某处拼错了变量名等，则会引发编译错误（这是非常常见的，实际上，因此始终使用 Option Explicit！，它将为您节省很多麻烦） .

Loop iterators can be of type Variantor they must match a type that is iterable in the collection/array, e.g.:

循环迭代器可以是类型，Variant或者它们必须匹配集合/数组中可迭代的类型，例如：

Dim ws as Worksheet
For each ws in ThisWorkbook.Worksheets
    Debug.Print ws.Name
Next

Or they can be a long/integer for an indexed collection

或者它们可以是索引集合的长/整数

Dim w as Integer
For w = 1 to ThisWorkbook.Worksheets.Count
    Debug.Print w
Next

Also, review the documentation:

另外，查看文档：

http://msdn.microsoft.com/en-us/library/office/gg264596(v=office.15).aspx

http://msdn.microsoft.com/en-us/library/office/gg264596(v=office.15).aspx

Ultimately what method you use to iterate depends on what you're trying to accomplish and the structures of the data you're working with.

最终，您使用哪种方法进行迭代取决于您要完成的任务以及您正在使用的数据的结构。