Loop structure in Visual Basic allows you to run the same code repeatedly. Sometimes we want a section of code to run more than once. Then, the loop structure run the code again and again until a condition is True or False. Maybe This concept is very abstract to you. So now the author will introduce in detail and take some examples.
Type of Loop Structure
There are 3 types of loop structures in VBA, they are:
- For Loop
- Do While Loop
- Do Until Loop
Now, let’s get to know each loop.
1. For Loop
The For Loop is the most commonly used loop type, you can write a loop that will be executed a specific number of times. For Loop has two forms:
a)For…Next
The For…Next loop can execute a piece of code in a specified number of times. The For loop uses a numeric variable. Starting from the initial value, the value of the variable increases or decreases every time it loops until the value of the variable equals the specified end value, and the loop ends. For example:
This code means calculate the cumulative sum of one to ten. If the value of Step is 1, then the step value can be omitted.
b)For Each Loop
For Each loop is used to execute all the elements in the data group one by one. If there is only one element, and the loop will exited and execution continues with the statement following the next statement.
The sh variable is the element variable, and Worksheets is the collection of all worksheets in the workbook.
Exit For statement
The Exit For statement is used to jump out of the loop process. It is generally used when ending the loop early. It is applicable to both For Next loop and For Each loop.
Let’s look at a practical example, when calculating the sum of 1-10 numbers, the loop will stop when the sum is greater than 30.
2.Do While Loop
The Do While Loop is allows you to execute a section of code while the specified condition is met. Do While Loop can be classified into Do While…Loop and Do…Loop While.
a) Do While …Loop
Do While… Loop, according to the value of the conditional expression after the While keyword, it executes when it is true and stops when it is false. The basic syntax is as follows:
As long as the [Condition statement] is true, it will continue to execute in a loop. [Condition statement] Once it is false, the loop stops and the program executes the code after the Loop.
Let’s Look at a practical example, find the cumulative sum of 1- 10.
The initial value of the i variable is 1. According to the conditions after While, as long as the i variable is less than or equal to 10, the subsequent code can be executed in a loop.
b) Do…Loop While
The difference from the previous is that the Do… Loop While loop executes the code at least once before judging the value of the condition expression. The basic syntax is as follows:
Exit Do statement
Similar to the Exit For statement, the Exit Do statement is used to jump out of the Do While loop.
3. Do Until Loop
The Do Until loop is similar to the Do While loop. The difference is that Do While continues to execute the loop when the condition statement is true; while Do Until stops the loop when the conditional statement is true.
Do Until Loop also can be classified into two forms. Do Until…Loop and Do…Loop Until.
a) Do Until…Loop
Judge the value of the condition statement after Until before the start of the loop, if it is true, stop the loop; if it is false, continue to execute the loop
b) Do…Loop Until
Run it once, and then judge the value of the condition statement after Until. If it is true, stop the loop; if it is false, continue to execute the loop.
To Sum Up
If you read this page carefully, then you have learned Loop Structure in Excel Visual Basic.
Otherwise we have learned two Exit statement.
Leave a Reply