Interact with Spreadsheets Using a VBA For Loop

The For Loop is the most basic iterative loop, permitting a block of code to be executed for a specific number of times. Nested within a for loop, scripts can help input, modify, or remove data from spreadsheets.

Overview

This post within the Getting Started with VBA series begins the focus on Loops in VBA.

In its most basic form, a For Loop is a control flow statement which allows a developer to execute a block of code a certain number of times. On its surface, this may not look like a frequently needed feature or tool, but drilling down further, we realize that the repetition of code allows us to accomplish many tasks. Some of these tasks–many specific to Excel–are as follows:

  • Automatically filling out data on a spreadsheet.
  • Checking for errors on a spreadsheet.
  • Reading, writing, or updating arrays.
  • Iterating through items within various controls, such as a listbox.
  • Providing various countdown, or general count features.

Basic Structure of a For Loop

Control flow diagram representing a simple For Loop.
Control flow diagram representing a simple For Loop.

A For Loop is one of the four loop types commonly used in VBA. Others loop types include For Each, Do While, and Do Until — to be discussed in the next few post of the series. The distinction of a For Loop when compared to the other types is that the loop statement contains a counting mechanism which is set prior to entry to the loop. In other words, the number of iterations that the loop will execute is known prior to entering the loop.

Given its nature, a For Loop always relies on a numeric counter to determine the loop’s condition state (True or False). A For Loop can never test if a value is null, False (as a Boolean variable), or test against a string variable.

A Simple For Loop

This example shows how basic a For Loop can be. This script–when executed–will show a message box 3 times, each counting from 1 to 3. Breaking down the For Loop statement into pieces, we see that the initial For Loop contains three parts. First, the Initialization statement “For i = 1” assigns the initial value to variable i, which is 1 in this case. if i was assigned integer 0 (zero), the script will be executed 4 times. If i was assigned the integer 2, it would only execute twice. Second, the Condition statement “To 3” specifies the condition for which the loop should still execute. In this case, the statement says the loop should continue iterating until i is equal to or less than 3. Third, the Afterthought “Step 1” specifies what the loop should do after the iteration in executed. In this example the statement says that it should add 1 after the iteration. If the value was “Step 2”, the code would only execute twice, as the sequence would be 1 and 3. Keep in mind that the Afterthought statement is optional, and VBA by default will iterate through by adding 1 if the Step statement is absent. It was included in this example for demonstration purposes.

The Afterthought statement is most useful for situations where increasing the counter by 1 for each iteration will not suffice. The following is an example of the same script, with the minor difference of counting down from 3 to 1.

In this example, the Afterthought statement is required as the script needs to override VBAs default behavior of iterating up.

Using For Loops in Spreadsheets

In most situations applied to spreadsheets, you will want some changeable value or variable in your block of code within a For Loop to provide the user with dynamic content. This would be as opposed to providing the user with the exact same block of code presenting static content over and over again. In the previous example, the variable i held two roles. First, it would act as the counter for the For Loop statement. Second, it would act as the content of the message to be provided to the user (simulating a count).

One of the most fundamental uses of a For Loop within a spreadsheet is its ability to iterate through rows and columns to print, read, or test cell content. In the following example, a For Loop is uses the Range object to print the value “Test” through the first 10 rows of a spreadsheet.

Continuing the idea from the first example, the block of code utilizes the i variable (also used by the For Loop statement) to specify the row for printing.

A For Loop counter can also be used to move through spreadsheet columns, using the Cells property.

In many cases, the row or column you want to iterate through may not be equal to the loop counter. This can be managed by either modifying the initiation and condition statements to fit within that range, such as “For i = 10 To 20”, if you want the script to begin in row/column 10 and go to row/column 20. Perhaps your script is a bit more complicated — to improve readability, you may want to introduce a completely new counter variable to separate dynamic content from the loop’s operation variable, such as the following:

You will notice a few differences in this code. First, the variable j is declared as a separate integer to specify the cell row. Second, the variable j is assigned the integer 10, signifying that it will start at row 10. Third, the variable j has its own counter within the For Loop that adds one to each iteration.

Note that the increment operator present in many other programming languages (i++, j++), is not understood by VBA.

Other Notes

  • While better suited for a Do Loop, infinite loops can be executed by specifying an Afterthought statement such as “Step 0”. For example: “For i = 1 To 2 Step 0”.
  • The counter variable can be assigned a new initial value and reused in subsequent For Loops. However, this may hinder readability and is discouraged.
  • Within a For Loop, the “To” behaves similar to the “Greater Than or Equal To” operator in a conditional statement.

 

Leave a Reply