I am trying to code the factorial function in VBA in Excel.
I know I can use the FACT() in the worksheet, but somehow I can't use
the same function when I am writing the VBA. It gave error and said
"sub or function not defined". I did macro recording and copy the
code to my sub-function and try to run it, it gave error again. Can
someone help? Thanks.

This syntax works for me:

Application.WorksheetFunction.Fact(5)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

skrev i en meddelelse
m...
I am trying to code the factorial function in VBA in Excel.
I know I can use the FACT() in the worksheet, but somehow I can't use
the same function when I am writing the VBA. It gave error and said
"sub or function not defined". I did macro recording and copy the
code to my sub-function and try to run it, it gave error again. Can
someone help? Thanks.

" wrote:

I am trying to code the factorial function in VBA in Excel.
I know I can use the FACT() in the worksheet, but somehow I can't use
the same function when I am writing the VBA. It gave error and said
"sub or function not defined". I did macro recording and copy the
code to my sub-function and try to run it, it gave error again. Can
someone help? Thanks.


If Leo's answer doesn't solve your problem, try this. Make sure you put in in a code module (as opposed to a sheet module).

Function Factorial(Num as integer)
Dim i as integer, answer as double
answer = 1
For i = 1 to Num
answer = answer * i
Next i
Factorial = answer
End Function