VBA – For Next Loop & Nested Loops

We are Covering below Topics:  

1. What are the Loops and Why do we need them in programming?

2. For Next Loop with different solutions

3. Which solution is the best one?

4. Art of writing Nested Loops & Fundamentals behind it.

---

Let us Begin:

1.What are Loops & Why do we need them?

If I want to repeat a task again and again with different inputs but with a same pattern, I go and use the loops, Be it any programming language, Loops are always there. Suppose, I tell you to open an excel and get to the first sheet and write a word “Hello” vertically or horizontally or diagonally up to 60,000 cells or maybe more than that. Now, What will you do in this case? 

Are you going to write Range(“A1”).value = “Hello” and then Range(“B2”).value = “Hello” and so on? This way you will have to write 60000 vba lines which is impossible. Yeah, you heard me 🤣🤣. No one is going to spend this much time even beginners will hate to do so. They will leave VBA and look at other options. hahaha… So, the point is we can create a loop where we just need to tell the Loop that runs the program 60000 times and every time you run, take different rows and column numbers and keep printing hello word. So, Loops perform repetitive tasks. It is a game-changer thing in any programming language and no language can ever be made or exist without them. Just like, I don’t exist anywhere without your love and support. Thank you! You have given me so much love and appreciated my courses that made me think that why not I should write articles for you. Sometimes we don’t have time to watch videos or even access to youtube videos and we prefer to read. Before, I get more emotional let us come back to the topic ❤️

In this blog post, we’ll take a deep dive into the power of Loops by taking up various case studies and see how we can implement the real data in Excel.

---

2.What is a For Next Loop?

For Next Loop is a loop/code that helps programmers do things over and over again without going crazy. It’s like having a robot that can repeat a specific task a certain number of times, making life a lot easier. “For” keyword is always closed by the “Next” keyword. Without Next, we cannot run a program. I will explain this in a while.

Below are some examples where we will learn how to write a For loop with very basic examples.

---

A.) Scenario for you.

Remember, I asked you to write some value on an excel sheet in VBA. Let us repeat that. Imagine, you need to write 1 to 4 numbers in excel cells from A1 to A4. (It can be millions).

Someone who does not know loops will write the code like this. See below image. Our Code goes to each cell one by one and writes the numbers like 1,2,3,4. Correct!

As I said, we will never prefer this way especially if we have to print millions or thousands of numbers. So, Now the question is how to turn this code into Loop and what are the benefits of the same?

Now, time to bring the For Next Loop: Pls give standing ovation to it 👏👏👏👏👏👏

(See how we are replacing the above code with For Next Loop)

Let me explain to you line by line. Sit tight and bring your cup of coffee and maybe a notepad. I use this approach when I read something useful and new.😊.First of all, we need to decide how many times our loop should run. In this example if you see we are running the loop from 1 to 10 which means it is going to run 10 times. 

For Next loop always needs to know the number of times it is supposed to run and we tell the loop by simply assigning a value to a variable. See below line, I’ve written separately.

    [For I = 1 to 10]

This variable can be anything. It does not matter. But what you write should be the correct syntax. Here 1 to 10 means Loop is going to run 10 times. We can change the value 10 to anything depending on the need of the work.

Now, when we say Loop should run 10 times, what exactly is it going to run? A valid question. Right? If you are thinking this, trust me you are simply awesome and if you are not ,you are double awesome. 😂😂

Can you see in the code what is written between For and Next keywords. That is our code and that is what we’re going to run 10 times. Below is a line which is written between For Next words.

  [Range(“A” & I).Value = I]

Now, if you see we are using the “I” variable in three places.

1. In telling the Loop that run 10 times [ For i = 1 to 10]

2. In deciding which cell to go on [Range(“A” & I)]

3. In printing the numbers on the cells [See 4th line from right side, it is  =I]

Explanation of the code line by line:

