100%(1)1 out of 1 people found this document helpful
This preview shows page 22 - 25 out of 28 pages.
M.22 Extended Learning Module MLOOPING The term loopingrefers to repeating a block of statements or code numerous times. You may know how many times your macro needs to loop, or variables used in your programs may determine this. There are several looping statements to choose from: the For-Nextloop, the Do-Whileloop, and the Do-Untilloop. FOR-NEXT LOOPS The simplest type of loop is a For-Nextloop. Here’s the syntax for this structure:For Counter =Start To End [Step n] [statements] Next [Counter] The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the Forstatement and the Nextstatement are the statements that get repeated in the loop. The following example shows a For-Nextloop that doesn’t use the optional Stepvalue. This Sub procedure loops 100 times and uses the VBA Rndfunction to enter a random number into 100 cells:Sub FillRange() Dim Count As Integer For Count =1 To 100 ActiveCell.Offset(Count −1, 0) =Rnd Next Count End Sub In this example, Count(the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because you didn’t specify a Step value, VBA uses the default value (which is 1). The Offsetmethod uses the value of Countas an argument. The first time through the loop, the procedure enters a number into the active cell offset by zero rows. The second time through (Count =2), the procedure enters a number into the active cell offset by one row (Count –1), and so on. DO-WHILELOOPS VBA supports another type of looping structure known as a Do-Whileloop. Unlike a For-Nextloop, a Do-Whileloop continues until a specified con-dition is met. Here’s the Do-Whileloop syntax:Do [While condition] [statements] Loop The following example uses a Do-Whileloop within a Sub procedure. This proce-dure uses the active cell as a starting point and then travels down the column, multiply-ing each cell’s value by 2. The loop continues until the procedure encounters an empty cell.Sub DoWhileExample() Do While ActiveCell.Value <> Empty ActiveCell.Value =ActiveCell.Value * 2 ActiveCell.Offset(1, 0).Select Loop End Sub
Decisions, Decisions, Decisions M.23DO-UNTILLOOP The Do-Untilloop structure is similar to the Do-Whilestructure. The two structures differ in their handling of the tested condition. A macro continues to execute a Do-Whileloop while the condition remains true. In a Do-Untilloop, the macro executes the loop until the condition is true. Here’s the Do-Untilsyntax:Do [Until condition] [statements] Loop The following example is the same one presented for the Do-Whileloop but recoded to use a Do-Untilloop:Sub DoUntilExample() Do Until IsEmpty (ActiveCell.Value) ActiveCell.Value =ActiveCell.Value * 2 ActiveCell.Offset(l, 0).Select Loop End Sub
M.24 Extended Learning Module MWrap It Up Figure M.11 displays a simple worksheet created to calculate the invoice amount of three products based on several parameters. The lookup table in cells A5:D7 gives the price of each product, the discount sales volume (above which a discount will be applied), and the percent discount for units above the discount volume. Using normal spreadsheet for-