Code That Loops
You've seen in this tutorial and in previous tutorials that it makes sense to divide up your VBA chores and place them in separate procedures or functions. That way, you need to write the code only once and then call it any time you need it. This is known in the trade as modular programming, and it saves time and effort by helping you avoid reinventing too many wheels. There are also wheels to avoid reinventing within your procedures and functions. For example, consider the following code fragment:
MsgBox "The time is now " & Time
Application.Wait Now + TimeValue("00:00:05")
MsgBox "The time is now " & Time
Application.Wait Now + TimeValue("00:00:05")
MsgBox "The time is now " & Time
Application.Wait Now + TimeValue("00:00:05")
Note: This code fragment uses the Excel Application object's Wait method to produce a delay.The argument Now + TimeValue("00:00:05") pauses the procedure for about five seconds before continuing.
This code does nothing more than display the time, delay for five seconds, and repeat this two more times. Besides being decidedly useless, this code just reeks of inefficiency. It's clear that a far better approach would be to take just the first two statements and somehow get VBA to repeat them as many times as necessary.
The good news is that not only is it possible to do this, but VBA also gives you a number of different methods to perform this so-called looping.
In this tutorial:
- Controlling Your VBA Code
- Code That Makes Decisions
- Using If...Then to Make True/False Decisions
- Using If Then Else to Handle a False Result
- Making Multiple Decisions
- Using the Select Case Statement
- Functions That Make Decisions
- Code That Loops
- Using Do..Loop Structures
- Using For...Next Loops
- Using For Each...Next Loops
- Using Exit For or Exit Do to Exit a Loop
- Indenting for Readability