Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
is it posible to have a formula for factorial via vba code?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
is it posible to have a formula for factorial via vba code?
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
is it posible to have a formula for factorial via vba code?
On Apr 6, 8:44*am, thread 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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
is it posible to have a formula for factorial via vba code?
thanks it was very helpfull
On 6 אפריל, 19:11, joeu2004 wrote: On Apr 6, 8:44*am, thread 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
is it posible to have a formula for factorial via vba code?
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. On Monday, April 7, 2008 1:38:58 PM UTC+5:30, thread wrote: thanks it was very helpfull On 6 אפריל, 19:11, joeu2004 wrote: On Apr 6, 8:44*am, thread 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Maximize posible? | Excel Discussion (Misc queries) | |||
is it posible to do payslips for a small business in excel | Excel Worksheet Functions | |||
Factorial (like =FACT) function? | Excel Worksheet Functions | |||
Range of Factorial Function | Excel Discussion (Misc queries) | |||
Creating "factorial" result from three lists... | Excel Discussion (Misc queries) |