Loops – While Wend

In this blog post we take a look at the While…Wend Loop.

While(someExpression)
...
Wend

The loop will continue to operate as long as someExpression is equal to true. When it becomes false, the while loop exits.

Sub whileLoopArray()
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
   
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0
    
    While (i < UBound(kitchenItems) + 1)
        'This line of code essentially says:
        ' As long as the value of i is less
        'than 6 execute the next line. Otherwise
        'exit the loop
        
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1
        'We need to increment i or we will be stuck
        'in a loop forever...
    Wend

End Sub

The While Loop is often used to cycle through Recordsets and Files.

Sub whileLoopRecordset()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "tblTeachers"
'For the purposes of this post, we are simply going to make
'strSQL equal to tblTeachers.
'You could use a full SELECT statement such as:
'SELECT * FROM tblTeachers (this would produce the same result in fact).
'You could also add a Where clause to filter which records are returned:
'SELECT * FROM tblTeachers Where ZIPPostal = '98052'
' (this would return 5 records)

Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!!
'In English, this means that we have opened up a recordset
'and can access its values using the rs variable.

With rs

    
    If Not .BOF And Not .EOF Then
    'We don’t know if the recordset has any records,
    'so we use this line of code to check. If there are no records
    'we won’t execute any code in the if..end if statement.
        
        .MoveLast
        .MoveFirst
        'It is not necessary to move to the last record and then back
        'to the first one but it is good practice to do so.
        
        While (Not .EOF)
        'With this code, we are using a while loop to loop
        'through the records. If we reach the end of the recordset, .EOF
        'will return true and we will exit the while loop.
            
            Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
            'prints info from fields to the immediate window

            .MoveNext
            'We need to ensure that we use .MoveNext,
            'otherwise we will be stuck in a loop forever…
            '(or at least until you press CTRL+Break)
        Wend
        
    End If
    
    .Close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '..and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Exit While

To exit a while loop isn’t as trivial a task as with other looping structures.  To exit a While one must force the While expression to be false.

Sub whileLoopExit()
    Dim i As Long
    Dim kitchenItems(0 To 5) As String
    'We have created an array that can hold 6 elements
    
    Dim stayInLoop As Boolean
    'This is the variable we will use for the
    'condition of the while loop
   
    kitchenItems(0) = "Cooker"
    kitchenItems(1) = "Fridge"
    kitchenItems(2) = "Cutlery"
    kitchenItems(3) = "Crockery"
    kitchenItems(4) = "Dishwasher"
    kitchenItems(5) = "Table and Chairs"
    'Here we fill each element of the array
    
    i = 0
    
    stayInLoop = True
    'sets stayInLoop as true
    
    While (stayInLoop)
    
        'As long as stayInLoop resolves to true,
        'we will stay in the loop
        
        Debug.Print "Item " & CStr(i) & " is " & kitchenItems(i)
        'This line prints a string to the immediate window.
        'An example would be:
        'Item 4 is Dishwasher
        
        i = i + 1       

        If i = 3 Then
        'If, at any point, i becomes equal to 3, we will change
        'stayInLoop to false and exit the while loop
            stayInLoop = False
        End If        
        
    Wend


End Sub

Related Posts