Excel 2019 Power Programming with VBA. Michael Alexander

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.

      Sub EnterDates1() ' 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 TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub

      This procedure uses a variable, TheDate, which contains the dates that are written to the worksheet. This variable is initialized with the first day of the current month. Inside the loop, the value of TheDate is entered into the active cell, TheDate is incremented, and the next cell is activated. The loop continues while the month of TheDate is the same as the month of the current date.

      The following procedure has the same result as the EnterDates1 procedure, but it uses the second Do While loop syntax, which checks the condition at the end of the loop.

      Sub EnterDates2() ' Do While, with test at the end Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop While Month(TheDate) = Month(Date) End Sub

Do While loops can also contain one or more Exit Do statements. When an Exit Do statement is encountered, the loop ends immediately, and control passes to the statement following the Loop statement.

      Do Until loops

      The Do Until loop structure is similar to the Do While structure. The difference is evident only when the condition is tested. In a Do While loop, the loop executes while the condition is True; in a Do Until loop, the loop executes until the condition is True.

      Do Until also has two syntaxes. Here's the first way:

      Do [Until condition] [instructions] [Exit Do] [instructions] Loop

      Here's the second way:

      Do [instructions] [Exit Do] [instructions] Loop [Until condition]

      The two examples that follow perform the same action as the Do While date entry examples in the previous section. The difference in these two procedures is where the condition is evaluated (at the beginning or the end of the loop). Here is the first example:

      Sub EnterDates3() ' Do Until, with test at beginning Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(TheDate) <> Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Loop End Sub

      Here is the second example:

      Sub EnterDates4() ' Do Until, with test at end Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) Do ActiveCell = TheDate TheDate = TheDate + 1s ActiveCell.Offset(1, 0).Activate Loop Until Month(TheDate) <> Month(Date) End Sub

The following example was originally presented for the Do While loop but has been rewritten to use a Do Until loop. The only difference is the line with the Do statement. This example makes the code a bit clearer because it avoids the negative required in the Do While example.

      Sub DoUntilDemo1() Dim LineCt As Long Dim LineOfText As String Open "c:\data\textfile.txt" For Input As #1 LineCt = 0 Do Until EOF(1) Line Input #1, LineOfText Range("A1").Offset(LineCt, 0) = UCase(LineOfText) LineCt = LineCt + 1 Loop Close #1 End Sub

      NOTE

      VBA supports yet another type of loop, While Wend. This looping structure is included primarily for compatibility purposes. Here's how the date entry procedure looks when it's coded to use a While Wend loop:

      Sub EnterDates5() Dim TheDate As Date TheDate = DateSerial(Year(Date), Month(Date), 1) While Month(TheDate) = Month(Date) ActiveCell = TheDate TheDate = TheDate + 1 ActiveCell.Offset(1, 0).Activate Wend End Sub