Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange looping in User-Defined function (Excel XP)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange looping in User-Defined function (Excel XP)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange looping in User-Defined function (Excel XP)
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. -- HTH... Jim Thomlinson "G.R. Toro" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange looping in User-Defined function (Excel XP)
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" wrote in message ... 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. -- HTH... Jim Thomlinson "G.R. Toro" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange looping in User-Defined function (Excel XP)
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... -- HTH... Jim Thomlinson "G.R. Toro" wrote: 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" wrote in message ... 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. -- HTH... Jim Thomlinson "G.R. Toro" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Calling Excel user defined function from C# | Excel Programming | |||
Looping through a user defined type? | Excel Programming | |||
How can I create a user defined function in excel? | Excel Discussion (Misc queries) | |||
How to create User Defined function in Excel | Excel Programming |