当需要多次执行一段代码时,就可以使用循环语句。 一般来说,语句是按顺序执行的:函数中的第一个语句首先执行,然后是第二个,依此类推。
编程语言提供了各种控制结构,允许更复杂的执行路径。
循环语句允许多次执行语句或语句组。 以下是VBA中循环语句的一般形式。
一、循环结构
1、for循环
Private Sub Constant_demo_Click()
Dim a As Integer
a = 10
For i = 0 To a Step 2
MsgBox ("The value is i is : " & i)
Next
End Sub
2、for each循环
For Each
循环用于为数组或集合中的每个元素执行语句或一组语句。For Each
循环与For
循环类似; 然而,For Each
循环是为数组或组中的每个元素执行的。 因此,这种类型的循环中将不存在步计数器。 它主要用于数组或在文件系统对象的上下文中使用,以便递归操作。
语法:
For Each element In Group
[statement 1]
[statement 2]
....
[statement n]
[Exit For]
[statement 11]
[statement 22]
Next
示例:
Private Sub Constant_demo_Click()
'fruits is an array
fruits = Array("Apple", "Orange", "Cherry")
Dim fruitnames As Variant
'iterating using For each loop.
For Each Item In fruits
fruitnames = fruitnames & Item & Chr(10)
Next
MsgBox fruitnames
End Sub
3、While Wend循环
在While...Wend
循环中,如果条件为True
,则会执行所有语句,直到遇到Wend
关键字。
如果条件为false
,则退出循环,然后控件跳转到Wend
关键字后面的下一个语句。
Private Sub Constant_demo_Click()
Dim Counter : Counter = 10
While Counter < 15 ' Test value of Counter.
Counter = Counter + 1 ' Increment Counter.
msgbox "The Current Value of the Counter is : " & Counter
Wend ' While loop exits if Counter Value becomes 15.
End Sub
结果:
The Current Value of the Counter is : 11
The Current Value of the Counter is : 12
The Current Value of the Counter is : 13
The Current Value of the Counter is : 14
The Current Value of the Counter is : 15
4、do...while循环
Private Sub Constant_demo_Click()
Do While i < 5
i = i + 1
msgbox "The value of i is : " & i
Loop
End Sub
结果:
The value of i is : 1
The value of i is : 2
The value of i is : 3
The value of i is : 4
The value of i is : 5
#另外一种do while 语句 ,直到while语句值为false,循环终止。
Do
[statement 1]
[statement 2]
...
[statement n]
[Exit Do]
[statement 1]
[statement 2]
...
[statement n]
Loop While condition
示例:
Private Sub Constant_demo_Click()
i = 10
Do
i = i + 1
MsgBox "The value of i is : " & i
Loop While i < 13 'Condition is false.Hence loop is executed once.
End Sub
5、do until 语句
Private Sub do_until_loop()
Dim i As Integer
Dim s As Integer
s = 0
i = 1
Do Until i > 10
s = s + i
i = i + 1
'MsgBox ("i值是:" & i & " S值是:" & s)
Loop
MsgBox ("S值是:" & s)
End Sub
结果:
S=55
二、循环控制语句
1、当想要根据特定标准退出For
循环时,就可以使用Exit For
语句。当执行Exit For
时,控件会立即跳转到For
循环之后的下一个语句。
Private Sub Constant_demo_Click()
Dim a As Integer
a = 10
For i = 0 To a Step 2 'i is the counter variable and it is incremented by 2
MsgBox ("The value is i is : " & i)
If i = 4 Then
i = i * 10 'This is executed only if i=4
MsgBox ("The value is i is : " & i)
Exit For 'Exited when i=4
End If
Next
End Sub
结果如下,i分别等于0,2,4,等到i=4时,i=40,然后跳出循环。
2、Exit do 语句
当想要根据特定标准退出Do
循环时,可使用Exit Do
语句。 它可以同时用于Do...While
和Do...Until
直到循环。
当Exit Do
被执行时,控制器在Do
循环之后立即跳转到下一个语句。
Private Sub Constant_demo_Click()
i = 0
Do While i <= 100
If i > 10 Then
i=i+100
MsgBox ("The Value of i is : " & i)
Exit Do ' Loop Exits if i>10
End If
MsgBox ("The Value of i is : " & i)
i = i + 2
Loop
End Sub
结果如下: