An example of using the for next statement. “VBA Loop Operators. Nested loops. Loop with negative arguments

Cycle is a group of statements that are executed multiple times. To organize loops in VBA, that is, multiple execution of one or more statements, you can use two main groups: loops with the For ... Next enumeration and loops with the Do ... Loop condition. There are two types For ... Next (For-Next and For Each-Next) and two kinds of loops Do ... Loop (Do While ... Loop and Do Until ... Loop), which differ in the type of condition being checked.

The most common loop in VBA is the For-Next enumeration loop. This cycle needs to set the limits (start and end values \u200b\u200bof the counter) within which the cycle variable will change.

The loop operator has the following syntax:
For For n \u003d 1)
Then End Value ( Then 10)
Step Increment ( Step2)

Next [counter]

Here a variable is a counter, the value of the variable is increased or decreased with each iteration of the loop. If there is no Step in the loop construction, then the increment is 1 (by default). To exit the loop operator early, i.e. before the counter reaches its final value, the operator must be introduced into the loop construction Exit For.

Taking into account Exit For the loop operator has the following syntax:
For counter \u003d Initial Value (e.g. For example, For n \u003d 1)
Then End Value ( Then 10)
Step Increment ( Step2)
[Instructions] or [Operator block]
Exit For
Next [counter]

To process a group of homogeneous objects or arrays, the following loop construction is used:
For Each Element In Group (name of a group of homogeneous objects)
[Instructions] or [Operator block]
Exit For
Next element

The Do While ... Loop statement loops as long as a specified condition is met. It should be noted that the condition is checked before the statement or statement group is executed.

In this case, the syntax of the Do While ... Loop statements is as follows:
Do whilecondition (For example, x<20)
[Instructions] or [Operator block]
Exit Do
Loop

Do
[Instructions] or [Operator block]
Exit Do
Loop while condition

The Do Until ... Loop statement loops until the condition is met, and when the condition is met, the statement exits the loop. The condition is checked before the statement or statement group is executed.

In this case, the syntax of the Do Until ... Loop statements is as follows:
Do untilcondition (For example, x \u003d 20)
[Instructions] or [Operator block]
Exit Do
Loop

If you need to check the condition after the instructions or statement block have been executed at least once, then you can use the following loop operator construction:
Do
[Instructions] or [Operator block]
Exit Do
Loop Until condition

For ... Next loop in VBA Excel, its syntax and description of individual components. Examples of using the For ... Next loop.

The For ... Next loop in VBA Excel is designed to execute a group of statements the required number of times, specified by the loop control variable - the counter. As the loop runs, the counter increments or decreases after each iteration by the number specified by the Step statement, or, by default, by one. When you need to apply a loop to elements whose number and indexing in a group (range, array, collection) are unknown, you should use.

For ... Next Loop Syntax

The brackets indicate the optional attributes of the For ... Next loop.

For ... Next Loop Components

*If attribute Step is missing, the For ... Next loop runs with a default step of 1 .

**If you don't use your code in the loop, the meaning of using the loop is lost.

For ... Next Loop Examples

You can copy the sample loops into your VBA module, run them sequentially and see the results.

The simplest cycle

Fill the first ten cells of the first column of the active Excel sheet with numbers from 1 to 10:

Sub test1 () Dim i As Long For i \u003d 1 To 10 Cells (i, 1) \u003d i Next End Sub

Simplest loop with step

The Step operator with the value 3 was added to the previous loop, and the results are written to the second column:

Sub test2 () Dim i As Long For i \u003d 1 To 10 Step 3 Cells (i, 2) \u003d i Next End Sub

Loop with negative arguments

This loop fills the first ten cells of the third column in reverse order:

Sub test3 () Dim i As Long For i \u003d 0 To -9 Step -1 Cells (i + 10, 3) \u003d i + 10 Next End Sub

Increase the step size to -3 and write the results to the fourth column of the active Excel sheet:

Sub test4 () Dim i As Long For i \u003d 0 To -9 Step -3 Cells (i + 10, 4) \u003d i + 10 Next End Sub

Nested loop

The outer loop sequentially sets the indices of the first ten rows of the active sheet, and the nested loop adds the numbers in the first four cells of the row with the current index and writes the sum to the cell in the fifth column. Before starting the nested loop with cumulative addition, we zero the fifth cell of the corresponding row so that if any number is found in it, it will not be added to the total sum.

Sub test5 () Dim i1 As Long, i2 As Long For i1 \u003d 1 To 10 "The fifth cell in line i1 is assigned 0 Cells (i1, 5) \u003d 0 For i2 \u003d 1 To 4 Cells (i1, 5) \u003d Cells (i1 , 5) + Cells (i1, i2) Next Next End Sub

Exiting the loop

In the sixth column of the active sheet, write down the names of ten animals, of course, using the For ... Next loop:

Sub test6 () Dim i As Long For i \u003d 1 To 10 Cells (i, 6) \u003d Choose (i, "Bear", "Elephant", "Giraffe", "Antelope", _ "Crocodile", "Zebra", "Tiger", "Lizard", "Lion", "Hippo") Next End Sub

