Microsoft Office Tutorials and References
In Depth Information
The following is a worksheet formula that uses the SUMOFSQUARES function:
=SUMOFSQUARES(A1:C100)
In this case, the function's argument is a range that consists of 300 cells.
In the preceding example, cell and rng are both variable names. There's nothing special
about either name; you can replace them with any valid variable name.
Referencing a range
VBA code can reference a range in a number of different ways:
• Using the Range property
• Using the Cells property
• Using the Offset property
The Range property
You can use the Range property to refer to a range directly by using a cell address or name. The following ex-
ample assigns the value in cell A1 to a variable named Init. In this case, the statement accesses the range's Value
property.
Init = Range(“A1”).Value
In addition to the Value property, VBA enables you to access a number of other properties of a range. For ex-
ample, the following statement counts the number of cells in a range and assigns the value to the Cnt variable:
Cnt = Range(“A1:C300”).Count
The Range property is also useful for referencing a single cell in a multicell range. For example, you may create
a function that is supposed to accept a single-cell argument. If the user specifies a multicell range as the argu-
ment, you can use the Range property to extract the upper-left cell in the range. The following example uses the
Range property (with an argument of “A1”) to return the value in the upper-left cell of the range represented by
the cell argument.
Function SQUARE(cell as Range)
CellValue = cell.Range(“A1”).Value
SQUARE = CellValue ^ 2
End Function
Assume that the user enters the following formula:
=SQUARE(C5:C12)
Search JabSto ::




Custom Search