VBA 中的循环和退出循环

Glen Alfaro 2023年1月30日 2022年5月18日
  1. VBA For 循环
  2. VBA Do Until 循环
  3. VBA Do While 循环
  4. 在 VBA 中使用 Exit 命令强制停止循环
VBA 中的循环和退出循环

计算机编程中的循环非常重要。它们允许用最少的代码行执行重复性任务。取决于要执行的任务,计算机中对循环的需求有几个原因。

尽管它们非常强大,但它们在上下文和语法上都很简单。

在 VBA 中,可以使用三个循环。

  1. For 循环 - 在预定义或固定限制内循环
  2. Do While 循环 - 在条件为 True 时循环。
  3. Do Until 循环 - 循环直到条件为 True

VBA For 循环

语法:

For [ counter ] = [start] To [end]  Step [ step ]
    [ statements ]
    [ Exit For ]
Next [ counter ]

参数:

[ counter ] 必需的。通常是一个整数或长变量,用于保存计数器的当前值
[ start ] 必需的。计数器的起始值
[end] 必需的。计数器的最终值
[ statements ] 在循环内执行的代码块
[ exit ] 可选的。强制循环停止并退出循环代码块
[ step ] 可选的。每次迭代计数器的增量。如果未声明,则值为 1

For 循环示例:

该代码块将接受一个整数值作为其起始编号,然后使用 For 循环将该数字减小到零。它将打印每次迭代的结果。

Sub ReduceToZero(numStart As Integer)

Dim i As Integer

For i = numStart To 0 Step -1


    Debug.Print "The number is now " & i
    If i = 0 Then

        Debug.Print ("Done!")

    End If

Next i

End Sub

Sub testSub()

Call ReduceToZero(10)

End Sub

输出:

The number is now 10
The number is now 9
The number is now 8
The number is now 7
The number is now 6
The number is now 5
The number is now 4
The number is now 3
The number is now 2
The number is now 1
The number is now 0
Done!

VBA Do Until 循环

语法:

Do Until [condition]
    [statements]
    [Exit Do]
Loop

参数:

[condition] 必需的。一旦为真将退出代码块的条件
[statements] 必需的。要执行的代码块
[Exit Do] 可选的。强制循环停止并退出代码块

Do Until 循环示例:

该代码块将接受一个整数值作为其起始编号,然后使用 Do Until 循环将数字增加到 10。它将打印每次迭代的结果。

Sub IncrementToTen(numStart As Integer)

Dim i As Integer

i = numStart

Do Until i = 10 + 1

    Debug.Print "The number is now " & i
    i = i + 1
Loop

End Sub

Sub testSub()

Call IncrementToTen(-5)

End Sub

testSub 输出:

The number is now -5
The number is now -4
The number is now -3
The number is now -2
The number is now -1
The number is now 0
The number is now 1
The number is now 2
The number is now 3
The number is now 4
The number is now 5
The number is now 6
The number is now 7
The number is now 8
The number is now 9
The number is now 10
Done!

VBA Do While 循环

语法:

Do while [condition]
    [statements]
    [Exit Do]
Loop

参数:

[condition] 必需的。执行代码块需要为真的条件。
[statements] 必需的。要执行的代码块
[Exit Do] 可选的。强制循环停止并退出代码块

Do While 循环示例:

该代码块将接受一个整数值作为其起始编号,然后使用 Do While 循环将该数字增加到 10。它将打印每次迭代的结果。

Sub IncrementToTen(numStart As Integer)

Dim i As Integer
i = numStart
Do While i < 10 + 1

Debug.Print "The number is now " & i
i = i + 1
Loop
Debug.Print "Done!"
End Sub

Sub testSub()

Call IncrementToTen(-9)

End Sub

testSub 输出:

The number is now -9
The number is now -8
The number is now -7
The number is now -6
The number is now -5
The number is now -4
The number is now -3
The number is now -2
The number is now -1
The number is now 0
The number is now 1
The number is now 2
The number is now 3
The number is now 4
The number is now 5
The number is now 6
The number is now 7
The number is now 8
The number is now 9
The number is now 10
Done!

在 VBA 中使用 Exit 命令强制停止循环

在处理循环时,它们与循环在完成时将继续执行的条件绑定。然而,一个常见的场景是我们需要退出循环,即使条件仍然满足。一个典型的应用是在处理错误处理方法时,搜索时停止技术。需要立即退出循环以防止进一步的错误或节省执行时间。

下面的代码块将演示使用 Exit 命令强制循环在 Do 循环中停止。

此代码块旨在在两个数字的商为 1 时停止循环。这两个数字是在子程序的参数 upperboundlowerbound 整数值之间随机生成的。如果生成的除数 (divis) 为零,则会发生错误,因为不允许除以零。为避免这种情况发生,我们将使用 Exit 命令终止循环。

Sub StopWhenQuotientIsOne(upperbound As Integer, lowerbound As Integer)

Dim divid As Integer
Dim divis As Integer
Dim isOne As Boolean

divid = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
divis = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Do Until isOne = True

    If divis = 0 Then
        Debug.Print "Illegal divisor. Exiting the Loop"
        Exit Do
    End If

Debug.Print divid / divis

If divid / divis = 1 Then

    isOne = True
    Debug.Print "Done! One is achieved."

End If

divid = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
divis = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Loop


End Sub

Sub testSub()

Call StopWhenQuotientIsOne(4, -4)

End Sub

testSub 输出(第一次试):

-4 
-0 
-0.666666666666667 
Illegal divisor. Exiting the Loop

testSub 输出(第四次试):

-2 
 0 
-3 
 1 
Done! One is achieved.

下面的代码块将演示使用 Exit 命令强制循环在 For 循环中停止。

在这个例子中,StopWhenNegative 子程序需要两个参数。首先是 startNum,然后是 EndNum。循环将从 startNum 迭代到 EndNum。如果计数器变为负数,则循环将由 Exit 命令终止。

Sub StopWhenNegative(startNum As Integer, EndNum As Integer)

Dim i As Integer
 
For i = startNum To EndNum Step -1
 
    If i < 0 Then
        Debug.Print "Opps, negative values detected. Exiting loop."
        Exit For
    End If
Debug.Print "The current number is :" & i
Next i

End Sub

Sub testSub()

Call StopWhenNegative(10, -5)

End Sub

testSub 输出:

The current number is :10
The current number is :9
The current number is :8
The current number is :7
The current number is :6
The current number is :5
The current number is :4
The current number is :3
The current number is :2
The current number is :1
The current number is :0
Opps, negative values detected. Exiting loop.