Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
Do While loops
This section describes another type of looping structure available in VBA. Unlike a For-Next
loop, a Do While loop executes as long as a specified condition is met.
A Do While loop can have either of two syntaxes:
Do [While condition]
Loop [While condition]
As you can see, VBA lets you put the While condition at the beginning or the end of the loop.
The difference between these two syntaxes involves the point in time when the condition is
evaluated. In the first syntax, the contents of the loop may never be executed. In the second syntax,
the statements inside the loop are always executed at least one time.
The following examples insert a series of dates into the active worksheet. The dates correspond to
the days in the current month, and the dates are entered in a column beginning at the active cell.
These examples use some VBA date-related functions:
● Date returns the current date.
● Month returns the month number for a date supplied as its argument.
● DateSerial returns a date for the year, month, and day supplied as arguments.
The first example demonstrates a Do While loop that tests the condition at the beginning of the
loop: The EnterDates1 procedure writes the dates of the current month to a worksheet
column, beginning with the active cell.
‘ Do While, with test at the beginning
Dim TheDate As Date
TheDate = DateSerial(Year(Date), Month(Date), 1)
Do While Month(TheDate) = Month(Date)
ActiveCell = TheDate