Loops with Excel VBA

In VBA, there is a wide variety of means for organizing loops, which can be divided into two main groups: loops with a condition (Do…Loop) and loops with enumeration (For…Next).

Loops of the Do…Loop type are used in cases where it is not known in advance how many times the block of statements forming the loop body should be executed. Such a loop continues running until a certain condition is met. There are four types of Do…Loop loops, which differ in the type of condition checked and the time when the check is performed. Table 2.10 shows the syntax of these four constructions.

Table 1. Syntax of Do…Loop statements

There are also two variations of the For…Next loop. Very often, when working with arrays, and in cases where it is necessary to repeat the execution of a group of statements a specified number of times, a For…Next loop with a counter is used. Unlike Do…Loop loops, this type of loop uses a special variable called a counter, whose value is increased or decreased by a given increment with each execution of the loop body. When the value of this variable reaches the specified limit, the loop execution ends.

The syntax of this loop looks as follows (elements in square brackets are optional):

For <counter> = <startValue> To <endValue>
    [Step <increment>]
    <statements>
Next [<counter>]

In this loop construction:

• <increment> can be either a positive or a negative number. If a negative increment is used, the end value must be less than or equal to the start value so that the loop body executes at least once.
• After the For…Next loop finishes, the variable used as the counter receives a value that necessarily exceeds the end value if the increment is positive, or is strictly less than the end value if the increment is negative.
• If the start and end values are the same, the loop body is executed only once.

Let us also consider another variation of the For…Next loop, often used in VBA when processing objects that make up an array or a collection of similar objects. In this variation, the counter is absent, and the loop body executes for each element of the array or collection. The syntax of such a loop is as follows:

For Each <element> In <collection>
    <statements>
Next [<element>]

Here, <element> is a variable used to refer to elements of the collection, and <collection> is the name of the array or collection.

Exiting Loops and Procedures

Normally, the execution of a procedure ends after its last statement has been executed, and the execution of a loop ends after several iterations of the loop body, when the termination condition is met. However, in some cases, it may be necessary to stop the execution of a procedure or loop prematurely, thus avoiding unnecessary procedure statements or redundant loop iterations.

For example, if an error occurs during the execution of a procedure that makes its continuation meaningless, you can execute a command for immediate exit from the procedure. Another example: if a For…Next loop is used to search for a required value in an array, then once the desired element has been found, there is no need to continue scanning the rest of the array elements. Premature exit from a control structure can be performed using one of the Exit statements.

• For premature exit from Do…Loop loops, the Exit Do statement is used.
• For exit from For loops, the Exit For statement is used.
• For premature exit from procedures and functions, the Exit Sub and Exit Function statements are used, respectively.

It should be noted that although the use of the Exit statement can be fully justified, excessive use of this operator should be avoided and it should only be applied in critical situations. Frequent use of this operator makes it more difficult to understand the written program code and to debug it.

Examples of Using Loop Statements

The For…Next Statement

The For…Next operator is used to find the sum of the elements in an array.

Summation of array elements

Sub DemoFor()
    Dim A As Variant
    A = Array(1, 4, 12, 23, 34, 3, 23)
    s = 0
    For i = LBound(A) To UBound(A)
        s = s + A(i)
    Next
    MsgBox s
End Sub

In the next example, the product of the first n natural numbers (the factorial of n) is calculated.

Finding the product of the first n natural numbers

Sub Factor()
    n = 20
    Fact = 1
    For i = 1 To n
        Fact = Fact * i
    Next i
    MsgBox Format(Fact, "############")
    ' Output: 2432902008176640000
End Sub

The For Each Statement
The For Each statement can be used to sum up the elements of an array. 

Summing the elements of an array using the For…Each statement

Sub DemoForEach()
    Dim A As Variant, s As Double
    A = Array(1, 4, 12, 23, 34, 3, 23)
    s = 0
    For Each b In A
        s = s + b
    Next
    MsgBox s
End Sub

The For Each statement can also be used when iterating through the cells of a range, in particular, when calculating the sum of the values entered in the cells of a range. 

Summing values from a range of cells using the For Each statement

Sub DemoSumSelection()
    Dim c As Range
    Dim s As Double
    s = 0
    For Each c In Selection.Cells
        s = s + c.Value
    Next
    MsgBox s
