VBA Blogs: Going Through the Visual Basics – Part 16
14 December 2018
We thought we’d run an elementary series going through the rudiments of Visual Basic for Applications (VBA) as a springboard for newer users. This blog has a lot going FOR it.
In a programming, a control structure determines the order in which statements are executed. The iteration control structure is used for repetitively executing a block of code multiple times.
One group of powerful loops is the FOR family. There are several members and examples are presented below.
FOR…NEXT
The FOR…NEXT loop uses a variable, which cycles through a series of values within a specified range and the statements inside the loop is then executed for each value.
For counter = start To end [ Step step ]
[ statements ]
Exit For
[ statements ]
Next [ counter ]
Here’s a simple example:
SubForNext()
Dim counter As Integer
For counter = 1 To 5
Debug.Print counter
Next counter
End Sub
The STEP keyword allows the specification of how the counter changes. It defaults to an increment of 1, but it can be used for jumps and decrements.
Sub ForNextStep()
Dim counter As Integer
For counter = 10 To 0 Step -2
Debug.Print counter
Next counter
End Sub
EXIT FOR
EXIT FOR statements may be placed anywhere in the loop as an alternate way to exit. This is often used after evaluating of some condition, for example IF…THEN, and then skips to the statements after the loop.
SubForNextExit()
Dim counter As Integer
For counter = 10 To 0 Step -2
Debug.Print counter
If counter = 6 Then
Exit For
End If
Next counter
End Sub
FOR EACH…NEXT
What if an action is needed to be performed to every object in a set?
FOR EACH…NEXT loops are a great way to cycle through sets – like an array or a range. Sometimes the number of rows or columns is uncertain. It is relatively easy to count the number of objects and set the upper bound of the FOR…NEXT loop appropriately. However, using FOR EACH…NEXT more clearly illustrates that the instructions are happening to every object.
Example:
SubForEach()
Dim myNumbers() As Variant
myNumbers = Array(1, 5, 10, 15)
Dim aNumber As Variant
For Each aNumber In myNumbers
Debug.Print aNumber * 5
Next
End Sub
