Excel - For Loops in VBA

For those familiar with Excel functions, the concept is not new. However, not everyone is versed in utilizing the advanced Developer feature to program functions that operate within Excel. By employing this tool, you can swiftly accomplish tasks that would be challenging or even impossible to do manually.

Guide on How to Use For Loops in VBA

Step 1: To enable the Developer feature in Excel, go to File > Options:

Next, navigate to the Customize Ribbon tab, check the Developer option, and click OK:

Step  2: In this article, I'll guide you through 2 tasks: Numbering from 1 to 10000 in column A starting from cell A5 and Printing 'Hello World' 10000 times in one Excel column, which is column B starting from cell B5. To do this, go to the DEVELOPER tab on the toolbar and click Visual Basic:

Now, a VBA programming tool will open. Double-click the sheet you are using to open the page that allows For loop execution commands:

Step 3: Input the For loop code as shown:

Sub OrderSequence()

   Dim i As Integer
   For i = 1 To 1000
   Cells(i+4, 1) = 1
   Next i

End Sub
 

In this context:

- The line Sub OrderSequence() is used to declare a function named OrderSequence.
- The line Dim i As Integer is used to declare a variable i with the data type integer.
- The line For i = 1 To 10000 declares a loop to be executed with the value of i ranging from 1 to 10000.
- The line Cells (i+4, 1) = 1 represents the content of the For loop, meaning: For each value of i in the loop, cell A(i+4) will be assigned the value of i.

Please note, in this example, I am printing numbers from cell A5 instead of A1. So, in the For loop, I enter the code as i + 4. However, if you print from cell A1, you just need to enter the code as Cells (i, 1) = 1.

- The line Next i is the statement allowing the variable i to move on to the next value.
- The line End Sub is used to conclude the OrderSequence function after it has completed with the value i = 10000.

Step 4: After inputting the code accurately, click on the icon of the Save button as shown. In the prompted dialog, choose Yes:

Step 5: To execute the entered function, click on the triangle-shaped button in green color, as shown in the picture:

In the new window that appears, select Run:

Now, cells from A5 onwards have been sequentially numbered from 1 to 10000:

You can verify whether the value 10000 has been accurately printed by typing into the A10004 cell to navigate directly to this cell. The result below demonstrates that the function has correctly printed 10000 values from 1 to 10000:

Step 6: To print 10000 instances of 'Hello World' in column B starting from B5, insert the following code snippet into the OrderSequence function:

In this case, the line Cells(i+4, 2) = “Hello World” serves to indicate: For each value of i in the loop, cell B(i+4) will be assigned the text segment Hello World.

After entering the above code, click on the Save button as before, and in the prompted dialog, select Yes:

Next, click on the triangle-shaped button in green color as shown above to execute the program:

In the new window that appears, select Run:

Now, from cell B5 onwards, each has been assigned the text Hello World:

You can verify whether the text Hello World has been accurately printed by typing into the B10004 cell to navigate directly to this cell. Here is the result demonstrating that the function has printed correctly:

Now that you've grasped the usage of For loops in VBA! This For loop empowers you to tackle various challenges in your work. It's exceptionally useful and will save you a considerable amount of effort and time compared to manual methods.