M22 Extended Learning Module M LOOPING The term looping refers to repeating a

M22 extended learning module m looping the term

This preview shows page 22 - 25 out of 28 pages.

M.22 Extended Learning Module M LOOPING The term looping refers 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-Next loop, the Do-While loop, and the Do-Until loop. FOR-NEXT LOOPS The simplest type of loop is a For-Next loop. 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 For statement and the Next statement are the statements that get repeated in the loop. The following example shows a For-Next loop that doesn’t use the optional Step value. This Sub procedure loops 100 times and uses the VBA Rnd function 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 Offset method uses the value of Count as 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-WHILE LOOPS VBA supports another type of looping structure known as a Do-While loop. Unlike a For-Next loop, a Do-While loop continues until a specified con- dition is met. Here’s the Do-While loop syntax: Do [While condition] [statements] Loop The following example uses a Do-While loop 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
Image of page 22
Decisions, Decisions, Decisions M.23 DO-UNTIL LOOP The Do-Until loop structure is similar to the Do-While structure. The two structures differ in their handling of the tested condition. A macro continues to execute a Do-While loop while the condition remains true. In a Do-Until loop, the macro executes the loop until the condition is true. Here’s the Do-Until syntax: Do [Until condition] [statements] Loop The following example is the same one presented for the Do-While loop but recoded to use a Do-Until loop: Sub DoUntilExample() Do Until IsEmpty (ActiveCell.Value) ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(l, 0).Select Loop End Sub
Image of page 23
M.24 Extended Learning Module M Wrap 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-
Image of page 24
Image of page 25

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture