VBA Brush Up 06：Repeating Actions in VBA

Technorati 標籤: VBA,loop,for,while,do

來自：Julitta Korol，“Access.2003.Programming.by.Example.with.VBA.XML.and.ASP”，by Wordware Publishing, Inc. 2005, p91-p101

（1）Using the Do…While Loop

1. Do While condition 
2.     statement1 
3.     statement2 
4.     statementN 
5. Loop

（2）continuously display an input box until the user enters the correct password

1. Do While pWord <> "DADA" 
2.     pWord = InputBox("What is the Report password?") 
3. Loop

（3）Another Approach to the Do…While Loop

1. Do 
2.     statement1 
3.     statement2 
4.     statementN 
5. Loop While condition

When you test the condition at the bottom of the loop, the statements inside the loop are executed at least once.

（4）Infinite Loops：

1. Sub SayHello() 
2.     Do 
3.         MsgBox "Hello." 
4.     Loop 
5. End Sub

To stop the execution of the infinite loop, you must press Ctrl+Break. When Visual Basic displays the message box “Code execution has been interrupted,”click End to end the procedure.

（5）Do…Until repeats a block of code as long as something is false.

1. Do Until condition 
2.     statement1 
3.     statement2 
4.     statementN 
5. Loop

（6）

1. Do 
2.     statement1 
3.     statement2 
4.     statementN 
5. Loop Until condition

If you want the statements to execute at least once, no matter what the value of the condition, place the condition on the line with the Loop statement.

（7）

1. For counter = start To end [Step increment] 
2.     statement1 
3.     statement2 
4.     statementN 
5. Next [counter]

（8）

1. For Each element In Group 
2.     statement1 
3.     statement2 
4.     statementN 
5. Next [element]

Element is a variable to which all the elements of an array or collection will be assigned. This variable has to be of the Variant data type for an array and of the Object data type for a collection. Group is a name of a collection or an array.

（9）Exiting Loops Early: Exit For, Exit Do, Exit Sub, Exit Function.

（10）When writing nested loops, you must make sure that each inner loop is completely contained inside the outer loop. Also, each loop has to have a unique counter variable.