Loops – While Wend

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


The basic syntax of a While loop is:

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
Item 0 is Cooker
Item 1 is Fridge
Item 2 is Cutlery
Item 3 is Crockery
Item 4 is Dishwasher
Item 5 is Table and Chairs

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
1 Anna
2 Antonio
3 Thomas
4 Christina
5 Martin
6 Francisco
7 Ming-Yang
8 Elizabeth
9 Sven

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
Item 0 is Cooker
Item 1 is Fridge
Item 2 is Cutlery

 

Related Posts

DoEvents
Looping Through a Recordset
Loops – Do Until / Do While / Do
Loops – For Each
Loops – For Next
Nested Loops
What are Recordsets

As always, a site wouldn't be anything without its users so please feel free to comment! We welcome any comments you have on the material and any suggestions you may have for future content.


In our humble opinion, it is worth commenting just so you can play with Fun Captcha below!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>