Loops allow you to repeat instructions as many times as you like.
The simplest loop is a For Next loop. It has the following general form. The items in brackets are optional depending upon your task.
For counter = start To end [Step stepvalue]
instructions
[Exit For]
[instructions]
Next [counter]
The following loop adds the numbers 1 through 5 and prints sum = 15.
Sub Test5()
Dim i As Long
Dim sum As Long
For i = 1 To 5
sum = sum + i
Next i
MsgBox "sum = " & sum
End Sub
Note: Notice the indentations of the code. This is optional but makes the code more readable.
The Do While loop can have two general forms. In the first form the loop may never be executed, whereas in the second form the loop instructions must be executed at least once.
Do While condition
instructions
[Exit Do]
[instructions]
Loop
VS
Do
instructions
[Exit Do]
[Instructions]
Loop While condition
The following Do While loop prints i = 0 and then prints sum = 6. Notice the sum is 6 instead of 5. This is because when Dim i As Long is declared, the variable i is initialized as 0.
Sub Test6()
Dim i As Long
Dim sum As Long
MsgBox "i = " & i
Do While i <= 5 'This is the Do While statement.
sum = sum + 1
i = i + 1
Loop
MsgBox "sum = " & sum
End Sub
Note: Notice the comment in the program. It is optional but helps others and you to remember what steps you took in creating the program. A comment is all text on a line after the single quote. In this case the ‘This is the Do While statement is a comment.
Note: If you run a program and your computer becomes unresponsive, it is because you are stuck in an endless loop. After about fifteen seconds a text Overflow box will appear. Click on End to continue. Another way to fix your computer is to press Ctrl-Alt-Delete, which opens up the Task Manager. In the Task Manager box, highlight Microsoft Visual Basic for Applications, and click on End Task. (See the screenshot below.)
Do Until loops are similar to Do While loops. The difference is that Do While loops execute while the loop condition is true. Do Until loops execute until the loop condition is true.
The Do Until loop can have two general forms. In the first form the loop may never be executed, whereas in the second form the loop instructions must be executed at least once.
Do Until condition
instructions
[Exit Do]
[instructions]
Loops
VS
Do
instructions
[Exit Do]
[instructions]
Loop Until condition
The following Do Until loop prints sum = 5.
Sub Test7()
Dim i As Long
Dim sum As Long
Do Until i = 5
sum = sum + 1
i = i + 1
Loop
MsgBox "sum = " & sum 'sum = 5
End Sub
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-Questions
