When executing a VBA program (VBA, Visual Basic for Applications), you may need to repeat some code fragment several times. There are cycles to accomplish this task.
The syntax of the Visual Basic language provides for the following types:
- For.
- Do While.
- Do Until.
Let’s analyze the differences between them in detail.
Cycle of type For
There are several distinct types of For loop. There is a type For… Next и For Each.
For… Next
Cycle For… Next relies on a variable that increments at the end of each iteration within a certain range. And every time a certain code is executed. The easiest way to depict the principle of this type of loop is on a light code snippet.
For i = 1 To 10
Total = Total + iArray(i)
Next i
In the example above, the loop defines a variable that increments the number it contains by one after each execution of the piece of code (this is called an iteration).
There is no step size specified here, so the loop uses the default size 1. Iterates as many times as necessary to make the value go from 1 to 10. But if you need to use different step sizes, you must use the word Step. Let’s give an example for clarity.
For d = 0 To 10 Step 0.1
dTotal = dTotal + d
Next d
In the described version of the loop, the specified step size is 0,1, so the value of the variable d changes along the numerical series 0.0, 0.1, 0,2, and so on, until the value of the variable reaches 10.
You can also use negative numbers to set the step size, as demonstrated in this code snippet.
For i = 10 To 1 Step -1
iArray(i) = i
Next i
In this code example, the step size is -1. Therefore, the numerical series of the corresponding variable will be as follows: 10, 9, 8 – up to one.
Цикл For Each
Cycle For Each similar For Next, except that the latter iterates over a numeric series of values, while For Each performs the required sequence of actions for all objects belonging to a given group. For example, the following code shows how a user might apply a For Each loop.
Dim wSheet As Worksheet
For Each wSheet in Worksheets
MsgBox «Found Worksheet: » & wSheet.Name
Next wSheet
This code will be executed for each sheet that is part of the document that the person is working on.
Exit For construction
This statement is intended to terminate the loop before all iterations have been completed. Under certain conditions, the interpreter stops the loop and starts executing the line of code that follows it.
For example, if the task is to find a specific array value, you can use a loop to iterate through all its elements until it is found. If the required value is found, then there is no need to continue searching, so the loop ends.
Let’s look at another example that shows the work of the operator well. Exit For. Here the loop analyzes 100 objects, the interpreter compares each of them with the value of the dValue1s variable. If it is equal to dVal1, then the loop ends.
For i = 1 To 100
If dValues1(i) = dVal1 Then
indexVal = i
Exit For
End If
Next i
Do While loop
Characteristic feature of the cycle Do while – the execution of the code contained in it is carried out until the moment when the certain condition is true. This is well illustrated by the following Sub function example, where this loop is used to print the numbers in the Fibonacci sequence as long as the number in the variable is greater than 1000.
‘ Sub procedure to list the Fibonacci series for all values below 1,000
Sub Fibonacci()
Dim i As Integer ‘ counter for the position in the series
Dim iFib As Integer ‘ stores the current value in the series
Dim iFib_Next As Integer ‘ stores the next value in the series
Dim iStep As Integer ‘ stores the next step size
‘ Initialise the variables i and iFib_Next
i = 1
iFib_Next = 0
‘ Do While loop to be executed as long as the value of the
‘ current Fibonacci number exceeds 1000
Do While iFib_Next < 1000
If i = 1 Then
‘ Special case for the first entry of the series
iStep = 1
iFib = 0
else
‘ Store the next step size, before overwriting the
‘ current entry of the series
iStep = iFib
iFib = iFib_Next
End If
‘ Print the current Fibonacci value to column A of the
‘ current Worksheet
Cells(i, 1).Value = iFib
‘ Calculate the next value in the series and increment
‘ the position marker by 1
iFib_Next = iFib + iStep
i = i + 1
loop
End Sub
In this fragment, you can see that the condition is written at the start iFib_Next < 1000. If iFib_Next becomes greater than 1000, then the loop is automatically terminated.
There is another option: the execution of a sequence of actions at least once, despite the fact that the initial condition is false. To do this, the condition must be placed at the end.
Do
Loop While iFib_Next < 1000
Cycle Do Until
This type is reminiscent Do while, but here the code is executed until the condition becomes true. That is, in the previous version, the condition is initially true until, in the course of performing actions, it becomes false. Here it is the opposite. This is well illustrated by this example:
iRow = 1
Do Until IsEmpty(Cells(iRow, 1))
‘ Store the current cell value in the dCellValues array
dCellValues(iRow) = Cells(iRow, 1).Value
iRow = iRow + 1
loop
This subroutine retrieves the values of the cells in the same column until an empty one is found.
Just like in the previous example, if the condition IsEmpty(Cells(iRow, 1)) is placed at the beginning of the loop, it will only run if there is a filled cell.
If it is necessary to run the loop only once, even if the initial condition is not true, it must be placed at the very end.
Do
.
.
.
Loop Until IsEmpty(Cells(iRow, 1))
Loops are a powerful tool for automating repetitive actions. It can be used for programming a wide variety of tasks. This makes it much easier to work with large amounts of data.
To summarize briefly, the cycle The is used if you need to repeat a certain piece of code a certain number of times. Do while и Do Until used for similar tasks. In the first case, as long as the condition is true, the code will be repeated. In the second, the repetition will be performed until it becomes true.