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

Otto,

I have replied directly to you on this.

Bob

"Otto Moehrbach" wrote in message
...
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

It actually occurs if you have any statement in Calculate that could cause a
calculate to occur I believe. I changed the calculate event to just put a
value in a cell and it still caused a problem. I changed it back and put in
debug.print statements to indicate when each piece of code fired. then did
a full calculation with Ctrl+Alt+F9. It produced

in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate

many many more repetitions.

If I remove the volatile from the function, I don't have a problem. So it
appears you are getting are recursive interaction.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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

Thanks Tom. I came to the same conclusion while waiting for a reply. Otto
"Tom Ogilvy" wrote in message
...
It actually occurs if you have any statement in Calculate that could cause
a
calculate to occur I believe. I changed the calculate event to just put
a
value in a cell and it still caused a problem. I changed it back and put
in
debug.print statements to indicate when each piece of code fired. then
did
a full calculation with Ctrl+Alt+F9. It produced

in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate
in Pupil
In Calculate

many many more repetitions.

If I remove the volatile from the function, I don't have a problem. So it
appears you are getting are recursive interaction.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
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