Strange looping in User-Defined function (Excel XP)

[G.R. Toro]

Hi,

I am having a strange problem with this user-defined function. I have only
one cell in my excel file that references this function.

If I follow the function with the debugger, I observe the following strange
behavior:

1. it executes as expected and gets to the last line.

2. Then, instead of exiting, it goes back to the first line and executes
again

3. Then it goes to the first executable line once more instead of exiting,
executes part of the way, then exits abruptly (not sure on which line), and
displays a #VALUE! in the worksheet.

Why is it looping in such a strange way? Any suggestions?

I attach the code, in case I am missing something obvious.

Thanks,

Gabriel

Public Function DiscLifetimeLoss(years As Double, discRate As Double,
stormLosses As Range, StormRates As Range) As Variant
'
' New, faster version: simulates by storm, not by year
'
' years: design life (e.g., 50)
'
' discRate: annual discount rate
'
' stormLosses: range containing storm losses by storm (typically 162
storms)
'
' StormRates: range containing storm annual occurrence rate by storm
(typically 162 storms)
'
' Calls user-defined random-number generators PoissonRand and Random
'
'calculates one realization discounted lifetime loss from all discrete
storms
'storm information give as arrays of losses and their annual rates
'
Dim iyr As Integer
Dim istrm, nstorms As Integer
Dim i, n_per_life As Integer
Dim discount_fctr As Double
Dim sum As Double
Dim loss, time_strm As Double
Dim r As Single
'''''''''''''''''''''''''''''''''''''''''''''''''''''
nstorms = stormLosses.Count ' number of storms considered in analysis
'
sum = 0#
For istrm = 1 To nstorms
loss = stormLosses(istrm)
'simulate possible occurrences of each storm capable of losses
'during lieftime
If (loss > 0#) Then
'simulate number of times this storm uccurs during life time
n_per_life = PoissonRand(StormRates(istrm) * years)
'for each occurrence, we need to simulate the occurrence time (uniform
between 0 and years)
For i = 1 To n_per_life
time_strm = years * Random()
disc_fctr = (1 + discRate) ^ (-time_strm)
sum = sum + disc_fctr * loss
Next i
End If
Next istrm
DiscLifetimeLoss = sum

End Function

 

[G.R. Toro]

Update:

The function now runs and gives the anticipated results. I had to change
the logic in lower-level function PoissonRand so it would handle the case
where its argument is 0.

Still, I am not sure why the function executes 3 times (it still does) even
when it appears only once in the calling excel file.

Gabriel

 

[Jim Thomlinson]

Just a guess here but does the value returned by this function change
anything in the 2 ranges that the function receives as arguments? If so then
calling the function will cause a loop that will continue until the values
"Settle Down". Basically you get a recursive loop.

 

[G.R. Toro]

No,

The function does not change anything in these data ranges. On the other
hand, the ranges contain indirect()'s to data on another sheet. It may be
that Excel erroneously thinks that these ranges have changed.

Thanks for the suggestion.

Gabriel

 

[Jim Thomlinson]

Indirect is a volatile function. That means that it is always dirty and will
be recalculated every time that a calculation is run (similar to the Now()
function which re-evaluates with each calculation.). So as a guess this is
your culpret. When this formula calculates it causes the indirect functions
to recalculate, which changes the input range of the function and causes the
UDF to be evaluated once again...