i.) First “I” variable will have a value as 1 which goes into syntax Range(“A” & 1).Value = 1 . So, what do you see here. It is very simple that in A1 we are going to print 1. Because A is fixed and Variable i is 1 so when you join them using & operator it becomes A1. Are you with me? Correct!

ii.) Now “Next I” is a line which will push or increment the value of “I” by 1 (By default Next always increases the value by 1 though we can increase the increments.)

iii.) Since Loop is not going to stop until it reaches a value 11,code moves back to the For line and this is where variable “I” will have a new value as 2.

iv.) So, now the same line/syntax has become Range(“A” & 2).Value = 2. So, a number 2 will be printed on A2 Cell.

v.) Are you getting the logic here? Every time “I” is changing , it is changing the Column A cells position like A1, A2, A3, A4, A5 ,A6 ……A10. And the value which is printed against these Cells is 1,2,3,4,5,6….10. 

vi.) When I reach 11 , code will automatically exit the For Loop because it follows the user or our instructions.

---

B.) Incrementing Values

We’ll understand what is the role of “Next” keyword in a For Next loop

“Next I” 

In for Loop, the job of “Next” keyword is to increment the value of a variable which is being in use. As mentioned before, “Next” is a VBA reserved word and it is by default is going to increment the value of a variable by 1 .For example, in this loop “i” starts with 1 and when code reaches the “Next” line, it gets an increment of 1. (i = 1+1) where 1 was the initial value. This increment happens automatically because you are saying go to next by using a “Next” keyword.

Next time, when i is 2 then next “i” in the background will be = 2+1 ( This is just to make you understand. So, to summarize “Next” increments the variable till it does not exceed its target. The target user has set up in the “For” line which means that you are supposed to run code this many times like 10,100,1000,10000 or whatever but not infinite. 😂😂

---

C.) For Next (Another example with different approach Line by Line)

 (Print numbers using Active Cell Method)

In this example, we use the ActiveCell method to move down the cells. Before we enter into the loop area, we are making sure we should be on A1 Cell. This is very important. I am explaining now.

The biggest difference between this approach and previous is that here we are not using Variable “I” to print values and also to move the location of cells. Remember, our previous approach? Let me paste the code from the previous example. Why do you bother yourself to scroll up to see the code. 🙂

