Visual Basic is characterized by using structured programming in the code with the aim of improving its quality and readability. Structures like for – next allow you to define the number of times a statement is executed. The for-next loop can be used in VBA Excel to repeat an action for a given time.
For example, the for next loop can be used to start numbering from a chosen starting point to a limited ending value. To be fulfilled, several conditions can be assigned.
What is structured programming?
Structured programming is a programming paradigm that uses segments of code that have an entry point and an exit point. Subroutine functions and control structures are used.
All this is done with the intention of having a more organized and easier to read code. This type of programming uses loops, which are sequences that are repeated N number of times. The loop repeats until a condition is met or until a final number is reached.
How to make and use a for-next loop in VBA?
The for-next loop is one of the most used types of loops in VBA because of its great utility. Its syntax is easy to understand:
for start_variable to limit_value
Instructions inside the loop
next start_variable ‘increments the variable and end of the statement
start_variable: It is the variable that contains the initial value. In general, before starting the loop, a value is assigned to the variable and its syntax would be as follows:
start_variable = 1
That value increases as the loop conditions are executed, until it reaches the final value.
value_limit: This variable is the one that starts the sequence and counts the repetitions. If the start variable reaches the limit value then the loop stops. It can be a variable with a value assigned before the statement or it can be a direct value. Example if we want the start variable to count only up to 10, the syntax would be as follows:
for start_variable to 10
next start_variable: is the last statement line of the loop instructions. The word next followed by the variable name increments its value by one.
for-next loop example in VBA
The for-next loop is very easy to use in Visual Basic and can be used with many Excel objects. A form with two text boxes was created for the user to place the start value and the end value of the loop. In the form there is a button that when pressed fills a combo box. The code assigned to the form and the button would be the following:
Dim i As Integer
Dim lim As Integer
Private Sub btnCount_Click()
lim = limit.Text
For i = start.Text To lim
result.AddItem i
Next i
End Sub
- The first two lines of code is the creation of the variables i and lim as a numeric value of type integer.
- Then when clicking on the button, the value should be assigned to the variable lim dependent on the limit value that the user placed.
- Begins the for-next loop where the value of i is equal to the starting value that the user put in. That value will increase until it is equal to the value of lim.
- Each time the loop executes a new value is added to the combo box. Finally, the variable i increases its value + 1 when the instruction ends.
Do-Loop in VBA
There are two types of Do-Loop structures that are Do-While and Do-Until, similar to the While loop. The Do-While repeats the instructions as long as a condition is met, that is, its value is true. Unlike Do-Until, which is fulfilled until the condition changes its value to true, that is, true.
The syntax for both structures is the same, what varies is the word while for until.
Do while condition ‘while can change to until
Instructions inside the loop
exit do
loop exit instructions
loop
