Please help me understand why I am getting a #VALUE! error in Excel using the following UDF in VBA?
I looked up this VBA code which is supposed to calculate factorial for numbers of any size including those larger than 170 which is my specific interest:
Function FactorialUsingForLoop(val As Long) As Long
Dim uni_input As Long, xy_Factorial As Long
Let xy_Factorial = 1
For uni_input = 1 To val
xy_Factorial = xy_Factorial * uni_input
Next
FactorialUsingForLoop = xy_Factorial
End Function
Note a copy of my worksheet. The triangle says: a value in the formula is of a wrong data type. I have eliminated extra spaces, the format is Numbers!
Thanks!
Using the "Multiply" function from https://en.wikibooks.org/wiki/Visual_Basic_for_Applications/Big_Number_Arithmetic_with_Strings#The_VBA_String_Math_Module you can perform the factorial on the string representation of the numbers:
Function FactString(n As Long) As String
Dim i As Long, s
s = "1"
For i = 2 To n
s = Multiply(s, CStr(i)) 'see link above
Next i
FactString = s 'return as text
End Function
FYI FactString(459)
is 1025 digits. Not sure what you could usefully do with the output though?
14485403354484377321946598467762747909890903376354418365375845955625396614008248762435334105235580367713111676386354517817732216317813131627739098804735552619387696361000728350823018080878612362369972102024834022677419331190003646581872203634520283130933793877130922404915890229584207088813666362515804443836956427867939234940645305786761788625294812604394194311811648253226441130486116296174698732284230693452530000613019235681414109884129686923390790591493053092840252338650743843979540442934522441499732761419205481441571729238180772206283267561602205068359377969242866767033627224184350824364701713347358540970587316052075568936320882958156270299294847780095880319469432857013405651004619680268495021224607907646943617462020856149988907272090333825938106785197889156789076323307207860239236383751068288278126756115213803211186543060092701556735081943005710263322762703394271852483534078236589862938081272791040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
EDIT - just noticed that link of methods does include a factorial method:
Function Factorial(ByVal sA As String) As String