Referencing named ranges

There are a number of ways to reference a range in VBA. Some of these are:

♦ Using a named range - follow the Range object with the named range in double quotation marks. For example, Range("week_sales")

♦ Use a cell address range - the same as above, but use a cell address reference range. For example, Range("D1:D5")

♦ Use a Range object variable (see Chapter 5). To create a Range object variable called myRange, you can use statements of the following form:

Dim RandomRange As Range Set RandomRange = Range("b1:b10")

♦ Use the Cells property of the Range object. This property can be used with either one or two arguments. For example, you could write: Range ("week_sales").Cells (5). If we look at Figure 4.5, the value returned will be 32, because this is the value of the contents of the cell in the fifth position of this range. Note that rectangular ranges are scanned from row to row. You can also use the Cells property with two arguments. For example, if we had Range ("week_sales").Cells (2, 3) then the value returned from this cell would be 66, because this is the value of the second row, third column cell in the range week_sales.

♦ Use the Offset method. This method works with two arguments and has the form Offset(x,y) where x is the rowOffset - the number of rows to go down (up if x is <0), and y is the columnOffset - the number of columns across to the right (to the left if y<0).

For example, in Figure 4.5, Range("rep_name").0ffset(0,1) would begin in the range rep_name and the rowOffset is 0, but the columnOffset is 1. This means it would actually refer to the next column to the right (because it is+1), and therefore the sales_to_date range.

0 0

Post a comment