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
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 REM 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 REM This is the Do While statement is a comment. REM stands for remember.
Note: If you run a program and your computer becomes unresponsive, it is because you are stuck in an endless loop. One way to end the loop is to press Ctrl-Alt-q. Another way to fix your computer is to press Ctrl-Alt-Delete, which opens up the Task Manager with a list of all processes. The process you are looking for will be using about 50 to 60 of percent of CPU. Click on the process to highlight it. Right click and select Terminate from the drop down box.
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]
Loop
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 REM 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-WS Functions
Ch15-Calc Help Page
