Looping structures like the VBA Do Loop allow you to specify the conditions under which you would like your program to run a set of code statements repeatedly.

There are 4 different variations of the VBA Do Loop we are going to look at:

‘Do While…Loop structures check the condition before running the code.
‘The code in the loop is run only if the condition evalutes as True, and
‘repeats until the test expression evaluates as False…Example…

Private Sub DoWhile()
    Dim intItems As Integer
    
    Do While intItems < 25
        MsgBox "You have to order a minimum of 25. " & _
            "You have ordered " & intItems & " so far."
    
        intItems = intItems + InputBox("How many more items do you need?")
    Loop
    
    MsgBox "That's the correct amount."
    
    
End Sub

‘Do Until…Loop structures check the condition before running the code.
‘The code in the loop is run only if the condition evaluates as False,
‘and repeats until the test expression evaluates as True…Example…

Public Sub DoUntil()
    Dim strPassword As String
    Dim strCorrectPassword As String
    
    strCorrectPassword = "pass123"
    
    'Make sure you ask the question once, or else the user will go directly to the invalid message, _
        and probably get confused.
    strPassword = InputBox("Enter the password")
     
    Do Until strPassword = strCorrectPassword
        MsgBox "That's not the correct password, try again."
        strPassword = InputBox("Enter the password")
        
    Loop
    
    MsgBox "Welcome to the site!"
    
End Sub

‘Do…Loop While structures check the condition after running the code and
‘repeat a code block until the test expression evaluates as False…Example…

Public Sub DoLoopWhile()
    Dim strAnswer As String
    
    Do
        MsgBox "You must answer the question, before continuing: "
        strAnswer = InputBox("4 + 6 / 2 = ?")
    
    Loop Until strAnswer = "7"
    
    MsgBox "Correct"
    
End Sub

‘Do…Loop Until structures check the condition after running the code and
‘repeat a code block until the test expression evaluates as True.

Public Sub DoLoopUntil()
    Dim strSearchText As String
    Dim intPos As Integer
    
    'Useful for parsing csv, or other delimited files...
    
    strSearchText = "1990, Cadillac, Seville, Gold"
    intPos = 0
    
    Do
        intPos = InStr(intPos + 1, strSearchText, ",")
        MsgBox "Comma at position: " & intPos
        
    Loop Until intPos = 0
End Sub

Watch the video:

Exit a VBA Do Loop by using the Exit Do statement.

<< VBA Error Handling PT2 | VBA For Loop >>

Sign Up For A FREE MS Access VBA Video Tutorial Subscription. You will get over 27 tutorial videos that will help you do your job better! Start Yours Now At:
http://vbahowto.com/ms-access-tutorial