DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

1. Rafi Guest

## Looping function

Does any one know why when the following function is called in excel it returns
#value for values greater than 5986? I would like to see how long it will
take to run a lot of nested functions and I thought this would be a way to
test.

Function x(t As Long) As long
If t = 0 then
x = 1
Else
x = x(t-1)
End if
End function

Thanks for the help, Rafi

2. Arthur Wood Guest

## Re: Looping function

Rafi,
This function DOES not actually return any meaningful value, other than
1, no matter WHAT value of the argument you supply---it recursively calls
itself, with the argument decreasing by 1 each time until it the argument
gets to 0, and then it returns the value 1.

I assume that you were attempting to compute the FACTORIAL function, in which
case you should change the function to

Function x(t As Long) As long
If t = 0 then
x = 1
Else
x = t * x(t-1) ' NOTE THIS CHANGE
End if
End function

and then you will get an OVERFLOW error when the inital argument is 13, no
where close to 5986. 13! is LARGER that a value whcih can be held in a LONG
data type.

Arthur Wood

"Rafi" <randyh@netvision.net.il> wrote:
>
>Does any one know why when the following function is called in excel it

returns
>#value for values greater than 5986? I would like to see how long it will
>take to run a lot of nested functions and I thought this would be a way

to
>test.
>
>Function x(t As Long) As long
> If t = 0 then
> x = 1
> Else
> x = x(t-1)
> End if
>End function
>
>Thanks for the help, Rafi

3. Jon Oliver Guest

## Re: Looping function

"Rafi" <randyh@netvision.net.il> wrote in message
news:3c3acf5b\$1@147.208.176.211...
>
> Does any one know why when the following function is called in

excel it returns
> #value for values greater than 5986? I would like to see how

long it will
> take to run a lot of nested functions and I thought this would

be a way to
> test.
>
> Function x(t As Long) As long
> If t = 0 then
> x = 1
> Else
> x = x(t-1)
> End if
> End function
>

A call stack of 5986 recursions seems fairly deep--perhaps
you've met a limit.

4. Sue Harsevoort Guest

## Re: Looping function

Actually from his message it doesn't really sound like he is looking for a
meaningful value. He is just using this to time how long a nested loop will
run.

When I did some test with it I was able to use higher numbers, though it
varied. I ran the function in the immediate window and received an Out Of
Stack Space message, so that looks like what the problem is.

Sue

"Arthur Wood" <wooda@saic-trsc.com> wrote in message
>
> Rafi,
> This function DOES not actually return any meaningful value, other than
> 1, no matter WHAT value of the argument you supply---it recursively calls
> itself, with the argument decreasing by 1 each time until it the argument
> gets to 0, and then it returns the value 1.
>
> I assume that you were attempting to compute the FACTORIAL function, in

which
> case you should change the function to
>
>
> Function x(t As Long) As long
> If t = 0 then
> x = 1
> Else
> x = t * x(t-1) ' NOTE THIS CHANGE
> End if
> End function
>
>
> and then you will get an OVERFLOW error when the inital argument is 13, no
> where close to 5986. 13! is LARGER that a value whcih can be held in a

LONG
> data type.
>
> Arthur Wood
>
>
> "Rafi" <randyh@netvision.net.il> wrote:
> >
> >Does any one know why when the following function is called in excel it

> returns
> >#value for values greater than 5986? I would like to see how long it

will
> >take to run a lot of nested functions and I thought this would be a way

> to
> >test.
> >
> >Function x(t As Long) As long
> > If t = 0 then
> > x = 1
> > Else
> > x = x(t-1)
> > End if
> >End function
> >
> >Thanks for the help, Rafi

>

5. Arthur Wood Guest

## Re: Looping function

Sue,

That would fit with attempting to run the Recursion TOO many levels deep.
Not generally a good idea.

Arthur

"Sue Harsevoort" <SusannaH67@hotmail.com> wrote:
>Actually from his message it doesn't really sound like he is looking for

a
>meaningful value. He is just using this to time how long a nested loop

will
>run.
>
>When I did some test with it I was able to use higher numbers, though it
>varied. I ran the function in the immediate window and received an Out

Of
>Stack Space message, so that looks like what the problem is.
>
>Sue
>
>"Arthur Wood" <wooda@saic-trsc.com> wrote in message
>>
>> Rafi,
>> This function DOES not actually return any meaningful value, other

than
>> 1, no matter WHAT value of the argument you supply---it recursively calls
>> itself, with the argument decreasing by 1 each time until it the argument
>> gets to 0, and then it returns the value 1.
>>
>> I assume that you were attempting to compute the FACTORIAL function, in

>which
>> case you should change the function to
>>
>>
>> Function x(t As Long) As long
>> If t = 0 then
>> x = 1
>> Else
>> x = t * x(t-1) ' NOTE THIS CHANGE
>> End if
>> End function
>>
>>
>> and then you will get an OVERFLOW error when the inital argument is 13,

no
>> where close to 5986. 13! is LARGER that a value whcih can be held in

a
>LONG
>> data type.
>>
>> Arthur Wood
>>
>>
>> "Rafi" <randyh@netvision.net.il> wrote:
>> >
>> >Does any one know why when the following function is called in excel

it
>> returns
>> >#value for values greater than 5986? I would like to see how long it

>will
>> >take to run a lot of nested functions and I thought this would be a way

>> to
>> >test.
>> >
>> >Function x(t As Long) As long
>> > If t = 0 then
>> > x = 1
>> > Else
>> > x = x(t-1)
>> > End if
>> >End function
>> >
>> >Thanks for the help, Rafi

>>

>
>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

 FAQ Latest Articles Java .NET XML Database Enterprise