Nested Loops

So far in this chapter you have tried out various loops. Each procedure demonstrated the use of each individual looping structure. In programming practice, however, one loop is often placed inside of another. Visual Basic allows you to "nest" various types of loops (For and Do loops) within the same procedure. When writing nested loops, you must make sure that each inner loop is completely contained inside the outer loop. Also, each loop has to have its own unique counter variable. When you use nesting loops, you can often execute the specific task more effectively.

The ColorLoop procedure shown below illustrates how one For.. .Next loop is nested within another For.. .Next loop:

Sub ColorLoop() Dim myRow As Integer Dim myCol As Integer Dim myColor As Integer myColor = 0 For myRow = 1 To 8 For myCol = 1 To 7

Cells(myRow, myCol).Select myColor = myColor + 1 With Selection.Interior .ColorIndex = myColor .Pattern = xlSolid End With Next myCol Next myRow End Sub

The ColorLoop procedure shown above uses two For.. .Next loops to change the color of each cell located in the first eight rows and seven columns of a worksheet. While the outer loop keeps track of the row number, the inner loop does more than one thing. It determines the current column number, selects the appropriate cell based on the current row and column index, keeps track of the color index, and then applies the color to the selected cell.

The inner For.. .Next loop applies a different color to seven cells in the first spreadsheet row (A1, B1, C1, D1, E1, F1, and G1). When the variable myCol is greater than 7, Visual Basic jumps back to the outer loop to increment the variable myRow by one and returns to the inner loop to color the next set of cells in the second row. When the procedure ends, 56 cells (8*7) are formatted with all the colors available in the current color palette. The first cell, A1, is formatted with a black color (color index number 1). The second cell, B1, is formatted as white (color index number 2). Each time the cell address changes—Cells(myRow, myCol).Select—the contents of the variable myColor change as well—myColor = myColor + 1.

0 0

Post a comment