Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: 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: 

Sub ForNext()

    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. 

Sub ForNextExit()

    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:

Sub ForEach()

    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

Newsletter