What is structured programming?
Structured programming is a programming paradigm that uses a segment of code with an entry point and an exit point. They are used functions of subroutine and control structures.
All this is done with the intention of having a more organized and easy 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 a final number is reached.
How to create and use for-next loop in VBA?
The for-next loop is one of the most used loop types in VBA due to its large usage. Its syntax is easy to understand:
for start_variable to limit_value
Instructions within the loop following start_variable
'increment the variable and the end of the statement
start_variable: 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:
initial_variable = 1
This value increases as the cycle conditions are executed, until it reaches the final value.
limit_value: This variable is the one that starts the sequence and counts the repetitions. If the start variable reaches the limit value, the cycle is aborted. 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 the following:
for initial_variable to 10
next start_variable: is the last line of the statement of the loop. The next word followed by the variable name increases its value by one.
Example of a for-next loop in VBA
The for-next loop is very easy to use in Visual Basic and can be used with many Excel objects. A form has been created with two text boxes in which the user can enter the start value and the end value of the loop. In the form there is a button which when pressed fills a combo box. The code assigned to the module and button would be as follows:
Dim i As Integer
Dim lim As Integer
Private Sub btnContar_Click ()
lim = limit.Text
For i = start.Text To lim result.AddItem
i
Next i
End Sub
- The first two lines of code create the variables i and lim as an integer type numeric value.
- Then, by clicking on the button, yes assigns the value to the variable lim in function of the limit value that the user has set.
- Start the for-next loop where the value of i is equal to the initial value entered by the user. This value will increase until it is equal to the value of lim.
- Each time the loop runs, a new value is added to the combo box. In the end, the variable i increases its value + 1 at the end of the instruction.
Do-Loop in VBA
There are two types of Do-Loop structures which are Do-While and Do-Until, similar to the While Loop. The Do-While repeats instructions until a condition is met, that is, its value is true. Unlike Do-Until which is satisfied until the condition changes its value to true, i.e. true.
The syntax for both structures is the same, what varies is the word while for up.
The Do while 'while condition can be changed to up to
Instructions within the loop
Exit do Esci
from the instructions from the loop
loop
