560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls 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
Looping in Visual Basic for Applications macros
Part two of a four-part series of blogs
There are 4 types of loop in Visual Basic for Applications - this blog shows how and when to use each type of VBA loop structure.
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
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
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:
'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!"
'if we get here, they didn't type a name - try again
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:
Dim Pointless As Integer
'this will execute loop twice
For Pointless = 13 To 4 Step -5
Debug.Print "Pointless = " & Pointless
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:
Dim RowNum As Integer
Dim ColNum As Integer
'clear any old format
'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)
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):
Dim i As Integer
For i = 1 To Worksheets.Count
'for each worksheet, print its name to immediate window
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):
Dim ws As Worksheet
'loop over all worksheets, printing name of each
For Each ws In Worksheets
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:
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)
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 ...
|Parts of this blog|
25 Aytoun Street