Loop in Excel VBA

Loop in Excel VBA

Loop is one of the most effective and powerful technique that requires precision and a clear understanding. A minor mistake in the loop code can cause unrecoverable damage to your code. A minimum effect of a Loop mistake is to activate an infinitive loop.

A loop in MS Excel VBA enables you to loop through a range of cells with just a few lines of code.

Single Loop

In single loop, a loop is applied to a one-dimensional range of cells. To get a better understanding as to how the Single Loop works, draw a command button on your worksheet and add the following lines of codes:

Dim intCell as Integer
For intCell = 1 To 5

Cells(intCell, 1).Value = 100

Next intCell

Now, click the Command button and you will observe the following result:

Explanation: The lines of code between For and Next will be executed five times. For intCell = 1, VBA enters the value 100 into the cell at the intersection of row 1 and column 1. When the program reaches to Next intCell, it increments intCell with 1 and jumps back to the For statement. Now, for For intCell = 2, VBA enters the value 100 into the cell at the intersection of row 2 and column 1, and so on till intCell completes 5 cycles. 

Double Loop

In double loop, a loop is applied to a two-dimensional range of cells. To get a better understanding as to how the Double Loop works, draw a command button on your worksheet and add the following lines of codes:

Dim intRow As Integer, intCol As Integer

For intRow = 1 To 5

    For intCol = 1 To 5

        Cells(intRow, intCol).Value = 100

    Next intCol
Next intRow

On clicking the command button on the sheet, you will observe following results:

Explanation: In the above code, there are two loops used in a nested form. The first loop is used to track the Rows and second in between nested loop is used to track the Columns. Now as the loop executes, the intRow initializes the value 1 making intRow = 1 and from next line, the second loop initiates which initializes intCol with the values 1 making intCol = 1. Now, VBA will enter the value 100 into the cell at the intersection of row 1 and column 1 (intRow and intCol values). Now, when the program will reach to “Next intCol” code, it will increment the value of intCol with 1 making intCol = 2 and will jump back to For intCol line and this time it will enter the value 100 into the Cell at the intersection of row 1 and column 2. This process will keep on going till intCol completes 5 cycles.

After completing intCol cycles, the code will jump to “Next intRow”, incrementing its value with 1 and making intRow = 2 and again the previous cycles will restart. This loop will remain active till intRow also completes its five cycles.

Do While Loop

Besides For.. Next.. loop, there is another VBA supported loop called Do.. While.. Loop. This loop functions only when the condition placed next to Do While statement returns TRUE. Otherwise it will terminate.

To have a more clear understanding of Do While Loop, follow the steps below:

1. Insert a command button on worksheet and add the following lines of code:

Dim intCount As Integer

intCount = 1

Do While intCount <= 6

    Cells(intCount, 1) = intCount

    intCount = intCount + 1

Loop

On clicking the command button, the values of intCount will be entered in Range(A1:A6)

Explanation: as long as intCount is less than or equal to 6, MS Excel VBA enters the value of intCount variable into the cell at the intersection of row intCount and column 1 and increments intCount by 1.