Factorial Subroutine

[wz72n01]

Sub Factorial()
num = Sheets("Inputs").Range("B5").Value
fac = 1
For i = 1 To num
fac = i * fac
Next i
Range("C5").Value = fac
End Sub



When I run this routine nothing happens...I inserted the number 5 in B5 but nothing returns in C5 of Sheet "Inputs".:banghead:

[lucas]

Hi wz72n01,
This is why we use Option Explicit at the top of the module. You would have received errors telling you....compile error...variable not defined:

Option Explicit
Sub Factorial()
Dim num
Dim fac As Integer
Dim i
num = Sheets("Inputs").Range("B5").Value
fac = 1
For i = 1 To num
fac = i * fac
Next i
Range("C5").Value = fac
End Sub
result in C5 is 120 if you enter 5 in B5

[mdmackillop]

MsgBox Application.Fact(10)

[unmarkedhelicopter]

MD, that is just cheating ... :rotlaugh:

[Oorang]

rofl :)
For non-excel vba applications:

Option Explicit
Public Sub TestFactorial()
MsgBox Factorial(10)
End Sub

Public Function Factorial(number As Long) As Long
Const lngLwrBnd_c As Long = 1
Dim lngRtrnVal As Long
Dim lngIndx As Long
lngRtrnVal = lngLwrBnd_c
For lngIndx = lngLwrBnd_c To number
lngRtrnVal = lngRtrnVal * lngIndx
Next
Factorial = lngRtrnVal
End Function

[mikerickson]

Nice job.

[mdmackillop]

When I run this routine nothing happens...I inserted the number 5 in B5 but nothing returns in C5 of Sheet "Inputs".:banghead:
If Inputs is not the active sheet when you run the code, the result will be inserted in C5 of the active sheet.

Sub Factorial()
Dim i As Long, fac As Long
With Sheets("Inputs")
fac = 1
For i = 1 To .Range("B5").Value
fac = i * fac
Next I
.Range("C5").Value = fac
End With
End Sub

[johnske]

long data type is way too small for factorials, need to use double data type, also need some error handling

Option Explicit

Sub Factorial()
With Sheets("Inputs").Range("B5")
If IsNumeric(.Value) And .Value = Int(.Value) Then
If .Value < 171 Then
.Offset(0, 1) = WorksheetFunction.Fact(.Value)
Else
MsgBox "Overflow error - please use a smaller number"
.Offset(0, 1).ClearContents
End If
Else
MsgBox "Please use whole numbers"
End If
End With
End Sub

Sub Factorial2()
Dim i As Double, fac As Double
fac = 1
With Sheets("Inputs").Range("B5")
If IsNumeric(.Value) And .Value = Int(.Value) Then
If .Value < 171 Then
For i = 1 To .Value
fac = i * fac
Next I
.Offset(0, 1) = fac
Else
MsgBox "Overflow error - please use a smaller number"
.Offset(0, 1).ClearContents
End If
Else
MsgBox "Please use whole numbers"
End If
End With
End Sub


(Edited)