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).
