In part it depends on what comes next; i.e. what does your code or the user
do with the result. The test for non-convergence should be obvious:

do
it=it+1
iteration code to find next value from old value
loop until convergeance criteria or it=100
If it=100 Then
' non-convergence result
Else
' regular result
End If
end function

The message box works if what you need to do is to alert the user; but not
so good if you need your code to respond to the error result.

If you need the code to respond, there are 2 basic things you can do:
1) If possible, and it may not be, have a distinctive return value that
flags the result of the function as meaning "did not converge." For example,
if the normal function would ALWAYS return a positive value, you could use -1
as a flag value to show it had not converged:
If it=100 Then
myudf = -1
Else
myudf=result
End If
end function

This avoids raising an error, but it can only be used for specific
circumstances and the person writing the code must know the return value that
flags it as not having convereged.

Probably the most rigorous, general use solution is to use an error. Two
different approaches are possible: Raise an error with Err.Raise
vbObjectError+something and then use error trapping to deal with error
results (test the error number to find if it was due to non-conveergence); or
set the function return value to a variant and use myudf=CvErr() to return a
particular error type, which could be tested with The IsError() function.
This approach has the advantage of working well as an Excel user function on
a worksheet, since it will lead to the #ERR! result and not bring anything to
a crashing halt.

I won't try to decide for you; but my general thought is:
Need user to respond or be alerted: use messagebox
Wish for code to handle without dealing with errors: use flag value, if
possible
Flag value not possible, or you want error to be raised, especially for
worksheet function: raise an error or return an error value.

HTH!
--
- K Dales


"MrShorty" wrote:


I have a handful of UDF's that use iterative algorithms to numerically
arrive at a solution (usually either a Newton-Raphson type algorithm or
successive approximations). I like to put a check on the number of
iterations, to prevent getting stuck in an infinite loop should the
problem diverge. Generic function looks something like:

function myudf(arglist, optional init)
if ismissing(init) then init= some reasonable initial guess for generic
problems of this sort
code setting up initial values for the iteration
it=0
do
it=it+1
iteration code to find next value from old value
loop until convergeance criteria or it=100
myudf=result
end function

What I would like to add is something to let me know when the
convergeance criteria hasn't been met, but rather the function has
ended because we've reached the maximum number of iterations. I'm just
looking for ideas. How would you inform me that the function reached
maxiterations without converging? message box? have the function
return an error? other possibility?

Thanks in advance.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=495594