The Range Object is one of the keys to being a productive Excel VBA programmer. Though powerful, it's also challenging to learn. Here are some helpful tips. First, named ranges. Naming a range is helpful in order to assign a friendly name to a group of cells and refer to the cells by that name later on. For instance, to select a range containing the user's data, it's a lot easier to write Range("UserData").Select than to keep track of the cell addresses, especially if code will be inserting or removing cells from the range. To create a named range first select the cells, then click in the range name drop-down (see Figure 3) and type in the name. Be sure to press return after typing or the name won't be assigned. To see how to do this in VBA, keep reading.
The first dialog in the Histogram Wizard needs to create a named range of all the contiguous cells around the currently-active cell. Tools like Excel's automatic subtotals and the Chart Wizard use this feature. Here's how to do it. First, place the active cell within the range. Then type something like the following:
Selection.CurrentRegion.Select
Selection.Name = "Bins1" 'or some other friendly name!
Range(Selection.Address).Name = "Bins1" 'This does the same thing
' in a different way.
The trick is to use the command Selection.CurrentRegion.Select , which selects all the contiguous non-blank cells around the active cell. In the second line, the selection property returns an object containing the current selection, in this case a Range object. The Range object has a Name property. In plain English, the second line of code above means "Assign the name Bins1 to a Range which is the current selection." There still may be times when a macro needs to get at the actual cell addresses. The third line of code illustrates the Address property of the Range object, which returns the address (for instance "$E$1:$E$4") of the selected cells.
There are times when Excel can be picky about range address formats. In the case of the FREQUENCY function, Excel insists on an R1C1-style reference. Fortunately, there's a special argument for the Address property that gives the desired result:
Selection.FormulaArray = "=FREQUENCY(" + Range("Data1").Address(True, _ True, xlR1C1) + "," + Range("Bins1").Address(True, True, xlR1C1) + ")"
What's odd is that typing in an A1-style reference manually for FREQUENCY works just fine, but VBA complains. So, be sure to try changing the reference style if unexplained errors happen and the range looks perfectly valid.
Use the Cells method to work with the cells in a range as if they were an array. The Cells method accepts a row and column address, but the address is local to the cells in the range itself. For instance, to assign the text "Data" to the cell in row 1, column 1 of the HCriteria range, use the code
Range("HCriteria").Cells(1, 1) = "Data"