Excel VBA Loop

What is loop?

Loop is one of the most interesting pattern in excel VBA. It let you run the same code multiple times depending on the range you input.

There are three types of loops: For Loop, Do While Loop and Do Until Loop.

1. For Loop 

   For loop has two kinds also: For Next Loop and For Each Loop.

   a. For Next Loop

      This is one of my favorites. See the pattern below.

            

                For x = startRange to EndRange

                         

                          my code here with x changes from startRange to EndRange

               Next x

Example 1:      Let's try using cells in excel.

      

      

    Try to type the code above in your vba editor. Your output should be like this

           

  I expect you knew already what is meant by Dim. If not you can check this article again.

  

        For x = 1 To 10

      Your code will run 10 times.

      If it is For x = 3 To 10, then your code will run 8 times.

        

       Cells(x, 1).Value = x * 2

     Cells is equal to Range

     Usually we use Range when you have constant range such as Range ("A1").

     Whereas Cells is for changing range. 

     The syntax for cells is:  Cells (row,column).

    Cells(x, 1)

       So your ranges are :

            Cells (1,1)  or Range ("A1");

            Cells (2,1)  or Range ("A2");

            Cells (3,1)  or Range ("A3");

                  until

            Cells (10,1)  or Range ("A10");

         

      x * 2  

        1*2 = 2

        2*2 = 4

        3*2 = 6

          until

        10*2 = 20

     Value from the right will be given to the left.

     Therefore, whatever the answer of x*2 is put in cell range.

Example 2:      Let's try using Message Box.

 

                   Try to type the code above in your editor.

         As you can see, I declare myDisplay as Variant. 

         Variant accepts string or integer. I often use the Variant since it's very convenient and safe for me.        

         If you want Messagebox, you can use Msgbox.

         If you type this, function will show also to guide you what to type.

          The msgbox will display depending on the value of x  

Homework
Your output should be like this.Like Multiplication table.

Hint
1. Make a loop where in For Loop is inside Another For Loop
2. Make two variable, one is from top to bottom (1 to 10), then the other is from left to right (1 to 10).

You gave up? Here's the answer.

         

Next For Each In Loop.