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.

**Table of Contents**hide

## 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**.

`=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.

**Read More: ****How to Calculate Factorial of Large Number in Excel**

### 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.

**Read More: ****How to Use FACT Function in Excel (2 Suitable Examples)**

### 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.

## 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.