' The factorial of a number
'
' if NUMBER is negative or >170 it raises an
' "subscript out of range" error

Function Factorial(ByVal number As Long) As Double
    Static result(170) As Double
    
    ' this routine is very fast because it
    ' caches all the possible results
    If result(0) = 0 Then
        ' this is the first time it is executed
        Dim i As Long
        result(0) = 1
        ' Factorial(170) is the highest factorial
        ' value that can be stored in a Double
        For i = 1 To 170
            result(i) = result(i - 1) * i
        Next
    End If
    
    ' just read the result from the cached array
    Factorial = result(number)
        
End Function


' *** UPDATE ***
' Rick Rothstein (MVP-VB) sent us the following update for the Factorial 
' function. It reports whole number results up to an argument value of 27 (a 29-
' digit answer containing 23 significant digits) before switching to power-of-
' ten notation.


' The factorial of a number
'
' if NUMBER is negative or >170 it raises an
' "subscript out of range" error
Function Factorial(ByVal N As Integer) As Variant
    Static Result(170) As Variant
    Dim X As Integer
    ' this routine is very fast because it
    ' caches all the possible results
    If Result(0) = 0 Then
        ' convert to Decimal data type
        Result(0) = CDec(1)
        ' Factorial(170) is the highest factorial
        ' value that can be stored in a Double
        For X = 1 To 170
            ' for X<28, calculate using Decimal data
            ' type; use Double data type afterwards
            If X = 28 Then
                Result(28) = 28 * CDbl(Result(27))
            Else
                Result(X) = X * Result(X - 1)
            End If
        Next
    End If
    ' just read the result from the cached array
    Factorial = Result(N)
End Function