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.