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