Contents
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.
VBA loops include:
Next, we’ll take a closer look at each of these cycles.
For Loop Operator in Visual Basic
The structure of the loop operator The 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 from 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 = 1 To 10 Total = Total + iArray(i) Next i
In this simple loop For… Next variable is used i, which sequentially takes the values 1, 2, 3, … 10, and for each of these values, the VBA code inside the loop is executed. Thus, this loop sums 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 an increment 1… However, in some cases it is necessary to use different increment values for the loop. This can be done using the keyword Stepas shown in the following simple example.
For d = 0 To 10 Step 0.1 dTotal = dTotal + d Next d
Since in the above example, the increment step is set equal to 0.1, then the variable dTotal for each repetition of the cycle takes on the values 0.0, 0.1, 0.2, 0.3,… 9.9, 10.0.
To determine the loop step in VBA, you can use a negative value, for example, like this:
For i = 10 To 1 Step -1 iArray(i) = i Next i
Here the increment is -1, so the variable i with each repetition of the cycle takes on the values 10, 9, 8, … 1.
Loop “For Each”
Cycle For Each similar to a cycle For… Next, but instead of iterating over the sequence of values for the counter variable, the loop For Each performs a set of actions for each object in 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 "Найден лист: " & 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. To do this, using a loop, each element of the array is scanned. As soon as the required element is found, there is no need to look through the rest – the cycle 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 = 1 To 100 If dValues(i) = dVal Then IndexVal = i Exit For End If Next i
The 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 the 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 next increment 'initialize variables i and iFib_Next i = 1 iFib_Next = 0 'Do While loop will execute until value of 'current Fibonacci number is greater than 1000 Do While iFib_Next < 1000 If i = 1 Then 'special case for first element iStep = 1 iFib = 0 Else 'save the size of the next increment before overwriting 'the current value of the sequence iStep = iFib iFib = iFib_Next End If 'print the current Fibonacci number in column A of the active worksheet 'in the row with index i Cells(i , 1).Value = iFib 'calculate the next Fibonacci number and increment the element position index by 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub
In the given example, the condition iFib_Next < 1000 checked at the beginning of the loop. Therefore, if the first value iFib_Next If there were 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 loop. In this case, the loop will be executed at least once, regardless of whether the condition is met.
Schematically, such a cycle Do while with the condition to be checked at the end will look like this:
Do ... Loop While iFib_Next < 1000
Цикл «Do Until» в Visual Basic
Cycle Do Until very similar to cycle 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 next procedure Sub using a cycle Do Until retrieve values from all cells in a column A worksheet until the column encounters an empty cell:
iRow = 1 Do Until IsEmpty(Cells(iRow, 1)) 'The value of the current cell is stored in the array dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop
In the example above, the condition IsEmpty(Cells(iRow, 1)) located at the beginning of the structure Do Until, so 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 that the loop 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))