How to Calculate Factorial Using Excel VBA (4 Effective Methods)

This tutorial will demonstrate how to create a User Defined Function to calculate the factorial of a number using Excel VBA. Determining factorial is an essential part of mathematical life. Factorial is actually an operator that determines the multiplication of a number with every positive integer less than that. It is denoted by the exclamation symbol (!). However, the factorial of 0 is 1. The factorial of a number (n) can be calculated using the following formula:

n!=1*2*3*……*(n-1)*n

So the factorial of 4 will be-

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

We can easily use the factorial result to determine the permutations and combinations. By this, you can predict future risks and make proper decisions in business life.

Download Practice Workbook

You can download the practice workbook from here.

4 Effective Methods to Calculate Factorial Using Excel VBA

We’ll use a sample dataset overview as an example in Excel to understand easily. For instance, we have the Number in column B and a Factorial in Column C. If you follow the steps correctly, you should learn how to create a VBA factorial function in Excel on your own. The steps are

1. Using For Loop to Calculate Factorial by Excel VBA

At the very beginning, our aim is to learn how to create a User Defined Function to determine the factorial of a number by using the VBA For Loop statement. We can fulfill our aim by following the below steps.

Steps:

• First, go to the Developer Tab and then select Visual Basic.

• After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Now, type the following code in the VBA Module and save the code using the Ctrl+S options.

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

Let me explain the code a little bit. In this scenario, the uni_input portion and xy_Factorial portions work as two variables. The whole code starts calculating from number 1(uni_input=1) and for that the code gives result one(xy_Factorial=1). After that, the multiplication section comes into the scene(xy_Factorial=xy_Factorial*uni_input) which means the more number you enter the more number the loop function will use to multiply with the first result and the code will continue till your main outcome comes.  In this way, an extra user-defined function will be available for our use. We will find this new function if we type FactorialUsingForLoop in the formula bar.

• Moreover, enter the excel file and type the formula in the C5 cell.

• Afterward, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

• Finally, you will get the proper result.

Thus, we have created a User Defined Function to determine the factorial of a number by using the VBA For Loop statement.

2. Use of Do Until Loop

Now, we want to learn how to create a VBA factorial in excel by using the Do Until Factorial option. We can fulfill our aim by following the below steps.

Steps:

• First, open a VBA Module option by following the same steps as Method 1.
• Second, type the following code in the VBA Module and save the code using the Ctrl+S options.

Function FactorialUsingDoUntil(val As Long) As Long
Dim uni_input As Long, xy_Factorial As Long
Let uni_input = 1
Let xy_Factorial = 1
Do Until uni_input > val
xy_Factorial = xy_Factorial * uni_input
uni_input = uni_input + 1
Loop
FactorialUsingDoUntil = xy_Factorial
End Function

Again, in this code, the uni_input portion and xy_Factorial portions work as two variables.  In the beginning, the whole code starts calculating from number 1(uni_input=1), and for that, the code gives result one(xy_Factorial=1). After that, the multiplication section along with Do Until section comes into the scene(xy_Factorial=xy_Factorial*uni_input) which means the more number you enter the more number the loop function will use to multiply with the first result for a certain condition. Then, the uni_input=uni_input+1 portion says to us that the formula will start counting from 1 then sequentially it will keep the multiplication until the final result comes. This way, an extra user-defined function will be available for our use. We will find this new function if we type FactorialUsingDoUntil in the formula bar.

• Third, go to the excel file and type the following formula in the C5 cell.

• Fourth, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

• Last, you will get the proper result.

Hence, we have created a VBA factorial in excel by using the Do Until Factorial option.

3. Utilizing VBA Do While Loop

Next, we will try to learn how to create a VBA factorial in excel by using the Do While Factorial option. We can fulfill our aim by following the below steps.

Steps:

• To begin with, open a VBA Module option by following the same steps as Method 1.
• In addition, type the following code in the VBA Module and save the code using the Ctrl+S options.

Function FactorialUsingDoWhile(val As Long) As Long
Dim uni_input As Long, xy_Factorial As Long
Let uni_input = 1
Let xy_Factorial = 1
Do While uni_input <= val
xy_Factorial = xy_Factorial * uni_input
uni_input = uni_input + 1
Loop
FactorialUsingDoWhile = xy_Factorial
End Function

So, this way, an extra user-defined function will be available for our use. We will find this new function if we type FactorialUsingDoWhile in the formula bar.

• Afterward, go to the excel file and type the following formula in the C5 cell.

• Later, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

• Finally, you will get the proper result.

If we again talk about the latest code section, then we will find out that, the uni_input portion and xy_Factorial portions work as two variables.  In the beginning, the whole code starts calculating from number 1(uni_input=1), and for that, the code gives result one(xy_Factorial=1). After that, the multiplication section along with Do While section comes into the scene(xy_Factorial=xy_Factorial*uni_input) which means the more number you enter the more number the loop function will use to multiply with the first result for a certain condition. Then, the uni_input=uni_input+1 portion says to us that the formula will start counting from 1 then sequentially it will keep the multiplication until the final result comes. So, we have created a VBA factorial in excel by using the Do While Factorial option.

4. Applying Recursive Function to Calculate Factorial

In this last method, we want to learn how to create a VBA factorial in excel by using the Recursive Factorial option. We can fulfill our aim by following the below steps.

Steps:

• First, open a VBA Module option by following the same steps as Method 1.
• Subsequently, type the following code in the VBA Module and save the code using the Ctrl+S options.

Function FactorialByRecursion(uni_input As Long, Optional temVl As Long = 1) As Long
If uni_input > 1 Then
temVl = temVl * uni_input
uni_input = uni_input - 1
FactorialByRecursion uni_input, temVl
End If
FactorialByRecursion = temVl
End Function

As a result, an extra user-defined function will be available for our use. We will find this new function if we type FactorialByRecursion in the formula bar.

• Furthermore, go to the excel file and type the following formula in the C5 cell.

• Thereafter, you will get the result for this cell and then use the Fill Handle option to apply the formula to all the cells.

• At the end of this method, you will get the proper result.

At the end of this code, let us discuss the code of this method. For instance, in this code, the uni_input portion and temVl portions work as two variables.  In the beginning, the whole code starts calculating from the number you entered (uni_input=input highest number), and for that, the code gives the result a certain(temVl). After that, the multiplication section (temVl=temVl*uni_input) which means the more number you enter the more number the function will use to multiply with the first result for a certain condition. Then, the uni_input=uni_input-1 portion says to us that the formula will start counting from the highest number and then sequentially reduce the number towards zero and it will keep the multiplication until the final result comes. Therefore, we have created a VBA factorial in excel by using the Recursive Factorial option.

Things to Remember

• The file must be saved as a .xlsm Otherwise, the VBA codes won’t work.
• When inserting the code the Sub of the code must be written properly otherwise you won’t able to understand what is the purpose of this code.
• The variables must be used with a conscious. Any mistake in choosing variables can ruin your whole code.

Conclusion

Henceforth, follow the above-described methods. These methods will help you to create a VBA factorial in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.