Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

A cell’s background color is not part of the
Font
object; it’s stored in the
Interior

object. This function returns
True
if the upper-left cell of its argument is colored red

(
vbRed
is a built-in constant):

Function ISREDBKGRD(cell)

ISREDBKGRD = cell.Range(“A1”).Interior.Color = vbRed

End Function

The Columns and Rows properties

The
Columns
and
Rows
properties work with columns or rows in a range. For example, the

following function returns the number of columns in a range by accessing the
Count
property:

Function COLUMNCOUNT(rng)

COLUMNCOUNT = rng.Columns.Count

End Function

The EntireRow and EntireColumn properties

The
EntireRow
and
EntireColumn
properties enable you to work with an entire row or

column for a particular cell. The following function accepts a single cell argument and then uses the

EntireColumn
property to get a range consisting of the cell’s entire column. It then uses the

Excel COUNTA function to return the number of nonempty cells in the column.

Function NONEMPTYCELLSINCOLUMN(cell)

NONEMPTYCELLSINCOLUMN = WorksheetFunction.CountA(cell.EntireColumn)

End Function

You cannot use this function in a formula that’s in the same column as the cell argument. Doing

so will generate a circular reference.

The Hidden property

The
Hidden
property is used with rows or columns. It returns TRUE if the row or column is

hidden. If you try to access this property for a range that does not consist of an entire row or

column, you get an error. The following function accepts a single cell argument and returns TRUE if

either the cell’s row or the cell’s column is hidden:

Function CELLISHIDDEN(cell)

If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then

CELLISHIDDEN = True

Else

CELLISHIDDEN = False

End If

End Function