Microsoft Office Tutorials and References
In Depth Information
Controlling Code Execution
TheDate = TheDate + 1
ActiveCell.Offset(1, 0).Activate
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 of 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)
ActiveCell = TheDate
TheDate = TheDate + 1
ActiveCell.Offset(1, 0).Activate
Loop While Month(TheDate) = Month(Date)
End Sub
The following is another Do While loop example. This procedure opens a text file, reads each
line, converts the text to uppercase, and then stores it in the active sheet, beginning with cell A1
and continuing down the column. The procedure uses the VBA EOF function, which returns True
when the end of the file has been reached. The final statement closes the text file.
Sub DoWhileDemo1()
Dim LineCt As Long
Dim LineOfText As String
Open “c:\data\textfile.txt” For Input As #1
LineCt = 0
Do While Not EOF(1)
Line Input #1, LineOfText
Range(“A1”).Offset(LineCt, 0) = UCase(LineOfText)
LineCt = LineCt + 1
Close #1
End Sub
For additional information about reading and writing text files using VBA, see
Chapter 27.
Search JabSto ::

Custom Search