Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
There are essentially three types of loop that you can write in Visual Basic for Applications:
Use FOR ... NEXT when you want to loop a given number of times.
Use DO UNTIL ... LOOP when you want to loop until a condition is true, or the very similar DO WHILE ... LOOP or WHILE ... WEND when you want to loop while a condition is true.
Use FOR EACH ... NEXT when you want to loop over the objects in a collection.
These are in ascending order of usefulness. If you're an experienced VBA programmer, looping over the objects in a collection is nearly always the way to go.
The rest of this blog gives examples of each type of loop, with the possible forms of syntax. You can also learn about looping in VBA on our two-day introduction to VBA course and (in more detail) on our two-day advanced VBA course.
The basic syntax of this type of loop is as follows:
'declare a variable to hold an integer
Dim i As Long
'loop a certain number of times
For i = 1 To 100
'do something within the loop
Debug.Print i
Next i
Note that it is not essential to put the name of the variable at the end of the loop:
'you don't need to suffix the NEXT statement with the
'name of the integer variable
Next i
However, it does help you see where a loop ends, and is particularly useful when you have one loop within another (see the example at the bottom of this page).
Use this type of loop when you know exactly how many times you want to go through a loop. For example, suppose that want to give somebody 5 chances to type in their name - here's some code which would do this:
Sub GetName()
'the number of goes to give someone to type their name
Const NumberChances As Integer = 5
'number of each turn
Dim ThisGo As Integer
'the name typed in
Dim ThisName As String
'give someone up to N chances to enter name
For ThisGo = 1 To NumberChances
ThisName = InputBox("Type your name please")
If Len(ThisName) > 0 Then
'if this person typed in a name, store it in worksheet
Range("A1").Value = ThisName
MsgBox "Thank you!"
Exit For
End If
'if we get here, they didn't type a name - try again
Next ThisGo
End Sub
Here the variable ThisGo will take the values 1, 2, 3, 4 and 5. If the (rather stupid) user still hasn't typed anything into the input box which keeps appearing by then, the loop will end.
Notice the command Exit For to exit the loop prematurely if the user types in a value successfully.
The example above gives the most useful form of the FOR ... NEXT loop, but you can change the start number, stop number and step number. For example:
Sub MoreComplicatedLooping()
Dim Pointless As Integer
'this will execute loop twice
For Pointless = 13 To 4 Step -5
Debug.Print "Pointless = " & Pointless
Next Pointless
End Sub
The code above will produce the following output:
The loop only executes twice, because on the third pass the value of the variable will be 3, which is below the stop value 4.
A common requirement is to colour Excel worksheets with a pretty pattern (!):
Well, it gives us a chance to loop a given number of times!
The macro to get the above worksheet to appear could look like this:
Sub ColourWorksheet()
Dim RowNum As Integer
Dim ColNum As Integer
'clear any old format
Cells.ClearFormats
'for each alternate row ...
For RowNum = 1 To 10
'... for each alternate column within this ...
For ColNum = (RowNum Mod 2) + 1 To 10 Step 2
'... set the relevant cell's fill colour to be increasingly red - going down -
'and green - going across
Cells(RowNum, ColNum).Interior.Color = _
RGB(RowNum * 25, ColNum * 25, 0)
Next ColNum
Next RowNum
End Sub
Here:
the outer loop takes the row number from 1 to 10; while
the inner loop takes the column number from either 1 or 2 up to 20, producing the diagonal effect (the Mod function gives the remainder when you divide a number by - in this case - 2).
Notice the indentation above - without this, the code would be hard to read.
Not often, is the answer (although looping over arrays is an exception - see the example at the foot of this page).
For example, suppose that you wanted to print out the names of the worksheets in the current workbook. You could do this by looping N times (where N is the number of worksheets in the workbook):
Sub ListSheetNames()
Dim i As Integer
For i = 1 To Worksheets.Count
'for each worksheet, print its name to immediate window
Debug.Print Worksheets(i).Name
Next i
End Sub
However, the code above would be better by far if you looped over the built-in collection of worksheets (shown towards the end of this blog series):
Sub ListSheetNames()
Dim ws As Worksheet
'loop over all worksheets, printing name of each
For Each ws In Worksheets
Debug.Print ws.Name
Next ws
End Sub
Good VBA programmers rarely use the FOR ... NEXT loop structure, except when iterating over arrays. As an example of this, the following code reads text into array cells and then writes it back out again:
Sub WriteDwarves()
Dim BiblicalCharacters(2) As String
Dim bc As Integer
'put text into array elements
BiblicalCharacters(0) = "Shadrach"
BiblicalCharacters(1) = "Meshach"
BiblicalCharacters(2) = "Abednego"
'write it back out again!
For bc = 0 To UBound(BiblicalCharacters)
Debug.Print bc, BiblicalCharacters(bc)
Next bc
End Sub
This code would produce a list of the 3 characters shown in the debug window.
Our two-day Introduction to VBA course covers the four types of loop available in VBA, should you want to learn more ...
The second type of loop, which is far more used than FOR ... NEXT, can be expressed in English as either:
These are the most dangerous types of loop, since it's so easy for the condition never to happen! In this case you'll be looking at an hourglass while your computer's CPU gradually starts warming up with the effort it's putting in ...
The syntax of the two types of loop is as follows. Firstly, DO UNTIL:
Sub KeepDoingSomething()
Dim i As Integer
i = 0
'keep going round the loop until the condition is true
Do Until i = 10
i = i + 1
Debug.Print i
Loop
End Sub
Whatever you can write using DO UNTIL you can also write using DO WHILE:
Sub KeepDoingSomething()
Dim i As Integer
i = 0
'keep going round the loop until the condition is true
Do While i <>
i = i + 1
Debug.Print i
Loop
End Sub
The above example shows that you only need to learn one of DO UNTIL and DO WHILE, as they're interchangeable.
You can write a DO loop in a number of different ways. For the example above, you could also use any of the following (shown abbreviated, and without comments).
Using WHILE ... WEND (a bit old-fashioned):
While i <>
i = i + 1
Debug.Print i
Wend
Putting the condition at the end (WHILE):
Do
i = i + 1
Debug.Print i
Loop While i <>
Putting the condition at the end (UNTIL):
Do
i = i + 1
Debug.Print i
Loop Until i = 10
However, I personally don't like any of these alternatives (I've included them here just because you may sometimes come across them).
Suppose that we want to colour all of the highly-rated Muppets in the spreadsheet below:
You may disagree with the ratings shown, but that's not the point of the exercise!
An algorithm to achieve this would be:
Here's what we want the spreadsheet to look like when we've finished:
As it happens, this pale blue colour is number 20.
The code to make this work might look like this:
Sub ColourGoodMuppets()
'go to Kermit (top of list)
Cells.Find("Kermit").Select
'keep going down till we hit a blank cell
Do Until ActiveCell.Value = ""
'if this has rating above 5 in column D ...
If ActiveCell.Offset(0, 2).Value > 5 Then
'... colour entire row
Range( _
ActiveCell, _
ActiveCell.End(xlToRight) _
).Interior.ColorIndex = 20
End If
'go onto next Muppet
ActiveCell.Offset(1, 0).Select
Loop
'show finished!
MsgBox "All done!"
End Sub
Here we've set the interior colour of a block of cells (taken from the current cell across to the right) to 20, which happens to be pale blue.
Sorry to keep saying it, but ... this macro would have been much better written by looping over the collection of cells, using FOR EACH ... NEXT (see next part of this blog series, or either of our online or classroom VBA courses).
Where looping until (or while) a condition is true is invaluable is when you're reading through the lines of a file. The following code will:
My reason for showing this example is to illustrate the fact that sometimes looping until/while a condition is true is the only way to go!
Let's start with what the output of the program will look like:
The program should print out the lines read in from the text file.
The first thing you'll need to do is to reference the Microsoft Scripting Runtime object library (more on this in a later blog on referencing other applications - if you're not show how to do this, just read through the code for now).
The code to produce the output shown above is then:
Option Explicit
'you need this to create and read files
Dim fso As New FileSystemObject
Sub LoopingExample()
'note that you'll need to create a reference to the Microsoft Scripting Runtime
'object library for this to work
'write the text file out
CreateFile
'now read it back in
ReadFile
End Sub
Sub CreateFile()
Dim i As Integer
Dim tsWrite As TextStream
'start writing to a new text file
Set tsWrite = fso.CreateTextFile( _
"c:\training\numbers.txt", True)
'write out the first 10 numbers
For i = 1 To 10
tsWrite.WriteLine ("i = " & i)
Next i
'close the file
tsWrite.Close
End Sub
Sub ReadFile()
Dim tsRead As TextStream
Dim ThisLine As String
Dim n As Integer
'open the text file created for reading
Set tsRead = fso.OpenTextFile( _
"c:\training\numbers.txt", ForReading)
n = 0
'keep reading until there are no more lines
Do Until tsRead.AtEndOfStream
'for each line, read it and show it in immediate window
n = n + 1
ThisLine = tsRead.ReadLine
Debug.Print "Line " & n & ": " & ThisLine
Loop
'good housekeeping - close the file
tsRead.Close
End Sub
The subroutine to run for the above example is the top one, LoopingExample, which will then call the other two routines.
As already mentioned, this type of loop structure is (for Excel at any rate) nearly always the way to go, although it is harder to use to begin with.
Consider the following (somewhat greedy) example:
Suppose that given this collection of creamcakes, we want to eat every single one.
We want to write a program in VBA to eat every cream cake. We know there is a collection of CreamCakes, and we know that we want to apply the Eat method to each of the objects in the collection. Here is how to do this:
Sub EatAllCakes()
'a reference to each object in the collection
'that we are about to loop over
Dim cc As CreamCake
'for each of the objects in the collection of cream cakes ...
For Each cc In CreamCakes
'... eat it!
cc.Eat
'now go on automatically to the next cream cakes
Next cc
End Sub
The syntax of the FOR EACH ... NEXT loop is thus:
Dim Item As Object
For Each Item In Collection
'do something to the item
Next Item
In Excel, the three most useful collections to loop over are:
Collection | What it contains |
---|---|
Workbooks | All of the files that you currently have open in Excel |
Worksheets | All of the worksheets in the current workbook |
Range of cells | Any range of cells |
However, there are many other collections in Excel, including PivotTables, Charts, Shapes and many more besides.
The great thing about VBA is this: once you've learnt the principles of looping over collections, you can write code to count paragraphs in Word, colour slides in PowerPoint or close forms in Access - the structure is always the same!
Suppose that you want to close down all of the workbooks that a user has open apart from the one containing the running code, but you want to give your user in each case the change to save any changes they've made. You could do this as follows:
Loop over all of the objects in the Workbooks collection.
For each such Workbook object, check it isn't the same as ThisWorkbook (the workbook containing the code you're running).
If it isn't the same, close it down; otherwise, ignore it.
Here's the code to do this - admire it's simplicity, elegance and conciseness!
Sub CloseAllButCodeWorkbooks()
'a reference to each workbook in turn
Dim wb As Workbook
'for each of the open workbooks ...
For Each wb In Workbooks
'if it isn't this one containing the code (note that you can't directly
'compare two workbooks: instead, you must see if they have the same name)
If wb.Name <> ThisWorkbook.Name Then
'it's not the same - close it down, giving user a chance to save changes
'(this is the default()
wb.Close
End If
'now go on automatically to the next workbook
Next wb
End Sub
Instead of using ThisWorkbook (which refers to the workbook containing the current running code), you could also have used ActiveWorkbook (the current workbook). These are not necessarily the same.
Imagine that (somewhat egotistically) you decide to name all of your worksheets in a workbook after your company name (for us it's Wise Owl).
The results of running our macro - worksheets with different names!
Here's how to rename all of the worksheets in a workbook like this:
Sub RenameWorksheets()
'a reference to each worksheet in the active workbook
Dim ws As Worksheet
'the index number to use
Dim SheetNumber As Integer
SheetNumber = 0
For Each ws In Worksheets
'for each worksheet, rename it
SheetNumber = SheetNumber + 1
ws.Name = "Wise Owl " & SheetNumber
Next ws
End Sub
Note that the above code works because it never tries to create 2 worksheets with the same name.
A common requirement is to delete all of the other worksheets in a workbook apart from the current one (whatever this may be called). When you do this, you will normally get the following message appearing:
The default message which appears when you try to delete a worksheet
To avoid this appearing, you can turn the DisplayAlerts property of Excel off temporarily:
Sub DeleteAllButCurrentSheet()
'a reference to each worksheet in the active workbook
Dim ws As Worksheet
For Each ws In Worksheets
'for each worksheet, suppress error messages and delete it
'(providing that it's not the active worksheet)
If ws.Name <> ActiveSheet.Name Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub
The above macro is dangerous: you'll lose all of the other worksheets in your wokbook!
By far the most common requirement in looping over collections in Excel is to do something to every single cell. For example, suppose that you wanted to colour our high-rating muppets again, but this time using FOR EACH ... NEXT:
We want to write a macro to colour all of the Muppets scoring more than 5
To do this we want to loop over the collection of cells from Kermit (shown shaded in green above) to the bottom of the column. We could refer to this block of cells as B5:B11, but it would be better to make our macro work even if we added more muppets. To do this, our macro will select the range shown below:
Our macro will select the cells shown, and leave the rows coloured blue as here.
Here's how to do this:
Sub ColourHighRatingMuppets()
'a reference to each cell in the muppet names column
Dim c As Range
'go to Kermit, and select from here down to the bottom of the column
Cells.Find("Kermit").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
'now look at each muppet in turn in this block
For Each c In Selection
'if this has rating more than 5 in column D ...
If c.Offset(0, 2).Value > 5 Then
'colour entire row blue
Range(c, c.End(xlToRight)).Interior.ColorIndex = 20
End If
Next c
End Sub
The big surprise here is that when you loop over a collection of cells, you refer to each cell in the collection as a Range object.
There is no such thing in Excel as a Cell object! Any range of cells is a collection of individual single-cell Range objects.
The perfect macro (!) for the above problem would use object variables (that is, variables which refer to objects built into Excel, rather than numbers, text or dates):
Sub ColourHighRatingMuppets()
'a reference to the first Muppet
Dim TopCell As Range
'references to:
'- the range of Muppets; and
'- each individual Muppet cell
'a reference to each cell in the muppet names column
Dim MuppetRange As Range
Dim MuppetCell As Range
'first check there is a top cell to start from
Set TopCell = Cells.Find("Muppet Name")
If TopCell Is Nothing Then
MsgBox "Can not find top of column"
Exit Sub
End If
'get a reference to the column of muppets
Set MuppetRange = Range( _
TopCell.Offset(1, 0), _
TopCell.End(xlDown))
'now look at each muppet in turn in this block
For Each MuppetCell In MuppetRange
'if this has rating more than 5 in column D ...
If MuppetCell.Offset(0, 2).Value > 5 Then
'colour entire row blue
Range(MuppetCell, MuppetCell.End(xlToRight)).Interior.ColorIndex = 20
End If
Next MuppetCell
End Sub
Question: where is the active cell after you run the above macro?
Answer: where it started (it doesn't move).
Now that you've learnt looping over collections, you can consider yourself a serious VBA programmer!
Ideas for where to go now:
Course | Notes |
---|---|
A two-day course showing how to program in VBA in Excel. | |
The same course, but again delivered in a classroom in the UK. |
You can see all of these courses at our Visual Basic for Applications training home page.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.