VBA MODULE 8
Program flow Control and Decisions with Loops
Looping means repetition. It is useful when we need to execute the same code numerous times until it meets an end condition.
In VBA we will find several control system which helps us to perform looping for complex programming.
In VBA, there are four types of loop
- The For Next Loop
- For Each Next Loop
- The Do While Loop
- The Do Until Loop
For Next Loop
Commonly used loop is For Next Loop. We always need to declare a variable to assign a range for our loop. The VBA code within the For Next statement is then executed for each value in the range.
To make it very clear let’s take an example to add numbers from 1 to 100 and display the total on the screen
Dim Range as Integer
Dim Total as Integer
For Range =1 to 100
Total = Total +Range
Explanation of the above example:
- We started with the Subroutine named “adding”
- Dim is used to declare variables Range and Total.
- In the For statement we declare a range for loop using Range variable
- Total variable will store the value every time the loop runs (till 100 times)
- Next will help to take the next value of Range
- After the loop executes 100 times it will end and Total will be displayed on the screen using msgbox
Nested loop is loop within the loop.
Check the below example of nested For loop which will put same value to all the cells of the defined range
Dim row As Integer, col As Integer
Result when you run the above program:
The For Each Next Loop
The For Each Next loop is used when there is a need to loop through each object in a collection of objects. So instead of running through a set of values for a variable, it runs around the collection of objects.
This loops through each cell in the Range("A1:A5"), and the code sets font color of red in each cell. Here, Cellvalue is the Range Object variable, and the group or collection are all Cells in the Range("A1:A5").
Dim Cellvalue As Range
For Each Cellvalue In ActiveSheet.Range("A1:A5")
Cellvalue.Font.Color = RGB(255, 0, 0)
Do While Loop
In Do While loop you don't need to set a start condition, just the end condition. It continues until a specified condition is met.
The following example uses a Do-While loop. This code uses the active cell as a starting point and then travels down the column, multiplying each cell’s value by 2 and showing the result in the next column using offset property. The loop continues until the routine encounters an empty cell.
Do While ActiveCell.Value <> Empty
ActiveCell.Offset(0, 1).Value = ActiveCell.Value * 2
Do Until Loop
The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A program continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the program executes the loop until the condition is true
This code color the cells yellow, until a empty cell is encountered.
Dim rowNo As Integer
rowNo = 1
Cells(rowNo, 1).Interior.Color = RGB(255, 255, 0)
rowNo = rowNo + 1
Loop Until IsEmpty(Cells(rowNo, 1))