Ranges

1. Ranges

2. Ranges • Unlike many of our programming concepts, the idea of a Range is particular to Excel • The ideas and code discussed in these slides can be found in demo ForEachForNext • We show how to use a For Each loop with a Range, and contrast with a For variable nested loop that will help prepare us to deal with arrays

3. A Range is a Collection of Cells • VBA in Excel has a data type Range • A Range can be a single cell, a block of cells, or a group of blocks of cells (we’ll stick with the first two possibilities) • You declare a Range variable in the usual way: DimworkAreaAs Range DimaCellAs Range

4. Using For Each with a Range • The For Each type of loop is designed to be used when you want to do the same thing to every item in a collection • A Range is a collection of cells • This loop clears all the cells in global range workArea: SubClearCells() Dim aCellAs Range For Each aCellInworkArea aCell.Value = Empty Next aCell End Sub

5. Put a 1 in Each Cell in a Range SubForEachDemo() DimaCellAs Range For Each aCellInworkArea aCell.Value = 1 NextaCell End Sub

6. Treat Cells Differently • If you don’t want to do the same exact thing with each cell, then you need a different kind of loop • For loops using variables work very well in this case • For a range that is just a single row or column, one loop is fine. For a two-dimensional range, you need nested loops

7. One-Dimensional Example (row) • To set elements 1 to size of the first row to two times their column index: ForcolNdx = 1 Tosize Cells(1, colNdx) = 2 * colNdx NextcolNdx

8. Two-Dimensional Example SubForVariableDemo() DimrowNdx, colNdxAs Long For rowNdx = 1 To size ForcolNdx = 1 To size Cells(rowNdx, colNdx).Value = rowNdx + colNdx Next colNdx NextrowNdx End Sub

9. These are Simple Examples • You could use a much more complex formula instead of the simple ones we used in these examples • For example, you could compute an amortization table or a projection of building an interest-bearing account under different scenarios you might program