Microsoft Office Tutorials and References
In Depth Information
The following Function procedure accepts a single-cell argument and returns the sum of the eight cells that sur-
round 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 sur-
rounding 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
Total = Total + cell.Offset(0, 1) ‘right
Total = Total + cell.Offset(1, –1) ‘lower left
Total = Total + cell.Offset(1, 0) ‘below
Total = Total + cell.Offset(1, 1) ‘lower right
Some useful properties of ranges
Previous sections in this chapter give you examples that used the Value property for a range. VBA gives you ac-
cess to many additional range properties. Some of the more useful properties for function writers are briefly de-
scribed in the following sections. For complete information on a particular property, refer to the VBA help sys-
tem.
The Formula property
The Formula property returns the formula (as a string) contained in a cell. If you try to access the Formula prop-
erty for a range that consists of more than one cell, you get an error. If the cell does not have a formula, this
property returns a string, which is the cell's value as it appears in the Formula bar. The following function
simply displays the formula for the upper-left cell in a range:
Search JabSto ::

Custom Search