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: