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.
For example:
- 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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Sum Values in Range
VBA: How to Calculate Average Value of Range
VBA: How to Count Number of Rows in Range
