Thanks Nick for the help. I am quite sure, the function is used for
once. Let me re-phraze what i wrote earlier.
I have this function, which works fine if I calculate that by going
into that cell and hitting enter. However, when I use calculate all
(F9), the status bar suggests 0% calculation and virtually stops there.
When I go to debug mode, the breaking points suggest it keeps on
calling the same function again(like recursive calling).
The execution goes like this, it calculates the "expiration" part, then
the control goes to the other sheet and start calculating the formulas
in the other sheet and then the control comes back to the function I
wrote, and it stays there.
I cannot figure this out that why it can calculate if I go cell by
cell, and why not when it calculates the formulas for the whole of the
workbook.
Any help would be a great help,
Thanks


NickHK wrote:
Not sure I follow, but the function will called once for wach occurence on
the worksheets.
So if you have 10 formulae with this function, it will be called 10 times.
OK, giving the same each time, I would imagine.
Is that what you mean ?

NickHK
P.S. For me, it would be better to make it clear that you are returning a
value from the function:
Function DTS_Message() As String


egroups.com...
I have written down a function which checks for "expiration" and other
like statements from another sheet within the same workbook. It
calculates that fine if I calculate that cell be cell. However, if I
calculate for the whole workbook, the calculation bar stops at 0%. When
I go into the debug mode, it takes me to the function which I wrote for
checking expiration, and then keep repeating in the same function. I
have run step by step execution, and I have found out that the control
goes back to the start even after it passes the return statement.
Someone, please help.
Here is my code:

Function DTS_Message()
sheet_name = "DTS"
count_exp = 0
count_caution = 0
' checking for negative value, if any. That shows if the value has
expired or not.
For Each cell In Sheets(sheet_name).Range("U:V")
If cell.value < 0 Then
count_exp = count_exp + 1
End If
Next cell
'if negative value found then go for expiration message.
If count_exp 0 Then
DTS_Message = "WARNING a Discard Time Requirement(s) has
expired!"
'if not then check for other condition i.e. caution flag
Else
For Each cell In Sheets(sheet_name).Range("Z:Z")
If cell.value = "Caution flag" Then
count_caution = count_caution + 1
End If
Next cell
'if there is a caution flag found then display this
If count_caution 0 Then
DTS_Message = "CAUTION a Discard Time Requirement(s) is
expiring shortly!"
Else
DTS_Message = "Discard Time Requirements are OK"
End If

End If


End Function