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
545 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 ...
Looping is an important concept in programming. A loop allows you to tell a group of instructions to execute repeatedly. VBA has several different types of loop; this lesson introduces you to the Do Loop structure, which allows you to continue executing instructions based on the result of a condition.
The condition that you use in a Do Loop can be based on any valid logical test. You might continue looping based on the value of a cell, the result of a calculation, or even the time of day.
You can click here to download the file for this page.
You can click here to download a file containing the sample code.
Extract and open the file linked to in the Files Needed section above. You'll find a workbook containing lists of matches for the 2018 World Cup:
Each worksheet contains a list of six matches for a single group.
We'd like to create a procedure which predicts the score for each match in each group. In the VBE you'll find a subroutine which predicts the result of a single match:
The code selects the first match on the first worksheet and creates a random score for each team.
Run the subroutine and check the result in Excel:
You'll see a result appear for the first match of Group A.
Now all we have to do is make this code repeat for every match on every worksheet!
One of the simplest ways to make code repeat in VBA is to enclose a set of instructions in a Do - Loop statement. Using the Predict_Scores subroutine, add the word Do above the two instructions which generate the random scores and add the word Loop below those two instructions:
It's conventional to indent the instructions within the loop by one tab space.
Now try stepping through the code by pressing the F8 key until you reach the Loop line:
Continue pressing F8 until you reach the line shown highlighted in yellow.
Press F8 again to see which line will be executed next:
Pressing F8 again returns to the first instruction within the Do - Loop statement.
Congratulations, you've successfully started your code looping! The major problem at this point is that it doesn't know when to stop. You can stop the subroutine manually by choosing Run | Reset from the menu, or by clicking the Reset tool:
Click this tool to stop the subroutine.
When you begin writing code which uses loops, it's very easy to create loops that will never end, or infinite loops. Just in case you accidentally run a procedure which contains an infinite loop, you should know that you can attempt to stop it by pressing either Esc or Ctrl + Break on the keyboard:
When you break into the running subroutine, you'll see this dialog box appear. Click End to stop the code.
We'd like our loop to stop automatically once it reaches the bottom of the current table. In order to achieve this, we need to move down to the next cell once we've created a result for the first match. Add a line to move down one cell before the Loop line:
Use the Offset property and Select method to do this.
We can now test if the selected cell is empty and, if so, exit from the Do - Loop statement:
Use the Exit Do statement to make the code jump out of the Do - Loop.
Save the workbook and then try running the subroutine:
Each match will have a result (regardless of how unrealistic it is!) and the selected cell will be the next empty cell in column A.
Now we simply need to make our code move to the next worksheet and repeat.
To create a set of scores for each group we need to move to the next worksheet and repeat the instructions we've already written. We can do this by nesting our existing loop within another. Start by adding an instruction to move to the next worksheet after the existing loop has finished:
This instruction will select the worksheet to the right of the currently selected one.
After moving to the next worksheet, we need to repeat the list of instructions starting with selecting cell A3. To do this, add a new Do - Loop statement which encloses the instructions:
The first loop is nested within the second.
The new loop will continue attempting to select the next worksheet, even when there isn't one to select. This will cause an error once you reach the Group H worksheet:
If there is no worksheet to the right of the currently selected one, you'll see this error message.
We can use this to create an exit point for the loop. Add an If statement above the instruction which selects the next worksheet:
It's important to exit from the Do - Loop before we attempt to select the next worksheet.
Run the subroutine and check the result in Excel:
After running the subroutine, each group will have a full set of scores and the Group H worksheet will be selected.
To practise writing Do - Until loops, we'll add a procedure which formats the winner and loser of each match in different colours:
Sub Format_Results()
Worksheets(1).Select
End Sub
Sub Format_Results()
Worksheets(1).Select
Do
Range("A3").Select
Loop
End Sub
Sub Format_Results()
Worksheets(1).Select
Do
Range("A3").Select
If ActiveSheet.Next Is Nothing Then
Exit Do
Else
ActiveSheet.Next.Select
End If
Loop
End Sub
You should end up on the Group H worksheet with cell A3 selected.
Sub Format_Results()
Worksheets(1).Select
Do
Range("A3").Select
Do
ActiveCell.Offset(1, 0).Select
Loop
If ActiveSheet.Next Is Nothing Then
Exit Do
Else
ActiveSheet.Next.Select
End If
Loop
End Sub
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then Exit Do
Loop
You will end up on the Group H worksheet with cell A9 selected.
Do
If ActiveCell.Offset(0, 2).Value > _
ActiveCell.Offset(0, 4).Value Then
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then Exit Do
Loop
Do
If ActiveCell.Offset(0, 2).Value > _
ActiveCell.Offset(0, 4).Value Then
ActiveCell.Offset(0, 2).Interior.Color = rgbGreen
ActiveCell.Offset(0, 4).Interior.Color = rgbRed
Do
If ActiveCell.Offset(0, 2).Value > _
ActiveCell.Offset(0, 4).Value Then
ActiveCell.Offset(0, 2).Interior.Color = rgbGreen
ActiveCell.Offset(0, 4).Interior.Color = rgbRed
ElseIf ActiveCell.Offset(0, 4).Value > _
ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 2).Interior.Color = rgbRed
ActiveCell.Offset(0, 4).Interior.Color = rgbGreen
Do
If ActiveCell.Offset(0, 2).Value > _
ActiveCell.Offset(0, 4).Value Then
ActiveCell.Offset(0, 2).Interior.Color = rgbGreen
ActiveCell.Offset(0, 4).Interior.Color = rgbRed
ElseIf ActiveCell.Offset(0, 4).Value > _
ActiveCell.Offset(0, 2).Value Then
ActiveCell.Offset(0, 2).Interior.Color = rgbRed
ActiveCell.Offset(0, 4).Interior.Color = rgbGreen
Else
ActiveCell.Offset(0, 2).Interior.Color = rgbOrange
ActiveCell.Offset(0, 4).Interior.Color = rgbOrange
End If
We could have chosen nicer colours.
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.