If you need to reproduce the same task in your program (use the same part of the code), you will need VBA loops for this.
Cycle For
This cycle has two types:
- For…Next цикл
- For Each цикл
Цикл For…Next
This loop analyzes values from the specified range based on the given variable. The loop then applies the specified code to all values. How it looks can be seen in the example:
For i = 1 to 10
Total = Total +iArray(i)
Next i
This example sets the variable i to a value between 1 and 10 and shows that the loop needs to apply code for each of these values.
In the example above, we didn’t specify a loop step size, so the default step size, which is 1, will be used. But if you need to use a specific step to apply the loop, do it like this:
For d = 0 to 10 Step 0.1
dTotal=dTotal + d
Next d
In this example, because the step is specified as 0.1, the value of the variable d will be set from 0.1 to 10. Also, if necessary, you can use steps with negative values:
For I = 10 To Step -1
iArray (i) = i
Next i
Here the step is specified as -1, so the value of each step will decrease starting from 10 and going down to 1.
Цикл For Each
This loop is similar to the previous one, but instead of working with the entire range of values, For Each looks at each value individually. Here is a code example that tells the loop to enumerate all the worksheets in the document:
Dim wSheet As Worksheet
For Each wSheet in Worksheets
MsBox “Found Worksheet:”&wSheet.Name
Next wSheet
Team Exit For
This command is needed to end the For loop ahead of time. The command causes VBA to end the loop and move on to the next line of code. For example, when you are looking for some value, you can use a loop to check and find it among the data array. But once you’ve found the data you want, there’s no need to continue the search cycle, so you break the cycle.
Below we will give an example of using this command in a loop that looks through an array of 100 values and tries to find the value specified in dVal. The loop will terminate sooner if dVal is found:
For i = 1To 100
if dValues(i)=dVal Then
indexVal = i
Exit For
End If
Next i
Do While loop
This loop will repeat the specified code until all conditions are met. We will show how to write this loop correctly. Let’s take the Sub procedure code as an example. And the task of the cycle will be to find and display the values of the Fibonacci Sequence. And the condition is that the values should not be higher than 1000:
‘Sub procedure outputs Fibonacci numbers for all values below 1000’
Sub Fibonacci()
Dim I As Integer ‘element number in series’
Dim iFib As Integer ‘save current value in series’
Dim iFib_Next As Integer ‘save the next value in the series’
Dim iStep As Integer ‘save next step size’
‘Initialize variables i and iFib_Next’
i = 1
iFib_Next = 0
‘Do While continues to execute until the value of the effective Fibonacci number exceeds 1000’
Do While iFib_Next<1000
If I = 1 Then
‘Special case for first value in series’
iStep = 1
iFib = 0
else
‘Save next step size before changing current value’
iStep = iFib
iFib = iFib_Next
End if
‘Give the current Fibonacci value in 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+Step
i = i +1
loop
End Sub
Here iFib_Next<1000 counts as the beginning of the cycle. If this value is greater than the specified value, then the cycle will not start at all.
Another way to use Do While is to specify conditions at the end of a loop. In this case, the loop will run at least once, regardless of whether the conditions were met or not. The example below will show the location of the conditions at the end of the loop:
Do
.
.
.
Loop While iFib_Next<1000
Cycle Do Until
Its functionality is similar to the previous one. The essence of his work is that he constantly executes a part of the code until he meets the given value. We will show how this loop looks in the Sub procedure. The task of the loop will be to extract the necessary data from the cells of column A. The condition for terminating the work will be the moment when the loop stumbles upon an empty cell.
iRow=1
Do Until isEmpty(Cells(iRow,1))
‘Save the current value of dCellValues’
dCellValues(iRow)=Cells(iRow,1).Value
iRow=iRow+1
loop
Here isEmpty(Cells(iRow,1)) is the condition and is at the beginning of the loop, so the loop will only run if the first cell checked is full. But there are times when it is necessary for the loop to run at least once, regardless of the initial conditions. Then the condition must be specified at the end:
Do
.
.
.
Loop Until isEmpty(Cells(iRow,1))