What are the four types of loops used in Excel’s VBA?
Looping is a fundamental concept in programming that allows the programmer to execute a block of code multiple times without having to write the same block of code repeatedly.
Looping in programming is crucial in software development because:-
- Loops help to automate repetitive tasks. This makes code more concise and reduces redundancy.
- Loops ensure that the code is efficient, thus promoting scalability.
- Loops enable dynamic behaviour based on certain conditions.
- Writing loop based code makes programs easier to update and debug.
- Loops are essential for tasks like: iterations, probability simulations, file processing, and AI applications such as machine learning.
In this blog post I intend to discuss the four types of loops that are used in Excel’s VBA, being:-
- For loop
- For each loop
- Do while loop
- Do until loop
For loop
In VBA the for loop is a structured way to repeat a block of code a set number of times. The for loop is especially helpful in iterating through arrays, performing calculations, and automating repetitive tasks.
Below is a very simple piece of code where:-
- Define an integer, i.
- Create a for loop where i iterates through the numbers 1 to 5 and prints the value in the row number associated with i.
Below is a screenshot of the output of the for loop described above:-
VBA’s for each loop
For each loop is used to iterate over a collection of objects, such as ranges, worksheets, cells, arrays or other elements. It is particularly useful when working with dynamic datasets where the programmer does not need to track and index manually.
Get Crystal X’s stories in your inbox
Join Medium for free to get updates from this writer.
The code below is an example of how to use a for each loop:-
- Define a worksheet, sht.
- Define an integer, s.
- Initialise s to the value of 1.
- Create a for each loop where each sheet in the workbook is iterated through. The name of the worksheet is oriented in the cell that corresponds with s, and then s is incremented by 1 through each iteration.
Below is an example of the above code where I had created three worksheets in the workbook:-
Do while loop
In VBA the do while loop is used when the programmer needs to repeat a block of code while a certain condition remains true.
The code below is an example of how to use a do while loop:-
- Define the integer, j.
- Initialise j to the value of 1.
- Set up a do while loop that will check the value of each vertical cell in column 1 (A) to see if it has anything in it. As long as the cell has a value in it, the do while loop will set the value of the corresponding cell in column 3 (C ) as being the value of j plus 50. The integer j will then increment by 1.
The screenshot below is the output of the referenced do while loop. As long as there is something in the cells in column 1 (A), the do while the value of 50 plus the value of j in its corresponding cell in column 3 (C ):-
Do until loop
VBA’s do until loop runs until a specified condition becomes true. Unlike the previous loop that we covered, do while, which runs while a condition is true, do until keeps looping as long as the condition is false and stops when the condition becomes true.
The code below is an example of a do until loop:-
- Define an integer, a.
- Initialise the integer, a, as 1.
- Set up a do until loop that will run until a is greater than 5. The loop will place the value of a in column 4 (D) of the worksheet. Once the command has been executed, the integer, a, will increment by 1.
The screenshot below is the output of the above piece of code that has been discussed:-
Looping is a very important part of coding in any programming language that I can think of and that is why I went all out to describe the four kinds of loops that are used in VBA. I intend to use a looping mechanism in the subroutine that I intend to write for my day job, and I certainly will write about it when I have completed it.
I have created a code review to accompany this blog post and it can be viewed here:- https://youtu.be/Hn6az1_3FwM
