Factorial function...whaaaaaa?

G

Geoff

I have the following equation in my code:

TotalCalc = Fact(n + 4) / (Fact(4) * Fact(n))

When I compile, it highlights the first Fact and tells me "Sub or Function
not defined".

TIA
 
J

Jacob Skaria

FACT() is a worksheetfunction. So you can try something like the below

TotalCalc = WorksheetFunction.Fact(n + 4) / (WorksheetFunction.Fact(4) * _
WorksheetFunction.Fact(n))
 
R

Rick Rothstein

Or, to lessen the amount of typing...

With WorksheetFunction
TotalCalc = .Fact(n + 4) / (.Fact(4) * .Fact(n))
End With

To the OP... be sure to note the "dot" in front of each of the Fact function
names.
 
J

Joe User

Geoff said:
I have the following equation in my code:
TotalCalc = Fact(n + 4) / (Fact(4) * Fact(n))

For n>166, Fact(n+4) exceeds the computation limits of the Double data type.

And for n>14, Fact(n+4) exceeds 2^53-1, the largest integer that can be
represented exactly with the Double data type. So the result of your
expression is infinitesimally different from the correct result, which can
lead to anomalies in some expressions.

It would behoove you to reduce the formula algebraically, namely:

TotalCalc = (n+4)*(n+3)*(n+2)*(n+1) / 24

Not only is the result more accurate for a wider range of n, but also it is
more efficient.

Even if 4 in Fact(n+4) and Fact(4) is actually variable, I think an
algorithm that reduces the formula algebraically would be more reliable,
albeit perhaps less efficient.


----- original message -----
 
D

Dana DeLouis

Another option might be:

TotalCalc = WorksheetFunction.Combin(n+4,4)

= = =
HTH :>)
Dana DeLouis
 
C

Chip Pearson

As others have indicated, you can use Excel's built in FACT worksheet
function. However, you can roll your own with a recursive function in
VBA. Recursive functions are those function that call themselves. In
the code below, the Fact function calls itself decrementing the input
value until it is 1.

See http://www.cpearson.com/Excel/RecursiveProgramming.aspx for an
introduction to recursion.

Function Fact(N As Long) As Long
If N = 1 Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function

Usage:

Sub AA()
Dim L As Long
L = Fact(6)
Debug.Print CStr(L) ' displays 120
End Sub

Then your original code will work with no modification.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
R

Rick Rothstein

Okay, as long as you posted a recursive Factorial function in VB, I figured
others might find this non-recursive function of some interest. The
following function will calculate factorials up to 29 accurate digits of
display before reverting to exponential display (but note the caution after
the function code)...

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 (that
is what the CDec function produces) when used in calculations... once an
expression using a Decimal data type calculates to more than 28/29 digits
(depending of if it contains a decimal point or not), it will produce an
overflow error. So, if you tried to use the above function like this

MsgBox 10 * BigFactorial(27)

you would get an overflow error but

MsgBox 10 * BigFactorial(28)

would work fine (the difference being in the first case BigFactorial returns
a Variant with a Decimal subtype whereas in the second case the Variant's
subtype is a Double).

--
Rick (MVP - Excel)



Chip Pearson said:
As others have indicated, you can use Excel's built in FACT worksheet
function. However, you can roll your own with a recursive function in
VBA. Recursive functions are those function that call themselves. In
the code below, the Fact function calls itself decrementing the input
value until it is 1.

See http://www.cpearson.com/Excel/RecursiveProgramming.aspx for an
introduction to recursion.

Function Fact(N As Long) As Long
If N = 1 Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function

Usage:

Sub AA()
Dim L As Long
L = Fact(6)
Debug.Print CStr(L) ' displays 120
End Sub

Then your original code will work with no modification.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




I have the following equation in my code:

TotalCalc = Fact(n + 4) / (Fact(4) * Fact(n))

When I compile, it highlights the first Fact and tells me "Sub or Function
not defined".

TIA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problems with office 2016 installation. 0
Need Help with a VBA subroutine 0
vba coding 3
vba 1
SaveAs using VB 2
PlaySound error (Chip Pearson site) 2
Excel Macro Programing Problem 2
Paragraph numbering in Word 1

Top