is it posible to have a formula for factorial via vba code?

[thread]

Hi all,

i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?

[joeu2004]

On Apr 6, 8:44 am, thread <yaniv...@gmail.com> wrote:
> i'm trying to build a complex formula using factorial but i havent
> foud an operator that calculate a simple factorial,any idea?

Perhaps the easy way is:

WorksheetFunction.Fact(n)

[Rick Rothstein (MVP - VB)]

Below my signature is a standard posting I have used over in the compiled VB
newsgroups, but the function will work just as well in Excel's VBA world.
Depending on how "complex" a formula you are trying to build, pay attention
to the cautions in my write up. You might be able to avoid them by
restricting the maximum size permitted before floating point results are
returned (change the 28 in the first statement to something lower).

Rick

You could cast (you can't Dim) a Variant variable as a Decimal type 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 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(N As Long) As Variant
Dim X As Long
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).

"thread" <yani...@gmail.com> wrote in message
news:50af4465-0bc7-43a8...@k20g2000prn.googlegroups.com...

[thread]

thanks it was very helpfull

[subhrodi...@gmail.com]

In case you are not into Big numbers (numbers that excel does not normally handle)
Use worksheet.function in VBA. Uses fact() in excel in vba.