End Sub

In the next example, the For Each loop is used to change the color of cells: all cells in the range A1:C4 with positive values are colored blue, and those with non-positive values are colored red.

Changing the color of cells

Sub DemoChangeColor()
    Dim c As Range
    For Each c In Range("A1:C4").Cells
        With c
            If .Value <= 0 Then
                .Interior.ColorIndex = 3
            Else
                .Interior.ColorIndex = 5
            End If
        End With
    Next
End Sub

The For Each statement is also used when iterating through members of a collection. For example, the following code  demonstrates the use of the For Each loop for working with a collection of worksheets. The statement deletes the worksheet named Test from the workbook, if it exists.

Working with a collection of worksheets

Sub DemoDeleteSheet()
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name = "Тест" Then
            ws.Delete
        End If
    Next
End Sub

The While Statement

The While statement, as mentioned earlier, unlike For, does not repeat a specified number of times but instead continues as long as a condition is met.

In the following example, the rolling of a die is simulated until a six is rolled. When a six is rolled, the game ends, and a message is displayed indicating on which attempt it ended.

Rolling a die

Sub DemoWhile()
    Dim attempt As Integer
    Dim score As Integer
    Randomize
    score = Int(6 * Rnd()) + 1
    attempt = 1
    While score < 6
        attempt = attempt + 1
        score = Int(6 * Rnd()) + 1
    Wend
    MsgBox "Won on attempt: " & attempt
End Sub

Using the value True as the condition in a While statement creates an infinite loop. To break out of it, you must place an instruction inside the loop body that provides an exit when a certain condition is satisfied.

While True
    <block of statements>
Wend

The Do Statement

An example of using the Do…Until loop statement for repeats the loop until the word time is entered in the input box as a password.

Example of using the Do statement

Sub DemoPassword()
    Dim ps As String
    Do
        ps = InputBox("Enter password")
    Loop Until ps = "time"
End Sub

For the Do While loop statement, the following example is provided . It sequentially displays the names of all .jpg files in the root directory of drive D:. Here the file-handling function Dir() is used, which returns the name of the first matching file. If no matching file is found, the Dir() function returns an empty string. On subsequent calls to Dir(), its parameter with the search mask is omitted.

Sequentially displaying the names of files with the .jpg extension

Sub DemoShowFiles()
    Dim f As String
    Dim res As String
    f = Dir("D:\*.jpg")
    res = f & vbCr
    Do While Len(f)
        f = Dir
        res = res & f & vbCr
    Loop
    MsgBox res
End Sub

Here’s the full English translation of your passage, preserving the technical structure and VBA-specific terminology:

Alternative Exit from a Loop

An example of using the alternative exit Exit Do from a loop can be the following code, in which the entered numbers are summed. The loop ends if any string expression is entered.

Summing all entered numbers

Sub DemoExitDo()
    Dim s As Double, x As Variant
    s = 0
    Do
        x = InputBox("Enter a number")
        If Not IsNumeric(x) Then Exit Do
        s = s + x
    Loop
    MsgBox s
End Sub

Creating an Infinite Loop with the Do Statement

A Do statement without conditions creates an infinite loop. Such loops are used when programming various background processes such as document printing.

Do
Loop

The Unconditional GoTo Statement

The unconditional jump statement specifies a jump to a given line within a procedure. The required parameter line can be any line label or line number.

GoTo line

To use the unconditional jump statement, a label must be assigned to some line. A label must begin with a letter and end with a colon.

As an example of using the unconditional jump operator, consider a game in which the player is given ten attempts to roll a die. If a six is rolled on any of these attempts, the player wins, and the game ends.

Rolling a die

Sub DemoGoTo()
    Dim i As Integer
    Dim score As Integer
    Randomize
    For i = 1 To 10
        score = Int(6 * Rnd()) + 1
        If score = 6 Then GoTo lblMessage
    Next
    GoTo lblEnd
lblMessage:
    MsgBox "Won on roll " & i
lblEnd:
End Sub

It should be noted that in this example, the use of the GoTo statement appears unnecessarily complicated. Using a Do loop can significantly simplify and shorten the code.