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.