Why the heck is this invalid!? This makes literally no sense

[Access9001]

So I know there's a fact() function but bear with me -- there's a reason why I need to use a user-made function, here, but I don't need to get into it.

But I am using the following:

Function facty(expression As Variant) As Variant
Dim I As Double
Dim bNum As Variant

If Not IsError(Evaluate(expression)) Then

bNum = Evaluate(expression)
If InStr(1, bNum, ".") = 0 Then
If bNum = 0 Then
facty = 1
Exit Function
End If
If bNum < 0 Then Exit Function
facty = 1
For I = 1 To bNum
facty = facty * I
Next
Else
facty = "errorthrow"
End If
Else
facty = "errorthrow"
End If

End Function



Now this works great when you use it in the Excel spreadsheets, but if I use another code:

msgbox evaluate("facty(4+5)")


it doesn't work. Why?

 

[Access9001]

The reason why I need to custom-make the fact() function is because Excel's fact() accepts decimals as valid inputs and I want it to only return values when the inputs are whole numbers, say fact(0) or fact(10) but not fact(2.2). Fact(2.2) truncates the decimal whereas I need it to not do so.

 

[boblarson]

What is factout? You say you have facty() as a function but you don't mention a factout() function but yet you say you are having trouble with it and not facty.

 

[Access9001]

Sorry, I meant facty. Slip of the tongue. Factout is a separate factorial function I have but it suffers from the same problem. Facty was chosen arbitrarily out of the two.

Edited the original post.

 

[Access9001]

I found one solution that uses x*exp(1)^gammaln(x) but it doesn't work for x=0, argh.

I need a way to basically apply this to a string replace.

For instance, I might have str="5+(4+5)" and I want to be able to replace the ( with fact( so I get "5+fact(4+5)"

Problem is sometimes the input is in decimal, so if I change 5+(2+5.5) to 5+fact(2+5.5), I get an answer that isn't right. So i need to replace ( with a factorial function that correctly evaluates the expression coming after it.

 

[Access9001]

Okay I think I've got it. I can replace the ( with EXP(1)^GAMMALN(1+

so you can write fact(x) as EXP(1)^GAMMALN(1+x)
Not sure if this'll help anyone else, but alas...

 

[Access9001]

Sigh, still a problem.

Using the gamma function isn't precise. To find 6!, using that gamma expression gives me 719.999999862944 which makes me unable to differentiate between a whole integer answer (6! being 720) and a decimal answer to begin with. AGHHHHH.

Why can't Excel use a better fact() function >:O

Just going to slap a round(factorial output, 5) and call it close enough.