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: