VBA For Loop

What is a VBA for loop?

A 'for' loop is a programming concept that repeats a particular command a specified number of times. It executes a set of instructions repeatedly for a specified number of iterations without guaranteeing execution at least once.

It is a programming construct that automates repetitive tasks by repeatedly executing a set of instructions for a specified number of iterations.

It uses a loop counter and defined conditions to control the flow, making it valuable for performing tasks like iterating through lists or arrays in code. By using the For…Next statement in your macro, you can easily loop through a defined range of cells.

Understanding For Loops in Excel VBA

A for loop is a programming construct in Excel VBA used to repeat a set of statements for a known number of times, not a built-in function.

For example, if you wanted your VBA program to check the buy/sell transactions for the first twenty entries, we loop from 1 to that number. Based on such a loop in our code, we can highlight the cell values with different colors to distinguish the buy and sell transactions.

For Loop Syntax

The syntax for using For….Next loop in your VBA code is:

For [counter = start value to end value] [Step increment]
(conditional statements)
Next [counter]

where,

• counter = the variable assigned for the loop
• start value = the starting value for the loop counter 
• end value  = the end value for the loop counter
• step increment (optional) = This parameter increases the counter value every time the loop is completed once. By default, the assigned value for step increment is 1, but can be any number ranging from positive to negative values
• conditional statements = the statements that are executed inside the loop
  

Basic For Loop

Suppose a teacher in a classroom of 25 students needs to determine which students have scored marks below 60 in English and Maths so that they can join the extra lectures scheduled for next month. 

To accomplish this, the teacher can use the for loop and conditional statements to determine which students should be notified about the extra lectures.

The dataset for the marks scored by the students is as follows:

The F column will display the status as ‘Yes’ or ‘No’ based on the condition that if students have marks below 60 in either subject, they will be notified about the extra lectures next month.

Let’s check the VBA code in depth to understand what conditions can be incorporated while using the For loop to determine the status in the F column.

When you run the VBA code from the Macros in the Developer tab, you will get the following result: 

In this code, we set up two conditions: if marks for either English or Math are less than 60, the students are eligible for extra lectures. This is represented by the code Cells(n, 4) < 60 Or Cells(n, 5) < 60.

The counter for the loop starts with 3 and ends with 27 since our dataset begins from the third row(excluding the headers). Since step increment is not mentioned, it assumes the default values of 1, i.e., n+1, in our loop counter.

We have tried differentiating our results in column F using a simple Cells code (n, 6).Interior.Color = RGB(255,255,0) if we get ‘Yes’ as an answer. This makes visually differentiating between the IF…else statement results obtained from our VBA code easier.

For Loop using incremental step

Using the for loop by incorporating incremental steps is a great way to skip unnecessary dataset parts in your spreadsheet. 

For example, you might often receive Excel spreadsheets where a blank row separates each row containing data. The reason behind this could be to protect the integrity of the data set to avoid it getting mixed up together, but it just makes it difficult for us to analyze the data.

For example, similar data below represents the credit scores of borrowers looking to take out bank loans.

Since it is the bank’s policy only to lend loans if the borrower’s credit score is greater than 720, the information about the Co Borrower is deemed unnecessary in your analysis to determine the approval of loans.

In this case, using incremental steps in our counter is a great way to skip the Co Borrower’s information in our dataset. The VBA code to achieve this is:

When you run this code, you will get the result in your spreadsheet as below: 

We see that the value of our counter follows an equation of n = n + 2, where n is the starting value of the counter. So the next values of the loop counter increase by 2: 5, 7, 9, 11, 13, etc.

The step counter, thus, proves beneficial in skipping consistent unnecessary discrepancies in your dataset. 

Similarly, a negative step counter can be used to loop through our data. For example, if our step counter were equal to minus 2 with our start value as 3, our next value in the loop would have been 1. This wouldn’t be quite helpful, right?

That’s why we change our start/end value when using a negative step counter. Let’s see an example of a negative step counter in the next section to understand its application better. 

Negative incremental step counter in for loops

When working on large datasets, it is usually challenging to determine how many rows and columns are in the workbook.

Counting the rows manually and setting the start/stop value in our for loop can become tedious. In such cases, a combination of the last row/last column and positive/negative incremental step counter works great on our data.

Example

Assume that you have Tesla Inc (NASDAQ: TSLA) OHLC data for the past year. 

You need to analyze the price trend based on historical data. This includes checking whether the previous day’s opening and closing prices are greater or less than the next day’s opening and closing prices to generate buy/sell signals.

This example is in no way any fundamental or technical analysis of Tesla stock but is used to demonstrate how negative step counters can be used in a for loop. 

Our spreadsheet looks like the one below, extending up to 250 rows. In addition, we have added additional headers after column G, which will be used in our Tesla Inc. stock analysis. 

We have written the VBA code below, which incorporates nested If-Else statements and a for loop.

The general idea with the code is to check our opening/closing price flags in the H and I columns, respectively, and if they return as “TRUE”, then move on to the next column, i.e., to determine whether the closing price is greater than the opening price in column J.

Finally, we check whether the day’s volume is greater than the previous volume in column K.  If the values in columns J and K are both "TRUE," a "Buy" signal is generated in column L. If any cells return as "FALSE," the loop breaks and takes a new value in the counter.

Hence, a “Buy” signal will be generated if all the preceding values are “TRUE”.

A trader/investor believes that the stock is in an uptrend if the opening/closing price today is greater than yesterday. The stock is bullish when it closes above its opening price(Column J assumption).

Similarly, if there is a surge in stock volume from the previous day, it shows that the stock is bullish, and people are looking to invest/trade in the stock. A combination of these parameters is used to build the VBA code, which is as follows:

If you are new to If...Else statements or nested If statements, check out our article on the same here!

We get the following result in our spreadsheet when we run the macro. 

Based on our analysis in the spreadsheet, we see that the criteria to generate “BUY” signals were only met 36 times from the total of 253 trading days prices.

Further examination of the data based on “BUY” signals shows that on 18 occasions out of 36, the price for Tesla Inc. stock closed higher than the previous day's closing price, indicating a 50% accuracy. Not bad for an Excel model based on VBA code, right?

When you remove the filter from your dataset, this is how your spreadsheet will look like this:

 For each statement

A for…each loop is similar to the for loop, with the only difference being the step counter does not exist in the former. Therefore, for each loop iterates through each element of a collection, but it doesn't guarantee execution for all elements if control structures like 'Exit For' are used within the loop.

Let's see an example of how each loop works differently than the for loop in the VBA code.

Example

Let's say you work for an NGO that volunteers for different public services. This month, you receive five applications, and you need to allocate each person to different sectors for volunteering. So this is what your spreadsheet data looks like:

Since we know the range of our data, i.e., F3:F7, we used the for each loop in our VBA code, which is as below:

When you run the code, the code iterates through a collection of ranges from F3:F7, and the conditional statements check in which age category the person falls.

Then, based on the fulfillment of conditions, the code offsets one cell to the right and gives the result in the G column as to where the person should be stationed for volunteering.

Exit For statement

If you intend to exit the for loops immediately, you can use the Exit For statement in your code. This is usually helpful when the condition is met for the for loops. Pretty stupid to keep waiting for the loop to end if we already get our expected result, right? 

The Exit For Statement is used along with the if statement and can be executed if the condition is met. For example, you might need to find a specific value in your database. Then, you can use a for loop and exit it once the cell is found.

Suppose you make algorithmic trading software based on stock historical data. Then, you generate buy signals based on the VBA code we wrote previously in the article here.

For our algorithm software to learn when to buy stock, it must accept correct data. Otherwise, our software will generate inaccuracies. Our final result from the VBA code was as below, which we have copied to a separate spreadsheet:

The inputs here that the software needs to learn are the values in columns H, I, J, and K. If the value is “TRUE”, our software will work correctly.

However, if there are any “FALSE” values in any of the columns, the software gives poor results in terms of trade accuracies (the goal here is to get 50% accuracy or higher as per our analysis after running the code).

Let’s assume that the conditions of our code are perfectly met, but still, the result we get is “FALSE” in some of the cells. Therefore, we must find those cells and manually change them to “TRUE”. To detect this, we have written the VBA code as below:

When you run the macro, the result that you will get in your spreadsheet will look like this:

You will observe that the macro highlights all the values that appear first. Due to the nested if statement, the code exits the loop after it finds the value and moves on to the next column.

Next, all these values will be then changed to “TRUE”, and the macro will be rerun to check if any other “FALSE” values exist in the dataset before it is loaded into the algorithmic software.

In this code, we also have a new Goto Statement that directly lets us jump to the required line of code in our VBA script.

You can use this code using the Goto statement followed by the label. This label is later repeated, followed by the colon, which helps to jump to anywhere in the code. The name for the label could be anything, just as long as they match.

FOR LOOP - Practical Examples

Suppose you have created different sheets in your workbook and need to rename them all in one go. For example, assume all sheets have a financial analysis done for different companies. The code we have written to rename the n number of sheets in the workbook is as follows.

When you run the code, the name of the spreadsheets in your workbook will look like this:

If you intend to iterate over elements in a collection, such as an array of spreadsheet names, you can use the for each loop in the code.

For example, suppose you are working on the financial analysis of Tesla Inc., Microsoft, Apple Inc., NetFlix, and Meta. Then, you can create an array for these companies and run a for each loop in the code, which looks as below:

After running the code, you will get the following result:

Practical Example 2

Empty cells can be really annoying while working on the data. Firstly, they tend to break the data, causing irregularities or discrepancies in the analysis. As a result, inserting the value as N/A or just 0 (zero) helps fill those gaps.

Let’s say you have this stock data and must fill in those empty cells in the dataset. Of course, you could also delete those cells, but that would change the dynamics of your data, i.e., either shift the data towards the left or in the up direction.

The VBA code that you can use for this is:

You will find that its value would have been updated to zero for any empty cells. 

Such empty cells interfere with dynamic macros, such as looping up to the last row or column. Therefore, it is necessary to make references with the slightest chance of being empty.

For our table, we have referenced the headers while looping through the column, while for the rows, the dates are in the A column. Adding a column and serial numbers is also a great trick to use as a reference in the code.

In case of encountering errors, debugging involves inspecting variables and logic rather than directly modifying the loop structure, like changing start/end values and step counters. For example, changing the for code to -

For j = last column to 1 Step -1 or For i = last row to 2 Step -1

If you need more examples to look at, check out our article on If..Else Statement here that incorporates both for loops and conditional statements!

More on Excel

To continue your journey towards becoming an Excel wizard, check out these additional helpful WSO resources.

• VBA Glossary
• VBA If Else
• VBA in Excel
• VBA Macros
• VBA Methods