For/Next loops allow you to set up a basic looping structure in which a series of statements execute repeatedly, with the value of a counter variable increased by one (or more) each time until the counter variable reaches a certain value.
As a simple - if not quite practical - example, the following snippet inserts the numbers 1 through 100 in the current document, one number per line:
For x = 1 to 100 Selection.InsertAfter Str(x) & Chr(13) Next x
This For/Next loop causes the InsertAfter statement it contains to execute 100 times. The first time through, the variable x is set to the value 1. The second time, x is 2; the third time, 3; and so on, all the way to 100.
The general form of a For/Next loop is
For counter-variable = start To end [Step increment] statements... Next [counter-variable]
You can specify any starting and ending value you want for the counter variable. In addition, you can specify an increment value by using the Step clause. You can use Step to create For/Next loops that count by twos or threes or any other value you want. If you omit Step, the default is 1. The term iteration is often used to refer to each execution of a For/Next loop. For example, a For/Next loop that starts with the line For x = 1 To 10 iterates ten times.
While/Wend loops provide a more sophisticated form of looping, in which the loop continues as long as a specified condition remains True. The general form is
While condition statements Wend
The While loop starts by evaluating the condition. If it's True, the statements in the loop execute. When the Wend statement is encountered, the condition is evaluated again. If it's still True, the statements in the loop execute again. This cycle continues until the condition evaluates as False.
At this point, you need to know just what it means to say that a condition is True: In VBA, False is defined as the numeric value 0, and any nonzero value is considered to be True. For example, consider this While loop:
x = 5 While x Selection.InsertAfter Str(x) & Chr(13) x = x - 1 Wend
This loop continues to execute as long as x is not zero. The moment x becomes zero, VBA considers the condition expression to be False and the loop terminates. As a result, this While loop displays five message boxes, showing the values 5, 4, 3, 2, 1, and then it terminates.
To continue a loop as long as an expression evaluates to False, use Not as part of the condition test. For example:
x = 0 While Not x = 5 Selection.InsertAfter Str(x) & Chr(13) x = x + 1 Wend
In this example, the loop repeats as long as x is not equal to 5.
The Select Case statement
Life would be easy if it consisted entirely of either/or choices. But in the real world, you're often faced with many alternatives to choose from. And so it is in VBA. More than a few VBA functions return more complicated results than a simple yes/no, true/false, or 0/1. For example, Selection.Style.Name returns the name of the style applied to the current selection. You can use this information to cause your macro to take a different action depending on which style is applied to the selected paragraph.
The Select Case statement is designed for just this type of situation. It lets you test an expression for various values, executing different statements depending on the result. Its general form is
Select Case expression Case case-condition statements [ Case case-condition statements ] [ Case Else statements ] End Select
The Select Case statement starts by evaluating the expression. Then it compares the result with the case conditions listed in the Case clauses, one at a time. When it finds a match, it executes the statements listed for the Case clause that matches, and it skips the rest of the Select Case statement. If none of the case conditions match, the statements in the Case Else clause execute. The key point is that only one of the Case clauses is selected for execution.
For each Case clause, values can be any of the following:
- A single value, such as Case 4: The Case clause is selected if the expression is equal to the value.
- A list of expressions, such as Case 4, 8, 12, 16: The Case clause is selected if the expression equals any of the listed values.
- A range of values, separated with the keyword To, such as Case 4 to 8: The Case clause is selected if the expression falls between the two values, inclusively.
- The word Is followed by a relational comparison, such as Is > 4: The relation is tested against the expression, and the Case clause is selected if the result of the comparison is True.
Here's an example of a While loop that includes a Select Case statement to count the number of Heading 1, Heading 2, and Heading 3 styles from the current selection to the end of the document:
Dim Heading1Count As Integer Dim Heading2Count As Integer Dim Heading3Count As Integer Dim s As Style While Selection.Move(wdParagraph, 1) Select Case Selection.Style.NameLocal Case "Heading 1" Heading1Count = Heading1Count + 1 Case "Heading 2" Heading2Count = Heading2Count + 1 Case "Heading 3" Heading3Count = Heading3Count + 1 End Select Wend
In this example, the variables Heading1Count, Heading2Count, and Heading3Count count the number of headings for each level. The Select Case statement evaluates the NameLocal property of the selection's Style object. Then the Case clauses check for the values "Heading 1", "Heading 2", and "Heading 3". If the NameLocal property returns one of these three values, 1 is added to the appropriate counter variable. You can use Case Else to handle any values that aren't specifically mentioned in Case clauses.
In this tutorial:
- Programming with VBA
- Basic Structure of VBA Macros
- Understanding the Basic Elements of VBA
- Working with Variables and Data
- Using static variables
- Using Strings
- Of Objects, Properties, and Methods
- Getting to know the object model
- Using the With statement
- Controlling Your Programs
- The ElseIf structure
- For/Next Loops
- User Input and Output
- User-Defined Procedures and Functions