In this example, I used the NOT keyword. EOF(l) evaluates to True after there are no more records to be read from invoice.txt. Some programmers feel it hard to read a program that contains a lot of NOTs. To avoid the use of NOT, use the Do Until <test expression>...Loop construct:

' Read a text file, skipping the Total lines Open "C:\Invoice.txt" For Input As #1 R = 1

Do Until EOF(1)

Line Input #1, Data If Not (Data, 5) = "TOTAL" then ' Import this row r = r + 1

Loop Close #1

In other examples, you always want the loop to be executed the first time. In these cases, you move the While or Until instruction to the end of the loop. This code sample asks the user to enter sales amounts made that day. It continually asks them for sales amounts until they enter a 0:

TotalSales = 0 Do x = InputBox(Prompt:="Enter Amount of Next Invoice. Enter 0 when done.") TotalSales = TotalSales + x Loop Until x = 0

MsgBox "The total for today is $" & TotalSales

In the following loop, a check amount is entered and then it looks for open invoices to which to apply the check. It is often the case that a single check is received and it covers many invoices. This program sequentially applies the check to the oldest invoices until 100% of the check has been applied.

1 Ask for the amount of check received AmtToApply = InputBox("Enter Amount of Check") ' Loop through the list of open invoices. 1 Apply the check to the oldest open invoices. ' Decrement AmtToApply NextRow = 2

Do While AmtToApply > 0

OpenAmt = Cells(NextRow, 3) If OpenAmt > AmtToApply Then

' Apply total check to this invoice Cells(NextRow, 4).Value = AmtToApply AmtToApply = 0


Cells(NextRow, 4).Value = OpenAmt AmtToApply = AmtToApply - OpenAmt End If


Because you can construct the Do...Loop with the While or Until qualifiers at the beginning or end, you have a lot of subtle control over whether the loop is always executed once, even if the condition is True at the beginning.

0 0

Post a comment