The next cycle will search in the sixth column of the crocodile that ate the galoshes. In the cell of the seventh column, the cycle, until it meets the crocodile, will write the line “There was a cycle here”, and when it finds the crocodile, it will write “He ate galoshes” and stop working by executing the Exit For command. This will be seen in the cells next to the names of the animals below the crocodile, which will not contain the text "There was a cycle".

Sub test7 () Dim i As Long For i \u003d 1 To 10 If Cells (i, 6) \u003d "Crocodile" Then Cells (i, 7) \u003d "He ate galoshes" Exit For Else Cells (i, 7) \u003d "Here there was a cycle "End If Next End Sub

You will receive such data on the active Excel sheet if you sequentially run it in the VBA editor all seven of the example routines that demonstrate For ... Next loops.

Loop with fractional arguments

The start, end, and step attributes can be represented by a number, variable, or numeric expression:

For i \u003d 1 To 20 Step 2 For i \u003d a To b Step c For i \u003d a - 3 To 2b + 1 Step c / 2

As a result of evaluating the value of a variable outside the loop or an expression inside it, a fractional result can be obtained. VBA Excel will round it to an integer using:

"Attribute values \u200b\u200bbefore rounding For i \u003d 1.5 To 10.5 Step 2.51" Rounded attribute values \u200b\u200bFor i \u003d 2 To 10 Step 3

Try not to allow unrounded argument values \u200b\u200bto enter the body of the For ... Next loop, so as not to get unpredictable results of its execution. If you need fractional numbers and want to use normal rounding, use the WorksheetFunction.Round in VBA code to round the number before using it in a For ... Next loop.

This lesson will cover working with a loop For in VBA. An example of working with a For loop, an example of creating formulas in Excel using macros will also be demonstrated.

Cycle For works on the principle of a counter. For it is used in cases when it is necessary to repeat some actions in advance known number of times. For example, the loop For often used when reading arrays.

Cycle For has the following syntax:
For counter = start of cycle To end of cycle [Step step]
a group of operators, teams, etc.
Exit For
Next counter

• "counter" -a variable that changes to the specified "step".If step is not specified, then the default is one.
• "start of cycle", "end of cycle "-numbers or variables indicating the lower limit of the counter and the upper limit. The cycle stops when "counter"\u003e"end of cycle "(or, if the cycle is reversed, i.e. with a step of -1, then "counter"< "end of cycle ").
• Exit For -command of forced stop of the cycle. It is used in cases when an event has occurred, after which it is necessary to stop the execution of commands in a loop, or to prevent an error from occurring.

Let's look at a couple of examples of using the loop For... In the future, we will come across this cycle quite often.

Example 1
Given two columns C and E filled with numbers:

Add the numbers in column C with the numbers in column E as follows:
C2 + E21, C3 + E20, ..., C21 + E2. Output the result in column D as a formula i.e. the content of the result cell should be "\u003d C2 + E21".

The macro code looks like this (we read where to write the code):

Sub Loop_For ()
"constant indicating the limit of the cycle, ie to what value the cycle should run
Const n \u003d 21
For i \u003d 2 To n
"create a string formula and save it to a cell
Cells (i, 4) \u003d "\u003d C" & CStr(i) & "+ E" & CStr((n - i) + 2)
"continue when running in a loop
Next i
"the rest of the program code
End Sub

Parse the written code:

• Const n \u003d 21 - description of the constant n with the value 21, i.e. the number of lines to run through the loop For;
• For i \u003d 2 To n - i counter which will change by 1 with each pass of the loop. The counter starts at 2 and ends when i\u003e n;
• Cells (i, 4) - cell dedicated sheet, i is the row number, 4 is the number of the column in which the result is displayed. Note that our counter i indicates the row number of the Excel sheet;
• Next i -operator for closing the loop and moving the pointer to For.Everything in between For and Next runs in a loop;
• CStr -a function that converts a number to text.

We assign the cell a formula created as follows "\u003d C" & CStr(i) & "+ E" & CStr((n - i) + 2). The & sign - "gluing" characters, lines. As a result, we get the formula "\u003d Сn + E ((n - i) + 2)" where n \u003d 21, i is a counter.
Fearfully? It only seems :)

Everything. After executing the macro, we get the following column (highlighted), and in each cell the formula:

Example 2
Now let's look at a loop with the indicated step. After calculating the last macro, we got three columns, now we need to subtract D from column E, and output subtraction formulas in column F. The macro code is as follows:

Sub Loop_For_with_step ()
Const n \u003d 21
For i \u003d n To 2 Step -1
Cells (i, 6) \u003d "\u003d E" & CStr(i) & "-D" & CStr(i)
Next i
End Sub

In this case, everything is the same, only the cycle now "runs" not from 2, but from 21 to 2 with a step (Step) -1.
The result will be the following:

Cycle For, in VBA, is not the only loop. In the future, we will consider a couple more options for cycles, which cannot be avoided when writing macros in Excel.

Lesson from the series: “ Visual Basic Programming.NEN for schoolchildren "

You are now familiar with the ComboBox control.
In this lesson, we will continue to study the operators of the Visual Basic.Net language, dwelling on the loop operators.
Cyclic algorithmic structures are of three types:

