Microsoft Office Tutorials and References
In Depth Information
Using Ranges
The following Function procedure accepts a range argument and returns the sum of the
squared values in the range:
Function SUMOFSQUARES(rng as Range)
Dim total as Double
Dim cell as Range
total = 0
For Each cell In rng
total = total + cell ^ 2
Next cell
SUMOFSQUARES = total
End Function
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:
h Using the Range property
h Using the Cells property
h 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 example 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 example, the following statement counts the number of cells in a range and assigns
the value to the Cnt variable:
 
Search JabSto ::




Custom Search