For Each and Looping through a Range

As stated at the beginning of this chapter, objects are often built from other objects. For example a Workbook object usually contains several Worksheet objects, which in turn contains multiple Range objects. It may be necessary, on occasion, to select individual objects contained within other objects. For example, you may want to access each individual Worksheet object in a Worksheets collection object in order to set certain properties. If you are thinking loops then you are right on track, but you're not going to use any of the looping structures previously discussed. Instead, you'll use a looping structure specifically designed to iterate through collections. The loop is the For/Each loop, and its use is illustrated in the example that follows:

Dim myRange As Excel.Range Dim myCell As Excel.Range


Set myRange = Range("A1:B15") For Each myCell In myRange myCell.Interior.Colorlndex = Int(Rnd * 56) + 1


In this example, the background of a group of cells is changed to all different colors. To accomplish this, each cell is accessed individually as a Range object before setting the Colorlndex property of the Interior object. The For/Each loop is used for this purpose.

Two object references are required with the For/Each loop; one for the individual objects, and the other for the collection of objects. In this example, the object variable myRange represents a collection of cells while the object variable myCell represents each individual cell within myRange.

The reference to the object variable myRange must be set (cells A1 through B15 in this example) before it can be used in a For Each loop.

The loop begins with the keywords For Each, followed by the variable that is to represent the individual elements in the collection—myCell in this example. The keyword In is followed by the name of the collection—myRange in this example. Note, that it is not necessary to set the object reference to the variable myCell, as VBA handles this automatically in the For Each loop.

Inside the loop, properties and methods of the individual elements can be addressed. In this case, the ColorIndex property of the Interior object is changed using a randomly generated number between 1 and 56 (there are 56 colors in Excel's color palette). Once each statement within the loop is executed, the Next keyword is used to continue the loop.

VBA iterates through the cells in the collection first by row and then by column. Therefore, in this example, the order follows A1, B1, A2, B2, A3, B3, and so on.

When all elements of the collection have been accessed and each statement executed, program execution resumes at the end of the loop as normal. The above code was added to a standard module in a sub procedure named CellColors() and executed. Figure 5.15 shows the result.

The result of executing the

CellColors() sub procedure.

The result of executing the

CellColors() sub procedure.

This a common technique for iterating through a collection of spreadsheet cells. You will see more examples of this technique in the Battlecell program.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment