Excel VBA Loops – Introduction

Introduction to the loops in Excel VBA

Loops are the strongest element of programming languages. It is through them that we can automate activities, speed up the work of our programs, do things that we could not do in the Excel data sheet. The loop itself, as its name suggests, is used to looping, and therefore performing an operation many times. In VBA in Excel, we distinguish three main types of loops While … Wend , Do … Loop and For … Next . All the mentioned VBA loops will be known in the further part of the training. In this chapter, we will discuss the basic description of the While … Wend loop and its use.

 

The operation of the example loop – How to write a loop in VBA

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

Or

The mere interpretation of the code means to us as follows: Follow the instructions if the condition is fulfilled . Let’s make a simple example on this basis. Using the While … Wend loop, let’s fill in the first 100 rows of our Excel spreadsheet with any value, eg “this is how the loop works”.

Description of the example:

We have just created our first program using loops. Let’s try to describe what was actually done one after the other:

  • We’ve created a program about the WhileWendExample
  • We declared an explicit variable named intCounter , which is our counter
  • We set the value 1 for our 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, which as we know in our sheet is not present.
  • We set a condition for the loop to be executed when the  intCounter is less than or equal to 100 .
  • We referred in the instruction to the cell “A1” through the Cells command  (intCounter, 1) , which with our initial counter value intCounter means as much as 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 satisfy the condition <= 100 . The loop goes out.

In our example, we introduced one variable. It is 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)

  1. Similarly to the example, fill the columns with numbers from 1 to 100.
  2. Use a loop to enter numbers from 1 to 10 into the worksheet, so that every second row is filled.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: