Re: Factorial

From: Rick Rothstein (rickNOSPAMnews_at_NOSPAMcomcast.net)
Date: 05/21/04 

Date: Thu, 20 May 2004 22:21:55 -0400

> Oops, to avoid overflow, do this instead:
>
> Public Function Factorial(x As Integer) As Double
> Factorial = 1
> For I = 0 To x - 1
> Factorial = Factorial * (x - I)
> Next I
> End Function

Perhaps this combination of two posts I given in the past may be of some
help or interest...

Rick - MVP

You could cast (you can't Dim) a Variant variable as a Decimal type
(96-bit number) and get some 28 or 29 digits of accuracy depending if
there is a decimal in the answer or not. Simply Dim a variable as
Variant and CDec a number (any number will do) into it to make it the
Decimal type. Thereafter, that variable will track 28/29 digits of
accuracy. For example the following function will calculate factorials
up to 29 digits of display before reverting to exponential display.

   Function BigFactorial(ByVal N As Integer) As Variant
      If N < 28 Then
          BigFactorial = CDec(1)
      Else
          BigFactorial = CDbl(1)
      End If
      For x = 1 To N
          BigFactorial = x * BigFactorial
      Next
   End Function

However, you have to watch out for overflows with Decimal data types --
once over 28/29 characters, they will produce an overflow error. So, if
you tried to use the above function like this

   Debug.Print 10*BigFactorial(27)

you would get an overflow error but

   Debug.Print 10*BigFactorial(28)

would work fine (the difference being in the first case BigFactorial has
a Decimal subtype and in the second case the subtype is a Double).

More generally, if a Variant variable is assigned a value that was cast
to Decimal, any calculation involving that variable will be "performed"
as a Decimal; and then the result cast back to the variable receiving
it. If the result is assigned back to the variable that was originally
cast to Decimal, that variable continues to contain a Decimal type
value. For example,

      X = CDec(135.6)
      X = X - 135
      X = X / 7
      Print X ==> 0.0857142857142857142857142857

You have to be careful with this though . . . all VB functions return
non-Decimal data.and assigning *that* back to the Variant that was cast
as Decimal "collapses" it back to a less robust data type. For example,
continuing the code above

      X = Sqr(X)
      Print X ==> 0.29277002188456

Relevant Pages

  • Re: Error from Hell: Overflow
    ... > Dim b as variant ... > Dim x1 as variant ... > I believe the overflow is caused by somewhere in these for loops. ...
    (microsoft.public.vb.general.discussion)
  • Re: Error from Hell: Overflow
    ... > My VB6 app produces an Overflow error and bombs. ... > Dim b as variant ... > Dim x1 as variant ... I run MS code advisor, ...
    (microsoft.public.vb.general.discussion)
  • Re: overflow problem and variable declaration
    ... The formatting was obliging the variant to be integer and ... therefore I was having the overflow problem. ... Kind regards ... removing the formatting on your cells (the calculation may not like ...
    (microsoft.public.excel.programming)
  • Re: Cannot compile with _FILE_OFFSET_BITS = 64
    ... No automatic conversion to 64 bit type is done by compiler. ... the second one might overflow, ... to use a cast to off_t rather than the "LL" suffix. ... Or you could apply the cast to all four constants, ...
    (comp.lang.c)
  • Re: Error from Hell: Overflow
    ... All those who reply for the "Error from Hell", ... First of all let me remind you that the Error 6 "Overflow" was coming on ... > Dim b as variant ... > Dim x1 as variant ...
    (microsoft.public.vb.general.discussion)