1 / 9

Ranges

Ranges. 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

sylvie
Download Presentation

Ranges

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related