Loop Operators
In VBA There are two main types of loops - counter loops (parametric) and conditional loops (iterative).
Counter loops are used when it is necessary to perform certain actions a certain number of times.
Conditional loops are used when certain actions in the program must be repeated until a certain condition is met.
Loops with parameterFor… Next
Cycle structure:
For Cycle_Parameter \u003d Start_ValueTo End_Value
[Step Step]
Operators
[Exit For]
Next [Cycle_Parameter]
where For - keywordVBA (from), indicating the beginning of the cycle;
Cycle_parameter is a variable defined as a cycle counter;
Initial_Value is a number that specifies the initial value of the cycle parameter;
To is a keywordVBA (before) separating
Start_value and End_Value;
End_Value is a number that specifies the value of the loop parameter,
At which the cycle ends;
Step is a keywordVBA (step) used for
Loop step assignments, optional;
Step is a number that specifies the step of the cycle, i.e. the value by which
Increase (or decrease) parameter value
Cycle at every step. This number can be
Negative;
Exit For - operator of early exit from the cycle (optional);
Next - keywordVBA (next) denoting
End of cycle.
Cycle operation:
Step 1 First, the loop parameter is determined, and the initial and final values \u200b\u200bof this variable are calculated and stored.
Step 2 The cycle parameter is assigned an initial value.
Step 3 The start value of the loop parameter is compared with the end value.
If the loop parameter is greater than the final value, the program immediately exits the loop and goes to the line of code that follows the loop.
Step 4 The body of the loop is executed.
Step 5 After the loop body is executed, the next value is assigned to the loop parameter. Go to step 3.
Note.
1. If a keyword is usedStep , then the loop parameter changes according to the number specified after this word. If the wordStep is absent, then the step value is equal to one.
Example 1.
For I \u003d 0 To 10 Step 2 (Value I will increase by 2)
2. For ... Next loop can be interrupted ahead of schedule when a condition is reached. To do this, in the right place in the loop, you need to place the operatorExit For.
Example 2.
Dim S As Integer
Dim j As Integer
S \u003d 2
For j \u003d 1 To 10
S \u003d S + j
If S\u003e 6 Then
Exit For (Exit the loop if the valueS\u003e 6)
End If
Next j
MsgBox (S)
Conditional loops (iterative)
If some action (several actions) needs to be performed many times, but it is not known in advance how many times and it depends on some condition, then you should use a loop with a precondition or with a postcondition.
In VBA there are two main cyclesDO ... LOOP - with a condition entered by a keywordWhile , and with the condition introduced by the keywordUntil ... They can both be preconditioned or postconditioned.
Syntax:
where Do - keyword (do);
While - keyword (for now);
Until - keyword (until then);
Loop - a keyword indicating the end of the cycle;
<условие> - a logical expression, the truth of which is checked
At the beginning of each execution of the loop body;
<тело_цикла> - an arbitrary sequence of operators;
Do ... While construct read: do while the condition is met. In constructionDo ... While For
The Do ... Until construct reads: do until the condition is met. In constructionDo ... Until to increase the step, you should write a special operator, since in it, in contrast to the designFor , this is not done automatically.
Condition written after the keywordUntil , is checked at the end of each iteration (after executing the loop body). Note that it does not work exactly as it does in a loopWhile ... If the condition is true (True ), then the loop ends. If the condition is not met (is false -False ), then the loop body is executed again.
Example 1.
Formulation of the problem. Calculate the sum of a finite series using a routine routine.
Task execution technology:
1. Initial data:i Z
Result: S R.
2. Type the following custom procedure in the standard module of the project using a loop with a preconditionWhile:
Sub summa ()
Dim S As Integer
Dim i As Integer
S \u003d 0
i \u003d 1
Do While i<= 10
S \u003d S + i ^ 2
i \u003d i + 1
Loop
MsgBox (S)
End Sub
3. Type the following custom procedure in the standard module of the project using a loop with a preconditionUntil:
Sub summa ()
Dim S As Integer
Dim i As Integer
S \u003d 0
i \u003d 1
Do Until i\u003e 10
S \u003d S + i ^ 2
i \u003d i + 1
Loop
MsgBox (S)
End Sub
4 Type in the standard module of the project the following custom procedure using a loop with a postconditionWhile:
Sub summa ()
Dim S As Integer
Dim i As Integer
S \u003d 0
i \u003d 1
S \u003d S + i ^ 2
i \u003d i + 1
Loop While i<= 10
MsgBox (S)
End Sub
5 Type in the standard module of the project the following custom procedure using a loop with a postconditionUntil:
Sub summa ()
Dim S As Integer
Dim i As Integer
S \u003d 0
i \u003d 1
S \u003d S + i ^ 2
i \u003d i + 1
Loop Until i\u003e 10
MsgBox (S)
End Sub
VBA. Organization of cycles.
Loop statements are used to repeat the execution of an action or group of actions a specified number of times. The number of repetitions (loop iterations) can be predefined or calculated.
VBA supports two kinds of looping constructs:
- Loops with a fixed number of repetitions ( counter cycles).
- Loops with indefinite repetitions ( conditional loops).
For all types of cycles, the concept is usedloop body which defines a block of statements enclosed between the start and end statements of the loop. Each repetition of the execution of the operators of the loop body is callediteration.
Fixed cycles
VBA provides two control structures for organizing a fixed loop: For… Next (counter loop) and For Each… Next (enumeration loop).
For… Next statement it is a typical counter loop that performs a specified number of iterations. For ... Next statement syntax:
For<счетчик> = <начЗначение> Then<конЗначение>
<блок операторов>
Next [<счетчик>]
An example of using the For… Next statement.
Listing 1. The For ... Next Statement
‘CHALLENGE: Write a program that receives two numbers from the user.
‘Adds all the numbers in the range given by those two numbers, then
'Displays the resulting amount.
Sub sample7 ()
Dim i As Integer 'cycle counter
Dim sStart 'start counter value
Dim sEnd 'counter end value
Dim sSum As Long 'resulting sum
sStart \u003d InputBox (“Enter the first number:”)
sEnd \u003d InputBox (“Enter the second number:”)
sSum \u003d 0
For i \u003d CInt (sStart) To CInt (sEnd)
sSum \u003d sSum + i
Next i
MsgBox “The sum of numbers from” & sStart & ”to” & sEnd & ”is:” & sSum
End Sub
For Each ... Next Loop Statement belongs to the category of object type operators, i.e. applies primarily to collectionsobjects as well as arrays ... The body of the loop is executed a fixed number of times, corresponding to the number of elements in the array or collection. For Each ... Next statement format:
For Each<элемент> In<группа> <блок операторов> Next [<элемент>]
Conditional loops (undefined loops)
Conditional loops are used when repetitive actions need to be performed only under certain conditions. The number of iterations is not defined and in the general case can be zero (in particular, for loops with a precondition). VBA provides developers with several control structures to organize conditional loops:
- Four types of Do..Loop loops, which differ in the type of the checked condition and the time of execution of this check.
- A non-interruptible While ... Wend loop.
Do While ... Loop - typical precondition loop... The condition is checked before the loop body is executed. The cycle continues its work while it is<условие> is executed (i.e. is True). Since the check is performed at the beginning, the body of the loop may never be executed. Do While ... Loop format:
Do while<условие>
<блок операторов>
Loop
Listing 2. The Do While ... Loop
‘OBJECTIVE: Write a program that requires user input
'An arbitrary sequence of numbers. Input must be terminated
'Only after the sum of the odd numbers entered exceeds 100.
Sub sample8 ()
Dim OddSum As Integer 'sum of odd numbers
Dim OddStr As String 'string with odd numbers
Dim Num 'to accept input numbers
OddStr \u003d “” 'output string initialization
OddSum \u003d 0 'initialization of OddSum sum
Do While OddSum< 100 ‘начало цикла
Num \u003d InputBox (“Enter a number:“)
If (Num Mod 2)<> 0 Then 'parity check
OddSum \u003d OddSum + Num 'accumulation of the sum of odd numbers
OddStr \u003d OddStr & Num & ””
End If
Loop
'Output line with odd numbers
MsgBox prompt: \u003d "Odd numbers:" & OddStr
End Sub
Do ... Loop While Statement designed to organizeloop with postcondition... The condition is checked after the body of the loop is executed at least once. The cycle continues its work until<условие> remains true. Do ... Loop While format:
Do<блок операторов> Loop while<условие>
Listing 3. Loop with postcondition
‘OBJECTIVE: Create a program for the game“ Guess the number ”. The program must be random
‘To generate a number in the range from 1 to 1000, the user must
‘Guess this number. The program displays a hint for each entered number
' "more or less".
Sub sample8 ()
Randomize Timer 'initialize the random number generator
Dim msg As String 'message string
Dim SecretNumber As Long, UserNumber As Variant
Begin: SecretNumber \u003d Round (Rnd * 1000) ‘computer generated number
UserNumber \u003d Empty ‘user entered number
Do ‘gameplay
Select case true
Case IsEmpty (UserNumber): msg \u003d “Please enter a number”
Case UserNumber\u003e SecretNumber: msg \u003d “Too many!”
Case UserNumber< SecretNumber: msg = “Слишком мало!”
End Select
UserNumber \u003d InputBox (prompt: \u003d msg, Title: \u003d "Guess the number")
Loop While UserNumber<> SecretNumber
‘Check
If MsgBox (“Play again?”, VbYesNo + vbQuestion, “You guessed it!”) \u003d VbYes Then
GoTo Begin
End If
End Sub
Do Until ... Loop and Do ... Loop Until cycles are the inversions of the previously considered conditional loops. In general, they work in the same way, except that the body of the loop is executed when the condition is false (i.e.<условие>\u003d False). Do Until ... Loop format:
Do until<условие> <блок операторов> Loop
Do ... Loop Until loop format:
<блок операторов>
Loop Until<условие>
Practical task: Rewrite the programs in Listings 10 and 11 using the inverted loop operators.
While ... Wend Loop also applies to conditional loops. This statement is fully consistent with the Do While ... Loop structure. While ... Wend loop format:
While<условие>
<блок операторов>
Wend
A distinctive feature of this operator is the impossibility of forced termination (interruption) of the loop body (the Exit Do operator does not work in a While ... Wend loop).
Interrupt cycle
The Exit statement is used to terminate the iteration early and exit the loop. This statement works in any loop structure except While ... Wend. The general syntax for using Exit to break a loop is as follows:
<начало_цикла>
[<блок операторов1>]
Exit (For | Do)
[<блок операторов2>]
<конец_цикла>
When the Exit statement is executed, the loop is interrupted and control is transferred to the operator following the statement<конец_цикла>... Several Exit statements can be present in the body of the loop.
Listing 4. Force exit from the loop
Sub sample9 ()
For i \u003d 1 To 10000000
If i \u003d 10 Then Exit For 'exit from loop when counter reaches 10
Next
There are situations when a VBA program is required to perform the same set of actions several times in a row (that is, repeat the same block of code several times). This can be done using VBA loops.
For loop operator in Visual Basic
The structure of the loop operator For in Visual Basic can be organized in one of two forms: as a loop For… Next or as a loop For Each.
Cycle "For ... Next"
Cycle For… Next uses a variable that sequentially takes values \u200b\u200bfrom a given range. With each change of the value of the variable, the actions enclosed in the body of the cycle are performed. This is easy to understand from a simple example:
For i \u003d 1 To 10 Total \u003d Total + iArray (i) Next i
In this simple loop For… Next variable used i, which sequentially takes the values \u200b\u200b1, 2, 3,… 10, and for each of these values, the VBA code inside the loop is executed. Thus, this loop adds the elements of the array iArray in variable Total.
In the above example, the loop increment is not specified, so to increment the variable i from 1 to 10, the default is increment 1 ... However, in some cases it is necessary to use different increment values \u200b\u200bfor the loop. This can be done using the keyword Stepas shown in the following simple example.
For d \u003d 0 To 10 Step 0.1 dTotal \u003d dTotal + d Next d
Since in the above example, the increment is set to 0.1 , then the variable dTotal for each repetition of the cycle takes on the values \u200b\u200b0.0, 0.1, 0.2, 0.3,… 9.9, 10.0.
You can use a negative value to determine the loop step in VBA, for example, like this:
For i \u003d 10 To 1 Step -1 iArray (i) \u003d i Next i
Here the increment is -1 , so the variable i with each repetition of the cycle takes on the values \u200b\u200b10, 9, 8, ... 1.
Loop "For Each"
Cycle For Each similar to a cycle For… Next, but instead of iterating over the sequence of values \u200b\u200bfor the counter variable, the loop For Each performs a set of actions for each object from the specified group of objects. In the following example, using a loop For Each enumerates all sheets in the current Excel workbook:
Dim wSheet As Worksheet For Each wSheet in Worksheets MsgBox "Sheet Found:" & wSheet.Name Next wSheet
Loop interrupt statement "Exit For"
Operator Exit For used to interrupt the cycle. As soon as this statement is encountered in the code, the program ends the execution of the loop and proceeds to the execution of the statements that are in the code immediately after this loop. This can be used, for example, to search for a specific value in an array. For this, each element of the array is scanned using a loop. As soon as the required element is found, there is no need to look through the rest - the loop is interrupted.
Operator application Exit For demonstrated in the following example. Here the loop iterates over 100 array entries and compares each with the value of the variable dVal... If a match is found, then the loop is terminated:
For i \u003d 1 To 100 If dValues \u200b\u200b(i) \u003d dVal Then IndexVal \u003d i Exit For End If Next i
Do While Loop in Visual Basic
Cycle Do while executes a block of code as long as the specified condition is met. The following is an example of a procedure Sub, in which using the loop Do while Fibonacci numbers not exceeding 1000 are displayed sequentially:
"Sub procedure outputs Fibonacci numbers not exceeding 1000 Sub Fibonacci () Dim i As Integer" counter to indicate the position of an element in the sequence Dim iFib As Integer "stores the current value of the sequence Dim iFib_Next As Integer" stores the next value of the sequence Dim iStep As Integer "stores size of the next increment "initialize variables i and iFib_Next i \u003d 1 iFib_Next \u003d 0" Do While loop will execute until the value "of the current Fibonacci number exceeds 1000 Do While iFib_Next< 1000 If i = 1 Then "особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub
In the above example, the condition iFib_Next< 1000 checked at the beginning of the cycle. Therefore, if the first value iFib_Next would be more than 1000, then the loop would never be executed.
Another way to implement a loop Do while - place the condition not at the beginning, but at the end of the cycle. In this case, the loop will be executed at least once, regardless of whether the condition is met.
Such a cycle is schematically Do while with a checked condition at the end it will look like this:
Do ... Loop While iFib_Next< 1000
The Do Until Loop in Visual Basic
Cycle Do until very similar to a loop Do while: the block of code in the body of the loop is executed over and over again until the specified condition is met (the result of the conditional expression is True). In the following procedure Sub using a loop Do until retrieves values \u200b\u200bfrom all cells in a column A worksheet until an empty cell is encountered in the column:
IRow \u003d 1 Do Until IsEmpty (Cells (iRow, 1)) "The value of the current cell is stored in the array dCellValues \u200b\u200bdCellValues \u200b\u200b(iRow) \u003d Cells (iRow, 1) .Value iRow \u003d iRow + 1 Loop
In the above example, the condition IsEmpty (Cells (iRow, 1)) is at the beginning of the construction Do until, therefore, the loop will be executed at least once if the first cell taken is not empty.
However, as shown in the loop examples Do while, in some situations it is necessary for the loop to be executed at least once, regardless of the initial result of the conditional expression. In this case, the conditional expression should be placed at the end of the loop, like this:
Do ... Loop Until IsEmpty (Cells (iRow, 1))
Operator syntax:
ForCounter= Start Tothe end[StepStep]
Block_Operators
NextCounter
Here it is indicated:
For – for (required keyword VB);
To – before (required keyword VB);
Block_Operators- one or more operators called the body of the cycle;
Counter -an integer variable that counts the number of cycles performed;
Start, End -start and end values \u200b\u200bof the counter;
Step – step (keyword VB);
Step -step of changing the counter; may be negative; the parameter is optional, because if step is 1, you can Step Steplower;
Next– next (mandatory keyword VB, end of loop statement).
Counter value (Start, End)may be numeric constants or variables of integer or real type, can be negative or positive numbers. For the loop body to be executed at least once, there must be Start ≤ End,if Step\u003e 0, and Start ≥ End,if Step< 0. As soon as it turns out that Counter>The end (if Start< Конец), the cycle ends. If Start \u003dThe end,the cycle will be executed once;
Example 9.1 .Calculating a functionY = 2 – 1.5 Sinx when X is changed with a step of 0.2 in the range.
Fragment of the program for calculating Y and outputting the argument X and the function Y:
For X \u003d 0 To 2.4 Step 0.2
Y \u003d 2 - 1.5 * Sin (X)
To understand how this cycle works, we present a program for a similar cycle created with operatorGoTo, labels, operatorIf… Then.
M1: X \u003d X + 0.2
If X<= 2.4 Then
Y \u003d 2 - 1.5 * Sin (X)
Let's analyze how this program works. The first calculation is Ynonstandard, as if it falls out of the cycle. The cycle begins after the first transfer of control from GoToM1 to the M1 label. In the line labeled M1, the argument X is incremented by a step of 0.2, and it is immediately checked whether the new value X does not exceed the final value 2.4. If it does not exceed, then the calculation of Y is repeated with this new X. Then the GoToM1 operator is executed again - transferring control to the line with the M1 label. These repetitions (cycles) of calculating Y will end as soon as X exceeds 2.4.
Now the program with If is compared with the For ... Next loop.
For X \u003d 0 To 2.4 Step 0.2
replaces two lines
M1: X \u003d X + 0.2
If X<= 2.4 Then
It is the last two lines of code that are executed in the For loop, but we don't see that. We encoded them with For ... The GoTo M1 code line is encoded with the word Next X (literally: next X). The result is a compact design.
When using a For… Next loop, you need to learn how to use the loop counter to solve various problems. To figure out how you can use a counter to solve a problem, you need analyze the formulation of the problem, find patterns of change in some parameters of the problem.
Example 9.2 . Determination of the sum of the elements of the series:.
Fragment of the program:
S \u003d 0 'S - the sum of the series
For i \u003d 1 To 16 'counter i is the denominator of the fraction
S \u003d S + 1 / i 'accumulation of the sum
Print “S \u003d“; S 'output the sum S to the form
For each value of the counter i, the expression 1 / i sequentially forms the elements of the row, starting from 1.
Example 9.3
.
Calculating the sum of a series of elements 
.
Fragment of the program:
S \u003d 0 'S - the sum of the series
For i \u003d 1 To 18 'counter i - numerator
S \u003d S + i / (i + 1) 'the denominator is 1 more than the numerator
Print “S \u003d“; S 'output the sum S to the form
Example 9.4 . Calculation of the sum: 5 + 8 + 11 + ... + 32 + 35
Fragment of the program:
S \u003d 0 'S - the sum of the series
For i \u003d 5 To 35 Step 3 'get arithmetic _
progression with denominator 3
Print “S \u003d“; S
Example 9.5. Calculating the sum for a given x:
Analysis of the problem shows that the degree at X varies from 1 to 10. In this case, the numerator in the coefficient at X is greater than the degree by 1, and the denominator by 2. The value of the degree will be formed using the counter i. Then you can compose the following program (fragment):
S \u003d 1 'S - the sum of the series
For i \u003d 1 To 10 'as counter i, the degree changes at X
S \u003d S + (-1) ^ i * (i + 1) * x ^ i / (i + 2)
Print “S \u003d“; S
CyclesFor… Next used ininput, output andprocessing arrays .
Example 9.6. Input and output of values \u200b\u200bof elements of array B (N).
Fragment of the program:
'Skip assigning a value to variable N, _
entered on the form in the txtN text box:
B (i) \u003d InputBox ("Enter item B (" & i & ")", _
"Input array B (" & N & ")")
Print ""; B (i);
Function InputBox() displays a dialog box with a close button, specified by message, input field, buttons OK,Cancel,the given title (or without it). If the number 12 is entered - the size of the array N, then in our example, when it first appears, this window will look like:
As you can see, the message Enter item B (1)prompts you to enter the value of the 1st item in the text box. This window will appear 12 times, since the array contains 12 elements. This follows from the title of the form. The index of item B (i) in the invitation will vary from 1 to 12.
If you need to display only the values \u200b\u200bof the elements of the array B (N) on the form, then the body of the cycle will consist of one operator:
Viewing the elements of an array to perform some actions on them also occurs using the For… Next loop operator.
Let us give examples of processing one-dimensional arrays.
Example 9.7 . Determination of the maximum element in the array B (M).
Excluding input of initial data and output of results, we will briefly describe the algorithm:
Let's declare the variable Вmaх, into which we will enter the value of the first element of the array, and the variable Imax, to which we will assign 1 - the index of the first element of the array.
In a loop, using the For ... Next operator, we look through all the elements of the array, starting from the 2nd. Using the If… Then operator, we compare their values \u200b\u200bwith the value stored in the Bmax variable.
If it turns out that the value of an array element is greater than Bmax, then Bmax is assigned the value of this element, and the Imax value is the index of this array element.
After the end of the cycle, the value of the maximum element will be shown in the variable Bmax, and its index (number) in Imax.
The program for this part of the algorithm.
Bmax \u003d B (1): Imax \u003d 1
If B (i)\u003e Bmax Then Bmax \u003d B (i): Imax \u003d i
Example 9.8. Determining the sum, product and number of positive elements of an arrayD(M).
Variables:S, P, K - respectively the sum, product and number of positive elements.
Algorithmsuch a definition:
We assign zero to the variable S and K, to the variable P we assign 1. As a rule, always the variables where the sum is accumulated, here it is S and k, are set to zero before the cycle, and the variables in which the product is calculated are assigned 1.
Using the For… Next loop, we iterate over all the elements of the array and check if they are positive (D (i)\u003e 0).
If it turns out that the element is positive, then we add its value to the value of the sum S and store the new sum in the same variable. We multiply the variable P by the positive value of the element and store it also in the variable P. And add 1 to the variable K and save the new value in the same variable
Programthis part of the algorithm looks like:
S \u003d 0: P \u003d 1: K \u003d 0
If D (i)\u003e 0 Then
S \u003d S + D (i) 'this is how the amount accumulates _
positive values \u200b\u200bof elements of array D (i)
P \u003d P * D (i) 'definition of the product of positive
'Array elements
K \u003d K + 1 'this operator is called COUNTER, here it is
'Defines the number of positive elements in the array
Example 9.9. Determining the sum, product, number and average of odd array elementsD(M).
Here is a fragment of the program of such a definition.
S \u003d 0: P \u003d 1: K \u003d 0
If D (i) Mod 2<> 0 Then
Ssr \u003d S / k 'calculating the average of odd elements
Compare this code snippet with the code in Example 9.8. This program almost completely repeats the previous one. Only the condition in the If statement has changed. Condition D (i) Mod 2<> 0 means that we are looking for elements of array D (i) that are not divisible by 2, that is, odd elements. If we check the condition D (i) Mod 2 \u003d 0, then we will select even elements of the array.
As you know, division by Modresults in the remainder of division in integers. For example, after executing the operator d \u003d 34Mod4, the variable d will be equal to 2. Therefore, to select array elements that are multiples of 4, the condition D (i) Mod 4 \u003d 0 must be checked. The condition will be similar if we search for elements that are multiples of other numbers. Instead of 4, these other numbers will be written.
Example 9.10. Writing array elementsR(N), multiples of 5, to another array and output the new array to the form.
Another array is denoted, for example, R5 (N). The size of this new array should be the same as the original one, since in the extreme case all elements can be multiples of 5.
Algorithm of the task:
Reset counter k. Using the loop operator For ... Next, we go through all the elements of the array R (N).
We check each element for multiplicity 5 using the If… Then statement and dividing the array element by Mod.
If the element is a multiple of 5, then using a counter like k \u003d k + 1 form the indices of the R5 (N) array, starting from 1, and write it into this other array –R5 (N).
If k is nonzero, we output the R5 () array to the form.
If k is equal to zero, we output: “There are no elements divisible by 5”.
Fragment of the program:
If R (i) Mod 5 Then k \u003d k + 1: R5 (k) \u003d R (i)
If k<> 0 Then
Print “No items divisible by 5”
Loops can be nested within other loops.
Let's demonstrate the work nested loops ... Below in the program, the output of the values \u200b\u200bof the loop counters i, j and k is organized. From the output of i, j, k it becomes clear how nested loops are executed.
Private Sub frmCycle_DblClick ()
ScaleMode \u003d 4 'units of measure - characters
For i \u003d 1 To 3 'outer loop
Print "i \u003d"; i;
For j \u003d 1 To 4 '1st nested loop
CurrentX \u003d TextWidth ("i \u003d 1") + 5
Print "j \u003d"; j;
CurrentX \u003d TextWidth ("i \u003d 1 j \u003d 1") + 7
For k \u003d 1 To 5 ’2nd nested loop
The depicted form (Fig. 1) shows the results of outputting the counters of all three cycles: the outer loop - counter i, the first nested loop - counter j and the second, innermost loop - counter k. As you can see the "slowest" counter of the outer loop(poi), and the "fastest" is the counter of the innermost cycle (byk).
The program is executed after double-clicking the left mouse button on the frmCicli form.
CurrentX, CurrentY - form properties that specify the X, Y coordinates of the start point for information output by the Print method (see in Fig. 1 the location of the X and Y axes on the form).
TextWidth () - a function that returns the width of the text specified in the function as an argument in double quotes.
Nested loops are used when processing two-dimensional arrays (matrices). But in some tasks, excluding input and output of elements of a two-dimensional array, you can limit yourself to one cycle. Let's consider some examples of matrix programming.
Example 9.11. Input and output of a matrix (two-dimensional array) of integersintA(N).
You can enter a matrix by row and by column ... Easier - by lines, if the output of array elements on the form will be programmed immediately after their input.
Matrix input and outputline by line - fragment 1.
Dim M As Integer, N As Integer, i As Integer, j As Integer
Dim intA () As Integer 'declare a dynamic array
M \u003d Val (txtN.Text) 'M - number of lines
N \u003d Val (txtN.Text) ‘N - number of columns
ReDim intA (M, N) As Integer 'redefine array
For i \u003d 1 To M 'i will retain its value until completely
'Nested loop on j will not be executed
Print ""; intA (i, j); 'Output line by line
Print 'jump to the beginning of a new line
To enter a matrix by columns, you should loop through j outer (sets the column numbers), and the inner loop through i (sets the row numbers).
Matrix input and outputby columns – fragment 2.
PrY \u003d 2500: CurrentY \u003d PrY 'PrY sets the Y coordinate of the start
'Output the first element of each column on the form
For j \u003d 1 To N 'j will keep its value until completely
'Nested loop on i will not be executed
intA (i, j) \u003d InputBox ("Enter intA element (" & i & “,” & j & ")", _
"Input matrix intA (" & M & "," & N & ")")
Print Tab (6 * j); intA (i, j) 'output by column
CurrentY \u003d PrY 'to display the first item
‘Next column
This second program fragment does not repeat the first 5 lines from the first fragment. The Tab (6 * j) function sets the start of the output on a line (in characters), starting from the left edge of the form. The PrY coordinate here is 2500 twips, but you can choose another value.
Example 9.12 . Ordering the values \u200b\u200bof array elementsV(N) Ascending.
There are several algorithms for ordering arrays. Here is one of them: using nested loopsFor… Next select the elements, starting with the first to the penultimate, and compare each of them with the subsequent elements; if it turns out that the next element is less than the selected one, we swap them.
A fragment of the program that implements this algorithm:
For i \u003d 1 To N - 1
For j \u003d i + 1 To N
If V (j)< V(i) Then P = V(i): V(i) = V(j): V(j) = P
Let's explain this fragment of the program.
Using the outer loop with counter i, select the element V (i) to compare with subsequent elements. The inner loop with counter j selects subsequent elements of V (j) for comparison. The initial value of j is i + 1. This is the first of the following.
To exchange the values \u200b\u200bof the elements V (i) and V (j), we introduce some variable P, in which we temporarily "hide" the value of one of the array elements (in the program it is V (i)). Then to the element V (i) we assign the value of the element V (j), and to the element V (j) we assign the value V (i), which is stored in the variable P. If we "hide" the value V (j) in P, then the code for the exchange of values \u200b\u200bwill be as follows: P \u003d V (j): V (j) \u003d V (i): V (i) \u003d P. The result will not change.
To order the array in descending order, it is sufficient to write the condition for verification in the form V (j)\u003e V (i), i.e., change the sign of the inequality to another.
If the array is not numeric, but string, and surnames will be entered into the array elements, then according to the program of example 9.12, you can sort the list of surnames alphabetically. The fact is that for the letters of the alphabet used in a computer, the following inequalities are true: A< Б < В < Г….< Я и т. д., поскольку числовые коды букв алфавита последовательно увеличиваются, начиная с А и до конца алфавита. Это же справедливо и для букв английского алфавита.
Example 9.13 . Calculating the sum of positive elements for odd columns of a matrixF(M, N) and the withdrawal of amounts to the form.
Algorithm of the program:
Using the outer loop with step 2, we form the index of the odd columns of the matrix, starting from the first column.
We set to zero the sum S, in which the sum of positive elements will be accumulated.
In the inner loop, we check the sign of the array element.
If the array element is positive (\u003e 0), calculate the sum of S.
After the end of the inner cycle, we output the sum S to the form.
Fragment programs:
For j \u003d 1 To N Step 2
If F (i, j)\u003e 0 Then S \u003d S + F (i, j)
Print “Column sum“; j; “:”; S 'j is the column number!
Nested loops let you organize enumeration of options and solve problems that cannot be solved analytically. As a result of enumeration, variants of solutions are obtained, among which one or several are selected that meet the condition of the problem.
Example 9.14. There is 1801 rubles. How many chocolates can you buy for 31 rubles. and buns for 18 rubles. To fully spend all the money.
Program:
Amount \u003d 1801
AllChocolates \u003d Sum \\ 31: AllBuns \u003d Sum \\ 18
For i \u003d 1 To All
For j \u003d 1 To AllBuns
Price \u003d i * 31 + j * 18 'total purchase price
If Price \u003d 1801 Then
Print "Chocolate:"; i; Tab (19); "Bun:"; j
Let us explain the program in which we use variables named in Russian.
First, we determine what the maximum number of only chocolates or only rolls can be bought for the entire amount. We use the obtained values \u200b\u200bof VseChocolate and VseBulki to limit the number of brute-force attempts by the number of chocolates and rolls. For each value of the number of chocolates (i) and the number of rolls (j), we determine the total Price for their purchase. If the calculated Price is 1801, then the selected option is one of the solutions to the problem.
The Tab () function specifies from what position from the edge of the form the information will be displayed following this function. If Tab (19), then from the 19th position.
The program displays 3 possible purchase options:
Chocolate 7, roll 88
Chocolate 25, roll 57
Chocolate 43, roll 26
If an unambiguous answer is required, an additional condition must be formulated. For example, “fewer rolls were bought than chocolates”. Then the only solution would be the 3rd option.
There are many math problems that can be easily solved by iteration using nested loop programming.
Loops allow you to execute one or more lines of code multiple times. VBA supports the following loops:
For ... Next For Each ... Next Do ... Loop
For. ... ... Next. When the number of repetitions is known in advance, a For loop is used. ... ... Next. A For loop uses a variable called a loop variable or loop counter that increases or decreases by a specified amount each time the loop repeats. The syntax for this construction is as follows:
For counter \u003d start To end Next statements
The parameters counter, start, end, and increment are numeric.
Note. The increment parameter can be either positive or negative. If positive, the start parameter must be less than or equal to the end parameter, otherwise the loop will not run. If increment is negative, then start must be greater than or equal to end for the loop body to execute. If Step is not specified, the default value for increment is 1.
VBA executes the For loop in the following sequence:
1. Sets the value of the loop variable counter to start.
2. Compares the value of the loop variable counter and the value of the end parameter. If counter is greater, VBA ends the loop. (If increment is negative, then VBA stops executing the loop if the value of the loop variable counter is less than the value of the end parameter.)
3. Executes statements of the loop body statements.
4. Increases the value of the loop variable counter by 1 or by the value of the increment parameter, if specified.
5. Repeats steps 2 through 4.
Consider an example: Calculate the value of the function f (t)
given a, b, n, if t changes from a to b with a step Dt \u003d (b-a) / (n-1).
Sub example 3 () Dim f () As Single Dim a As Single, b As Single, t As Single, dt As Single Dim i As Integer, n As Integer Call read ("a1", a): Call read ("b1" , b): Call read ("c1", n) ReDim f (1 To n - 1) dt \u003d (b - a) / (n - 1): t \u003d a Call out ("a2", "i") : Call out ("b2", "t"): Call out ("c2", "f (t)") For i \u003d 1 To n - 1 t \u003d t + dt If t<= -1 Then f(i) = -1 ElseIf t > 1 Then f (i) \u003d 1 Else f (i) \u003d t End If Call out ("a" & (2 + i), i): Call out ("b" & (2 + i), t): Call out ("c" & (2 + i), f (i)) Next i End Sub
For Each construct. ... ... Next
For Each loop. ... ... Next is similar to a For loop. ... ... Next, but it repeats a group of statements for each element from a set of objects or from an array, instead of repeating statements a specified number of times. It is especially useful when you don't know how many elements are in a set.
The syntax for the For Each loop construct. ... ... Next is:
For Each element In group Next element statements
Keep in mind the following limitations when using a For Each loop. ... ... Next:
For sets, the element parameter can only be a variant variable, a shared object variable, or an object listed in the Object Browser
For arrays, the element parameter can only be a Variant.
You cannot use a For Each loop. ... ... Next with an array that is of a user-defined type, since a variant variable cannot contain a value of a user-defined type
Do ... Loop construction
The Do loop is used to execute a block of statements an unlimited number of times. There are several flavors of the Do construct. ... ... Loop, but each evaluates a condition expression to determine when to exit the loop. As with the If construct. ... ... The Then condition must be a value or an expression that can be False (zero) or True (non-zero).
In the next construction Do. ... ... Loop statements are executed as long as the condition is True:
Do While Condition Loop Statements
In this loop, VBA checks the condition first. If the condition is False, it skips all loop statements. If True, VBA executes the loop statements, returns to the Do While statement, and checks the condition again.
Therefore, the loop represented by this construct can run any number of times as long as the condition value is not zero or True. Note that the statements of the loop body are not executed even once if the condition is checked for the first time (False).
Consider an example: Calculate the sum of a series
with a given accuracy.
Sub example 4 () Dim e As Single, x As Single, s As Single Dim m As Single, p As Single, i As Single Call read ("a1", x): Call read ("b1", e) s \u003d 0 : i \u003d 1: m \u003d 1: p \u003d -1 Call out ("a2", "i"): Call out ("b2", "m"): Call out ("c2", "s") Do While Abs (m)\u003e \u003d ep \u003d -p * xm \u003d p / is \u003d s + m Call out ("a" & (2 + i), i): Call out ("b" & (2 + i), Abs (m)): Call out ("c" & (2 + i), s) i \u003d i + 1 Loop End Sub
Another variation on the Do. ... ... Loop first executes the statements of the loop body and then checks the condition after each execution. This variation ensures that the loop body statements are executed at least once:
Do statements Loop While condition
The other two flavors of the loop construct are similar to the previous ones, except that the loop is executed while the condition is False:
The loop does not run at all or runs many times:
Do Until condition
loop operators
The loop runs at least once:
operators
Loop Until condition
7.2 Nested loops.
You can put control structures inside other control structures (for example, an If... Then block inside a For... Next loop). It is said that a control structure placed inside another control structure is nested.
The depth of nesting of control structures in VBA is not limited. To improve the readability of the code, the practice of shifting the body of the decision-making structure or the loop in the program is adopted in the case of using nested control structures.
When nesting one or more other loops in a loop, one speaks of nested loops, in which one distinguishes between outer (enclosing) and inner (nested) loops.
Consider an example of summing the elements Aij of the matrix A (n, m) row by row.
Sub example 5 () Dim a () As Single, s () As Single Dim n As Integer, m As Integer Dim i As Integer, j As Integer Call read ("a1", n): Call read ("b1", m ) ReDim a (1 To n, 1 To m), s (1 To n) "Reading the matrix For i \u003d 1 To n For j \u003d 1 To m Call readcell (i + 1, j, a (i, j)) Next j Next i "Calculation For i \u003d 1 To ns (i) \u003d 0 For j \u003d 1 To ms (i) \u003d s (i) + a (i, j) Next j Call outcell (i + 1, m + 1 , s (i)) Next i End Sub
Note that the first Next statement closes the inner For loop, and the last Next statement closes the outer For loop. Likewise, for nested If statements, End If statements are automatically applied to close the closest If statement. Nested Do structures. ... ... Loops work in a similar way: the farthest Loop operator matches the farthest Do operator.
When inputting / outputting elements of a two-dimensional array to a Microsoft Excel worksheet, it is convenient to use custom input / output procedures:
Sub readcell (i As Integer, j As Integer, val As Variant) val \u003d Sheet1.Cells (i, j) .Value End Sub Sub outcell (i As Integer, j As Integer, val As Variant) Sheet1.Cells (i, j) .Value \u003d val End Sub
where I is the row number, j is the worksheet column number.
Leaving management structures
The Exit statement lets you exit directly from a For loop, Do loop, Sub procedure, or Function procedure. The syntax for the Exit statement is simple:
For counter \u003d start To end [statement block] [statement block] Next Do [(While | Until) condition] [statement block] [statement block] Loop
Exit For inside a For loop and Exit Do inside a Do loop can appear any number of times.
The Exit Do statement works with all flavors of the Do loop syntax.
The Exit For and Exit Do statements are used when you want to end the loop immediately without continuing with further iterations or without waiting for the statement block in the loop body to execute.
When using the Exit statement to exit the loop, the values \u200b\u200bof the loop variable depend on how the loop ends:
When the cycle ends normally, the value of the cycle variable is one more than the upper limit of the number of cycles
If the loop ends prematurely, the loop variable retains its value, which it received taking into account the usual rules
When the loop ends at the end of the set, the loop variable is Nothing if it is an object variable, or Empty if it is a Variant variable