Range(“A” & I).Value = I  (See here Variable I was joining hands with “A” and moving the cell one by one like A1.A2,A3….Got it?

We have hired two more variables for this. A Variable “printval” is the one which is printing the values in excel cells and surprisingly there is no variable to move the position of a cell. This is what you need to understand. We are using activecell.offset command and it is moving the cell from one row to next. If you know offset function in excel, you find this so easy.

Offset means moving from one cell to another by knowing how many rows it has to move up/down from activecell and how many columns left/right from an activecell need to be moved.

“Activecell.value = printval”

This line means that wherever you are on excel sheet( here it is A1 Cell),you need to write the value of the “printval” variable which is 1 at this time because we have allocated it a value as 1. So, our first value is printed 1 in A1 Cell. So far so good.

“Activecell.Offset(1,0).Select”

This line will move the position of the cell from A1 to A2. Offset(1,0) means 1 row down and no changes in column position from activecell. Offset has two parameters , row and column.

“printval = printval+1”

Now, when the loop is going to execute the code a second time,what do we want to write in A2 Cell. We need 2 , correct? So, we are increasing the printval variable value by 1.This line is actually printval = 1+1 (It already has a value of 1 from the above line, Correct?).So, this is where we are making sure that before we run the loop  next time, we should have what we want to write or publish in the cell.

Rest, the process is the same. Fundamentals are the same.You already know this loop. So,now you have seen the difference between these 2 approaches.They are so different but yet same results.

Now, let me become a face reader and answer the questions that you are having in your mind.😊😊. Sorry, I cannot see your face but still i know what you thinking 🙂 

---

3. Which approach should I use in Loop? Active cell or the previous one(Range one)?

“Range(“A” & I).Value = I”  (First approach example)

“Activecell.Offset(1,0).select”(2nd approach example)

See, in the first line “I” is a variable which is starting with 1 and it keeps incrementing the value because it is used in a for loop so we need not to do anything extra when it comes to moving the cell and printing the value. So, If i can manage multiple things using one variable, i will prefer that because for me less variable means less headache and less monitoring in local window. Speed can also be better but such things matter if you are writing a very big program otherwise it’s on us. We can choose whatever suits us. For optimal performance, it’s recommended to directly reference cells using the Range or Cells method:

---

One more great example to achieve same results:

I will not explain much here since we have discussed so much already but wanted to show this example. Here, we are using three variables. ”I” Variable for a loop, “MYROW” for row movement and “printval” for a value you want to write in a excel cell.

Here, we have to increment MYROW Variable manually because they have no connection with a variable “I”. So, every time as we cross the “Next” keyword, we will have “printval” and “MYROW” incremented by 1 to its previous value. So, Loop can be written in many ways. I thing for beginners there is nothing like bad loop or good loop as long as it is giving the results. Such bad and good or i should say fast or slow loops are a technical thing and this comes with experience. So, You should be happy if you are able to create loops instead thinking my colleague has created a better loop structure. You do not know his background or if he/she has already spent a lot of time in loops. Just, be happy with your results and rest keep working. Time/Experience is a big teacher and it will teach you everything.

Conclusion

VBA loops offer a powerful way to automate repetitive tasks in Excel or even outside Excel environments like with Outlook, Powerpoint,Databases,Folder objects. These Loops can save your time and effort in your data related tasks. There are different ways of writing the loops but we should choose the ones which are faster and also easy to maintain.

---

4.Nested Loops or Sub-Loops (This is an Art 😊)

A.) By Definition:

In VBA, nested loops are a powerful tool or code that allows developers to repeat actions to do complex tasks in a structured and easy way. Nested loops involve putting one loop inside another, making it possible to do more difficult tasks. In this section, we’ll take a closer look at what nested loops are and explore them through two simple VBA code examples.

A nested loop is made up of an outer loop and one or more inner loops. The outer loop controls how many times the inner loop (or loops) runs, creating a structured way to perform repetitive tasks. Each time the outer loop goes through one cycle, it triggers the inner loop(s) to do their thing.

Note: Inner Loops are first executed and then outer loops.

Code: 

Let’s start understanding the code line by line. This is going to be great source of knowledge for you and enjoy this ride. Learning should not be a target. It should be journey which should never end.

i.) There are two loops that are going to run but we need to understand the system. First, VBA will decide that it is supposed to run the loop thrice because code sees the variable “I” has a value till 3. So, it is going to execute the code thrice. Correct! We shall see a message box with a value of 1.

ii.) Now, after this message ,VBA finds another loop which is called a subloop or nested loop. As a rule, VBA will now start working on this loop and it is going to run the loop till it is not finished which means till the time it does not loop through the number of times it is asked to. The Variable “j” will have values 100,101,102,103 through the message box. So, now we will see four message boxes one by one. Correct?

iii.) Now, the moment variable “j” has 104, VBA will exit from this loop and go to Next I. We know the I variable till now has just a value of 1 so VBA will go back to the outer loop and now the message box again will for  variable “I” which this time is 2.

iv.) Again, VBA will find the same subloop which is using a variable “j” so, again the same four numbers will pop up in the message box.(100,101,102,103).

By now, I am sure you must have understood the pattern/logic. This process keeps running till we will not have variable “I” as 4 because that is the time when VBA will exit the loop and naturally, if outer loop has exit, nothing inside it will run. So, it means inner or Sub-Loops run till the time outer loop runs.