1. loops with a counter, in which the body of the loop is executed a certain number of times;
2. loops for processing arrays or collections;
3. conditional loops, in which the body of the loop is executed as long as the condition is true (or until the condition becomes true).

In this lesson, consider the counter loop operator (For... Next). Let's write a project using this operator.

Cycle with counter For. ... ... Next is used when the number of repetitions to be performed is known in advance.

Block diagram

General view of the operator

For Counter \u003d StartValue To EndValue Loop Body Next [Counter]

The syntax of the statement is as follows: the line beginning with the For keyword is the head of the loop, and the line with the Next keyword is the end of the loop, between them are the statements that are the body of the loop.

Algorithm for executing the For statement. ... ... Next

1. The value of the Counter variable is set equal to StartVal.
2. The condition Counter is checked<= КонЗнач
3. If the condition is met, then the body of the loop is executed. The variable Counter is changed by the step size: Counter \u003d Counter + Step.
4. When the Counter variable reaches the EndValue value, the loop exits and the following statements are executed.

The programmer can organize premature exit from the loop using the Exit For construction. In this case, an automatic exit from the cycle occurs and control is transferred to the command following the cycle.

Project "Income from deposit"

Write a project that calculates the income on the deposit, provides the calculation of simple and compound interest. Simple interest is charged at the end of the deposit term, compound interest - monthly and is added to the original deposit amount and the next month interest is charged on the new amount. The recommended form is shown in the figure.

Execution technology

This lesson showed you how to use a For ... Next loop to repeat code a specified number of times.

A loop is an algorithmic structure, with the help of which the multiple repetition of the statement block is implemented.

Counter loop operator (For ... Next)

The For… Next statement allows you to cyclically execute a set of instructions (operators) a specified number of times. Instruction syntax:

For counter \u003d start_valueTo end_value

[instructions]

[instructions]

Next [ counter]

Parameter counter Is a numeric variable that automatically changes its value by step after each repetition of the cycle. The cycle is executed until counter£ end_valuewith a positive parameter value step or until counter³ end_valuewith a negative parameter value step... Thus, the loop will not be executed even once if, with a positive step value start_value more than end_value... If the Step step is omitted, then by default the increment of the counter variable is set equal to 1.

The Exit For statement ends the execution of the loop ahead of schedule and transfers control to the statement following the statement in the loop. Exit For is usually placed in a conditional statement, for example:

If condition Then Exit For

Note. According to the syntax of the For statement, the parameter counter - an arithmetic variable of any type, it is still better to use the integer type of the variable, since the fractional part of the value of a variable of a real type usually has an error. It has to do with the way numbers are stored in a computer.

Example 1

Find the sum of the first 10 natural numbers: S \u003d 1 + 2 + 3 + ...+ 10 ... Fragment of the program:

Dim I as Integer, S as Integer

S \u003d 0 " Zeroing the value of the variable S

For i \u003d 1 to 10 " The beginning of the cycle. Setting the start and end values
"loop parameter. The default loop step is 1.

S \u003d S + i " The assignment operator is executed at every
"loop execution, in this example 10 times

Next I " End of cycle. The loop parameter value is incremented.

In this program fragment, the loop will be executed exactly 10 times.

Example 2

Output in the 1st column of the worksheet (starting from the 2nd row) the values \u200b\u200bof x in the range from 0 to 3.2 in increments of 0.4.

Public Sub Tab ()

Dim x As Single, i As Integer

For x \u003d 0 To 3.2 Step 0.4

Although the interval length is precisely divisible by the 0.4 step, the result on the worksheet looks like this:

What should be done?

1. You can slightly increase the final value, in this example, instead of 3.2, write 3.201.

2. Determine the number of repetitions of a chicle and write a loop over an integer type counter.

In this case, the program will take the form:

Public Sub Tab1 ()

Dim x As Single, i As Integer, n As Integer

n \u003d CInt ((3.2 - 0) / 0.4)

For i \u003d 0 To n Step 1

Cells (i + 2, 1) \u003d x

For Each ... Next Loop Statement

The For Each… Next loop lets you repeat the execution of a group of statements for each element in an array or family. This instruction has the following syntax:

For Each elementIn group

[instructions]

[instructions]

Next [ element]

Parameter elementIs a variable that represents a member of a family or array. Argument group - the name of the array or family. Instructions are performed only once for each group member. Using an instruction is convenient because you don't need to know in advance how many elements are in an array or family. Variable element must be of type Variant.

Example 1

Find the arithmetic mean of all elements of the selected range

Public Function Average (Range As Range) As Double

Dim Element As Variant

Dim Sum As Double

Dim Number As Integer

Quantity \u003d 0

For Each Item In Range

Sum \u003d Sum + Element

Quantity \u003d Quantity + 1

Next Element

Average \u003d Amount / Quantity

Application. The selected range of contiguous cells must contain numbers. Numbers in empty cells are considered to be 0. Text in a cell will result in an error.

The above If… End If, Select… Case, For… Next, For Each… Next statements are a group of control statements that change the order in which commands are executed. Control instructions can be nested within each other in any order.