Calculate the factorial of a number in Excel VBA

What is a factorial?

The factorial of a non-negative integer nnn is the product of all the integers from 1 to nnn, i.e.:

n!=n×(n−1)×(n−2)×⋯×1n! =n×(n−1)×(n−2)×⋯×1

For example: 5!=5×4×3×2×1=5×4×3×2×1=120

VBA Code to Calculate the Factorial

Sub CalculateFactorial()
    ' Declare variables
    Dim n As Integer
    Dim result As Long
    Dim i As Integer   
    ' Prompt the user to enter a number
    n = InputBox("Enter an integer to calculate its factorial:")   
    ' Check if the input is valid
    If n < 0 Then
        MsgBox "Factorial is not defined for negative numbers.", vbExclamation
        Exit Sub
    End If   
    ' Initialize the result
    result = 1   
    ' Calculate the factorial
    For i = 1 To n
        result = result * i
    Next i  
    ' Display the result
    MsgBox "The factorial of " & n & " is: " & result
End Sub

Explanation of the Code:

1. Variable Declarations:

Dim n As Integer
Dim result As Long
Dim i As Integer

1. • n : Variable to store the number for which we want to calculate the factorial.
   • result : Variable to store the result of the factorial calculation, declared as Long to handle larger values.
   • i : Control variable for the loop.
2. Prompting the User for Input:

n = InputBox("Enter an integer to calculate its factorial:")

• • The InputBox function asks the user to enter a number.

3. Input Validation:

• If the user enters a negative number, an error message is shown, and the code execution is stopped using Exit Sub.

4. Initializing the Result:

result = 1

• • The variable result is initialized to 1, since multiplication starts with this value (the identity element for multiplication in factorial calculation).

5. Factorial Calculation Using a Loop:

For i = 1 To n
    result = result * i
Next i

• • The For loop iterates from 1 to nnn, multiplying result by each value of i at each iteration.

6. Displaying the Result:

MsgBox "The factorial of " & n & " is: " & result

1. • Once the calculation is complete, a message box displays the result of the factorial of n.

How to Use the Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module:
   • Click on Insert in the menu bar and select Module.
3. Copy and paste the code above into this module.
4. To run the code, press F5 or go to the Run menu and select Run Sub/UserForm.

A message will appear prompting you to enter a number, and once you do, another message will show you the factorial of that number.

Example of Execution:

If you enter 5 in the input dialog, the program will calculate 5! and display the message:

The factorial of 5 is: 120