Excel VBA Loops - Introduction

Last Updated on January 11, 2023 by token

Introduction to the loops in Excel VBA

Loops are the most powerfull elements in programming languages. Using them, you can automate activities, speed up your programs, do things that you couldn’t do in the Excel datasheet. The loop, as its name suggests, is used to do some operations many times. In VBA Excel, there are three main types of loops:

All the mentioned VBA loops will be learn in the further part of the VBA Course. In this chapter, we will discuss the basic description of the While … Wend loop example and its use.

Loop example – How to write a loop in VBA

The VBA loops that you will most often use in your code will be based on the following scheme:

While [statements]
 [code]
Wend

Do While [statements]
 [code]
Loop

The interpretation of the code means to us as follows:

Do the code when the condition is True. If the condition is true also after doing the code, do it once again.

Let’s make a simple example. Using the While … Wend loop, let’s fill in the first 100 rows of our Excel spreadsheet with any value, ie. “how the loop works”.

Sub WhileWendExample()

Dim intCounter As Integer
intCounter = 1

While intCounter <= 100
 Cells(intCounter, 1) = "how the loop works"
 intCounter = intCounter + 1
Wend

End Sub

Loop example - How to write a loop in VBA — Loop example – How to write a loop in VBA

VBA Loop example – Description step by step

Let’s try to modify our VBA Loop example:

Sub LoopExample2()
 Dim intCounter As Integer 
 intCounter = 1 

 While intCounter <= 100 
  Cells(intCounter, 1) = intCounter 
  intCounter = intCounter + 1 
 Wend 
End Sub

You have just created new program using loops. Let’s try to describe what was actually done step by step:

We’ve created a program with name WhileWendExample
We declared an explicit variable named intCounter , which is our counter
We set the value 1 for our counter variable. This is important because the default value is 0, which means that without this block we would like to first fill in the “A0” cell. As we know in our sheet is not exist.
We set a condition for the loop to be executed when the intCounter is less or equal than 100 .
We referred in the instruction to the cell “A1” through the Cells command (intCounter, 1) , which with our initial counter value intCounter means Cells (1, 1). We filled in the “A1” cell in this way.
As part of the intCounter = intCounter + 1 statement, we increase the counter value by 1 each time the loop is running. In this case, the loop is executed 100 times until the value does not fulfill the condition <= 100 . The loop goes exit.

In our example, we introduced one variable. The instruction for this numeric variable that has been “looped”. Let’s modify our program. Instead of the phrase “this is how the loop works”, enter the value of the intCounter counter into the cells. In this way, our program will fill 100 consecutive cells in the “A” column with numbers from 1 to 100 .

VBA loop - numbers from 1 to 100 — VBA loop – numbers from 1 to 100

Quick Tasks (You can enter the solution in a comment)

Similarly to the example, fill the columns with numbers from 1 to 100.
Use a loop to enter numbers from 1 to 10 into the worksheet, but only every second row need to by filled.