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
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 .
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.