v.) Let us print the answers in a sequence which will come after finishing this code. 

    1,100,101,102,103 – in first run   [I = 1,  j =101,102,103,104 ..now exit “j” loop]

    2,100,101,102,103 – in second run   [I=2,  j=101,102,103,104..now exit “j” loop]

    3,100,101,102,103 – in third run   [I=3,  j=101,102,103,104..exit ” j” loop..& finally Exit ” I” loop as well]

Let’s summarize:

• Outer Loop (For I = 1 To 3):

• • Repeats three times, showing a message box with the current value of I but each time I value pops up ,before it pops up a second value , subloop variable j will first popup till it does not exit.

• Inner Loop (For j = 100 To 103):

• • Runs four times for each cycle of the outer loop, showing a message box with the current value of j.

This example demonstrates how the outer loop runs three times, and for each run, the inner loop runs four times.

---

B.) Nested Loops(Example 2):

Now, let’s take a look at a bit more complex example with three nested loops.

Explanation:

• Outer Loop (For I = 500 To 499 Step -1): Step-1 means loop will run in reverse direction. Here, it is 500,499 – two times

• Goes backward from 500 to 499, showing a message box with the current value of I which are first 500 and then 499. But in-between, Middle loop will run which has variable “j”. Remember, inner loop are executed first and here Variable “j” one is the inner loop for variable “i”

• Inner/Middle Loop (For j = 1 To 2):

• Runs twice for each cycle of the outer loop, showing a message box with the current value of j which are 1 & 2.

• Innermost Loop (For K = 20 To 23):

• Runs four times for each cycle of the middle loop, showing a message box with the current value of K and then goes back to inner loop(The “j” one)

I am writing the answers in a sequence per code which has popped up via message box when we ran the code. (This will help you so much)

  500,1,20,21,22,23,2,20,21,22,23 –        in first go… Let me show you step by step how values will come in message box. Be Ready!

  I = 500         ‘First message box in a code is having a variable “i”  so here its 500. Recheck the code for clarity, if any confusion

     j =1          ‘ Here Loop with variable J will trigger, remember it is the middle loop or inner loop of “I”

       k = 20,21,22,23    ‘Now exit “k”    ‘ while working with “j” loop vba finds another loop which is the innermost loop of variable “k” so this is priority now.

     j = 2       ‘because j is supposed to run twice so now its 2  ‘ after we are done with k loop , VBA triggers the j loop again as it has still a scope to run.

          k = 10,20,21,22,23          ‘now again exit k    ‘ again, innermost loop will start just like it had run when j variable was 1. You know now j is 2

    Exit j                ‘(because j is 3 by now)    ‘ Now J was supposed to run twice as per our code so J loop has exit here.

  499,1,20,21,22,23,2,20,21,22,23 – in second go, these are the values we will have. Let me show you line by line this entire process

 I = 499        ‘ see now I is reduced from 500 to 499 due to step-1, if you remember.

   j =1

    k = 20,21,22,23       ..now exit “k” Loop

   j = 2        (j is supposed to run twice)

    k = 10,20,21,22,23         ..now exit k…

  exit j     (because j is 3 by now)

 exit I      (I has reached its value 499 so finally all loops are finished)

No need to thank me for this incredible explanation. its ok. It’s my job to provide you the best knowlege. 😍😍😍😍. You have supported me so much over these 12 years. A token of love for you. if you like to share any feedback please leave a review here. Your reviews make me grow and inspire me.

This example showcase the flexibility of nested loops, allowing for different iterations at each level. The outer loop goes backward, the middle loop runs twice for each outer loop cycle, and the innermost loop runs four times for each middle loop cycle.

Conclusion:

Nested loops are very powerful and intelligent way in VBA or any programming world because it offers a structured way to handle repetitive tasks. Whether you’re working with data, doing calculations, or implementing specific actions, nested loops provide a easy solution. Understanding how to use them effectively can help you in solving complex problems. Of course, this takes some weeks of practice. Nothing is achievable without hard work and those who say its a minute job to learn and write a code , i think they are super human beings. I took a lot of time to learn VBA but now I can do anything.

---