How to Create a Factorial Function in VBA (With Example)

---

A factorial is the product of all positive integers less than or equal to a given positive integer.

For example, 5 factorial (written as 5!) is calculated as:

• 5! = 5 * 4 * 3 * 2 * 1 = 120

You can use the following syntax to create a factorial function in VBA:

Function FindFactorial(N As Integer) As Double

 Dim i As Integer, result As Long
 
 result = 1

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

 FindFactorial = result

End Function

Once you’ve created this function, you can then type something like =FindFactorial(A2) in a cell in Excel to find the factorial of the integer in cell A2.

The following example shows how to use this syntax in practice.

Example: Create a Factorial Function in VBA

Suppose we have the following list of numbers in Excel and we’d like to calculate the factorial of each number:

We can define the following function in VBA to do so:

Function FindFactorial(N As Integer) As Double

 Dim i As Integer, result As Long
 
 result = 1

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

 FindFactorial = result

End Function

Once we’ve created this function, we can then type the following formula into cell B2 to calculate the factorial of the value in cell A2:

=FindFactorial(A2)

We can then click and drag this formula down to each remaining cell in column B:

Notice that column B now displays the factorial of each integer in column A.

• 1! = 1
• 2! = 2 * 1 = 2
• 3! = 3 * 2 * 1 = 6
• 4! = 4 * 3 * 2 * 1 = 24

And so on.

Note: To calculate a factorial in Excel without using VBA, you can use the FACT function.