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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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: • 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.
`=FactorialUsingForLoop(B5)` • 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.
`=FactorialUsingDoUntil(B5)` • 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.
`=FactorialUsingDoWhile(B5)` • 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.
`=FactorialByRecursion(B5)` • 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.

## Related Article Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  