Excel XP & Win XP
I have a problem that Bob Phillips has been kind enough to help me with.
We're still working on it. I have narrowed it down significantly and
thought it would be helpful to post this to the whole newsgroup along with a
copy to Bob.
Below are two small procedures, one a Worksheet_Calculate macro and the
other a UDF. They both do the same thing: Count the number of cells in a
range (the same range).

Private Sub Worksheet_Calculate()
Range("D2").Value = Range("A8", Range("A" & Rows.Count).End(xlUp)).Count
End Sub

Function PupilCount() As Long
Application.Volatile
PupilCount = Range("A8", Range("A" & Rows.Count).End(xlUp)).Count
End Function


To manifest the problem, please do the following:
Open a new blank workbook (only one sheet is needed).
In Column A, starting with A8 down, fill a few cells with any text or
numbers you want.
Copy/Paste the above WorkSheet_Calculate procedure into the sheet module.
In any blank cell type =1+2 to force a calculation.
Note that cell D2 displays the cell count of Col A.
Remark-out the WorkSheet_Calculate procedure. (IMPORTANT)
Insert a regular module.
Copy the above Function into that module.
In any blank cell, type =PupilCount() and hit Enter.
The same cell count is displayed in the cell.
Clear that cell. (IMPORTANT)
UnRemark-out the WorkSheet_Calculate procedure.
Now you have both procedures active.
In any blank cell, type =PupilCount() and hit Enter.
I get the count for just a moment, then a VALUE error in the cell.
Why?

Thanks for your help. Otto