Skip to content

Loops

A loop runs the same statements more than once. VBScript offers several, and they differ in what they test and when they test it.

A flowchart of a loop's condition. Flow reaches the condition; if true it runs the conditional code and returns to the condition; if false it leaves the loop

This is not the dataset's own iteration

IMan already runs a field expression once per record. This page is about looping within a single evaluation, over an array or a collection you built yourself. The dataset's own iteration is the Data Processing Pattern.

Most expressions need no loop at all.

Choosing a loop

Loop Runs Tests
For A counted number of times Before each pass
For Each Once per element of an array Before each pass
While While the condition is True Before each pass
Do While While the condition is True Before or after, your choice
Do Until Until the condition becomes True Before or after, your choice

A loop that tests after the pass always runs at least once. Exit For and Exit Do leave a loop early.

For

For counts from one number to another. Step sets the size of the increment, and defaults to 1.

For counter = start To end [Step increment]
  [statements]
Next

A flowchart of For..Next. For leads to the condition; if true, the code block runs and Next returns flow to the condition; if false, flow leaves the loop

The counter takes the start value, and the loop runs while the counter has not passed the end value. Next adds the increment, and the loop tests the counter again.

Totalling a counted range

Dim i
Dim Total

Total = 0

For i = 1 To 5
  Total = Total + i
Next

Total

The expression returns 15.

Counting in twos

Dim i
Dim Total

Total = 0

For i = 0 To 10 Step 2
  Total = Total + i
Next

Total

i takes 0, 2, 4, 6, 8 and 10, and the expression returns 30.

A negative Step counts down. For i = 10 To 1 Step -1 runs ten times.

For Each

For Each runs once for every element of an array, without a counter.

For Each element In group
  [statements]
Next

This is the loop you will use most often, because Split turns a delimited field into an array and For Each walks it.

Prefixing every code in a delimited field

Dim Codes
Dim Code
Dim List

Codes = Split(%WarehouseList, ",")
List = ""

For Each Code In Codes
  If List <> "" Then List = List & ","
  List = List & "WH-" & Code
Next

List

With %WarehouseList holding MAIN,NORTH,RETURNS, the expression returns WH-MAIN,WH-NORTH,WH-RETURNS. The If puts a comma before every code except the first. An empty field gives Split no elements, and the expression returns an empty string.

For Each needs an array

For Each over a string or a number raises error 451, "Object not a collection", rather than running no passes. Where the value may not be an array, test it with IsArray first.

While

While tests its condition before each pass and runs until the condition is False. Wend ends the loop.

While condition
  [statements]
Wend

A flowchart of While..Wend. The condition is tested first; if true the code block runs and flow returns to the condition; if false the loop is skipped. A false condition on entry means the block never runs

A condition that is False on entry means the statements never run at all.

Totalling until a limit

Dim Counter
Dim Total

Counter = 10
Total = 0

While Counter < 15
  Counter = Counter + 1
  Total = Total + Counter
Wend

Total

Counter takes 11, 12, 13, 14 and 15, and the expression returns 65.

Do While

Do While runs while its condition is True. The condition goes at the top or at the bottom, and the position changes the behaviour.

Testing first

Do While condition
  [statements]
Loop

A flowchart of Do While..Loop. Do While leads to the condition; if true the statement runs and flow returns to the condition; if false flow leaves the loop. The test comes before the statement

The condition is True on entry

Dim i
Dim Total

i = 0
Total = 0

Do While i < 5
  i = i + 1
  Total = Total + i
Loop

Total

The expression returns 15.

Testing last

Do
  [statements]
Loop While condition

A flowchart of Do..Loop While. Do leads straight to the statement, and only then to the condition; if true flow returns to the statement, if false it leaves. The statement always runs at least once

The statements always run once, whatever the condition says, because nothing has tested it yet.

The condition is False on entry

Dim i
Dim Total

i = 10
Total = 0

Do
  i = i + 1
  Total = Total + i
Loop While i < 5

Total

i is already 10 and the condition fails at the first test. The pass has already run by then, and the expression returns 11.

Do Until

Do Until inverts the sense of Do While. It runs until its condition becomes True.

Testing first

Do Until condition
  [statements]
Loop

A flowchart of Do Until..Loop. The condition is tested first, and the sense is inverted: if false the statement runs and flow returns to the condition; if true flow leaves the loop

Running until the counter passes a limit

Dim i
Dim Total

i = 10
Total = 0

Do Until i > 15
  i = i + 1
  Total = Total + i
Loop

Total

The loop tests the condition before each pass, and each pass increments i before the next test sees it. i takes 11 through 16 and the expression returns 81.

Testing last

Do
  [statements]
Loop Until condition

A flowchart of Do..Loop Until. Do leads straight to the statement, then to the condition; if false flow returns to the statement, if true it leaves. The statement always runs at least once

The statements always run once here too.

The condition is already True

Dim i
Dim Total

i = 10
Total = 0

Do
  i = i + 1
  Total = Total + i
Loop Until i < 15

Total

The pass runs, i becomes 11, and 11 < 15 ends the loop. The expression returns 11.

Leaving a loop early

Exit For and Exit Do stop a loop and carry on at the statement after it. Neither runs the rest of the current pass.

Exit For

Exit For

A flowchart of Exit For. The usual For, condition, code block and Next cycle, with a second branch out of the code block to Exit For, which joins the false path and leaves the loop without reaching Next

Stopping the count at four

Dim i
Dim Total

Total = 0

For i = 0 To 10 Step 2
  Total = Total + i
  If i = 4 Then
    Exit For
  End If
Next

Total

i takes 0, 2 and 4, and the expression returns 6.

Exit Do

Exit Do works in a Do While and in a Do Until alike.

Exit Do

A flowchart of Exit Do. The statement runs, then the condition; if true flow returns to the statement, if false it leaves. A second branch out of the statement reaches Exit Do and leaves the loop without testing the condition

Stopping before the condition would

Dim i
Dim Total

i = 0
Total = 0

Do While i < 100
  i = i + 1
  Total = Total + i
  If i = 5 Then
    Exit Do
  End If
Loop

Total

The condition would allow a hundred passes. Exit Do ends it after five, and the expression returns 15.

A loop that never ends fails the record after a minute

IMan stops an expression that runs for more than 60 seconds, and reports "The script was aborted because execution exceeded the specified timeout period". A While or a Do whose condition never becomes False reaches that limit. The run then treats it as any other runtime error. Change something inside the loop that the condition tests, and prefer For wherever the number of passes is known before the loop starts.