Microsoft Office Tutorials and References

In Depth Information

Dim cell As Range

Dim cnt As Long

For Each cell In rng.Cells

If cell.Text Like pattern Then cnt = cnt + 1

Next cell

COUNTLIKE = cnt

End Function

The following formula counts the number of cells in B4:B11 that contain the letter
e:

=COUNTLIKE(B4:B11,”*[eE]*”)

Counting sheets in a workbook

The following COUNTSHEETS function accepts no arguments and returns the number of sheets in the work-

book from where it's called:

Function COUNTSHEETS() As Long

COUNTSHEETS = Application.Caller.Parent.Parent.Sheets.Count

End Function

This function uses Application.Caller to get the range where the formula was entered. Then it uses two Parent

properties to go to the sheet and the workbook. Once at the workbook level, the Count property of the Sheets

property is returned. The count includes worksheets and chart sheets.

Counting words in a range

The WORDCOUNT function accepts a range argument and returns the number of words in that range:

Function WORDCOUNT(rng As Range) As Long

‘

Count the words in a range of cells

Dim cell As Range

Dim WdCnt As Long

Dim tmp As String

For Each cell In rng.Cells

tmp = Application.Trim(cell.Value)

If WorksheetFunction.IsText(tmp) Then

WdCnt = WdCnt + (Len(tmp) – _

Len(Replace(tmp, “ “, “”)) + 1)

End If

Next cell

WORDCOUNT = WdCnt

End Function

I use a variable, tmp, to store the cell contents with extra spaces removed. Looping through the cells in the sup-

plied range, the ISTEXT worksheet function is used to determine whether the cell has text. If it does, the num-