Microsoft Office Tutorials and References
In Depth Information
Using Ranges
The following statement uses the Excel COUNTA function to determine the number of nonempty
cells in the worksheet:
NonEmpty =WorksheetFunction.COUNTA(Cells)
The Offset property
The Offset property (like the Range and Cells properties) also returns a Range object. The
Offset property is used in conjunction with a range. It takes two arguments that correspond to
the relative position from the upper-left cell of the specified Range object. The arguments can
be positive (down or right), negative (up or left), or zero. The following example returns the
value one cell below cell A1 (that is, cell A2) and assigns it to a variable named NextCell:
NextCell = Range(“A1”).Offset(1,0).Value
The following Function procedure accepts a single-cell argument and returns the sum of the
eight cells that surround it:
Function SumSurroundingCells(cell)
Dim Total As Double
Dim r As Long, c As Long
Total = 0
For r = –1 To 1
For c = –1 To 1
Total = Total + cell.Offset(r, c)
Next c
Next r
SumSurroundingCells = Total – cell
End Function
This function uses a nested For-Next loop. So, when the r loop counter is –1 , the c loop
counter goes from –1 to 1 . Nine cells are summed, including the argument cell, which is
Offset(0, 0) . The final statement subtracts the value of the argument cell from the total.
The function returns an error if the argument does not have eight surrounding cells (for example,
if it’s in row 1 or column 1).
To better understand how the nested loop works, following are nine statements that perform
exactly the same calculation:
Total = Total + cell.Offset(–1, –1) ‘ upper left
Total = Total + cell.Offset(–1, 0) ‘left
Total = Total + cell.Offset(–1, 1) ‘upper right
Total = Total + cell.Offset(0, –1) ‘above
Total = Total + cell.Offset(0, 0) ‘the cell itself

Search JabSto ::

Custom Search