When learning Excel VBA, you need to know how to use loops competently.
By using loops we can walk through a range of values, rows, sheets or other objects and perform a task repeatedly on each one.
This is a fundamental Excel VBA skill. Not being able to efficiently construct loops will heavily limit the macros you can create.
There are 2 types of loop in VBA. There are For loops and Do loops. This article focuses on learning For loops.
If you want to learn Excel VBA fast. Enrol in our online course. This is a complete beginners guide to learning Excel VBA.
Introduction to For Loops in Excel VBA
There are 2 types of For loop in VBA – the For Next and the For Each Next. This tutorial will fully explain both.
The For Next loop is used when you need to perform a sequence of tasks a specified number of times.
You specify how many times you want the code to run, and what task to perform each time it runs.
The number of times the code runs is either known by us – perform these tasks 12 times (once for each month of the year), or by Excel – perform these tasks for each sheet in the workbook (Excel knows how many sheets are in the active workbook).
Let’s look at some examples of the For Next Loop.
Loop Through Each Row in a List
Looping through each row in a list is a very typical example of a loop. Reasons for doing this could be to analyze the value in column A of each row, and format or perform calculations on specific values.
In the code below, we loop through each row in a list until the total number of rows is reached. Each value in column A of the list is doubled.
This is a simple example of such a loop, but it demonstrates the structure of a For Next loop and its capability.
Sub DoubleValues() Dim r As Long Dim TotalRows As Long TotalRows = Application.CountA(Columns(1)) For r = 2 To TotalRows Cells(r, 1).Value = Cells(r, 1).Value * 2 Next End Sub
The For loop begins from row 2, and finishes after the row number is equal to the total number of rows.
Adding a Step to the For Next Loop
If you wanted to double every other row in the list, you could add a step.
In the code below, the loop steps 2 rows each time it loops, instead of each row.
Sub DoubleValues() Dim r As Long Dim TotalRows As Long TotalRows = Application.CountA(Columns(1)) For r = 2 To TotalRows Step 2 Cells(r, 1).Value = Cells(r, 1).Value * 2 Next End Sub
The Step 2 statement is added at the end of the first line of the For Loop.
Loop Through Each Character in a Cell
Another example of a For Next loop is to walk through each character in a cell. It could be that you want to extract, format or remove part of a cells content.
In this example, we will format all the numbers within a cell red.
Sub FormatNumbers() Dim i As Integer Dim r As Long Dim TotalRows As Long TotalRows = Application.CountA(Columns(1)) For r = 2 To TotalRows For i = 1 To Len(Cells(r, 1)) If IsNumeric(Mid(Cells(r, 1), i, 1)) Then Cells(r, 1).Characters(Start:=i, Length:=1).Font.Color = 255 End If Next i Next r End Sub
If you are a beginner to VBA this code may look a little daunting, but it is rarely as bad as it seems.
We have 2 For Next loops here. One to loop through the rows in a list, and a deeper one to loop through the characters of a cell.
A couple of text functions (Len and Mid) have been used to find the length of the string and also work on each character.
The colour code of 255 has been used to format the characters red.
See how to format whole words in a cell a specific colour with a macro.
Loop Through the Cells of a Selected Range
Now let’s look at the For Each Next Loop.
This loop is wonderful when working with collections. Examples of collections include all the sheets of a workbook, all the open workbooks, or all the cells of a selected range.
The macro below converts all the values in the selected range to uppercase.
Sub ConvertToUpper() Dim r As Range For Each r In Selection r = StrConv(r, vbUpperCase) Next End Sub
Such as simple, yet useful little macro.
A variable is declared named r to act as a cell. This is used in the For Each Next loop to go to each cell in the selected range and convert to uppercase.
Loop Through the Sheets of a Workbook
Another awesome example of the For Each Next loop being used with a collection, would be with all the sheets of a workbook.
In this macro, we will freeze the top row of every sheet in a workbook.
Sub FreezePanesAll() Dim s As Worksheet For Each s In ActiveWorkbook.Worksheets s.Activate Range("A2").select ActiveWindow.FreezePanes = True Next End Sub
Excel knows how many sheets are in a workbook, so it is not necessary for us to know. We just use a For Each Next loop through Activeworkbook.Worksheets.
Check out this example where we export all sheets as a PDF.
This beginners guide to For loops should give you a good understanding of how they can be utilised in your macros.
If you are using macros to automate repetitive tasks, then loops will be needed.
If you find them a little overwhelming right now. Trust me. As you start to use them, you will be comfortable in no time.
Thank you very much sir.