We can perform** permutation and combination in Excel VBA**. Permutation and Combination are often used in mathematical analysis, especially in the field of Probability and Statistics, Optimisation, and Data Analysis. It helps to optimise the best scenario from a set of possibilities. So, for mathematical analysis Permutation and Combination are often required. This article is about how you can perform these calculations using Excel VBA.

The above video shows the overview of performing a combination of 4 ingredients.

## Download Practice Workbook

You can download and practice this workbook.

## How to Launch VBA Editor in Excel

To launch VBA Editor in Excel, follow these steps:

- Open Microsoft Excel.
- Then, click on the
**Developer**tab in the ribbon and**Click**on the**Visual Basic**icon in the**Code**section.

- This will open the
**VBA Editor**Window in which the**Project Explorer**will be on the left-hand side and the**Code Editor**in the main area. Now, Select**Module**from the**Insert**tab.

- A new
**Module**will be created where you can**Write**or**Edit**your**VBA code**in the**Code Editor**window.

Note: In case the **Developer **tab is not visible in the ribbon, you may need to enable it by going to **File > Options > Customize Ribbon **and then checking the box next to **Developer **in the right-hand pane.

## What Are Permutation and Combination?

Permutation and Combination are two concepts of mathematics that are used to count the number of ways in which a set of objects can be arranged or selected.

**Permutation**

A permutation is a way of counting the number of ways objects can be arranged in a specific order. It is basically the ordered arrangement of objects.

The Number of permutations for **r** number of selected objects from **n **number of objects can be given as,

**Combination**

The combination is the way of counting the number of ways objects can be selected without any order. It is basically the unordered selection of objects.

The number of combinations for **r** number of selected elements from **n **number of elements can be given as,

## Permutation and Combination in Excel VBA: 5 Suitable Examples

**Permutation** and **Combination** can be formulated in different ways. We are going to show you how we can perform this in **Excel VBA**. We will use **InputBox** with **For Next** loop. Also, we will use other methods to calculate Permutation and Combination on Excel Spreadsheet.

### 1. Permutation and Combination in Excel VBA with InputBox

If you want to calculate Permutation and Combination for a single case, you should use this method. In this method, we will take the value of **n** and **r** from the **InputBox** and get the value of **nPr **and **nCr** in the** MsgBox**.

**Permutation with InputBox**

You can follow these procedures to count the permutation number of **r** objects from **n** number of objects.

**Copy**the following**Code**in a new**Module**and**Click**on the**Run**button.

**VBA Code for Permutation**

```
Sub Permutation_with_InputBox()
n = InputBox("Enter Value of n:")
r = InputBox("Enter Value of r:")
result1 = 1
For i = 1 To n
result1 = result1 * i
Next i
result2 = 1
For i = 1 To n - r
result2 = result2 * i
Next i
permutation = result1 / result2
MsgBox (permutation)
End Sub
```

- After running the Code, an
**InputBox**will open where you have to Insert the value of**n**. We took the value of**n**as 6. Then Press**OK**.

- Another
**InputBox**will open where you have to Insert the value of**r**(we took**r**as 4) and Press**OK**.

- Doing so, the number of Permutations will be shown in a
**MsgBox**.

**🔎**** VBA Code Breakdown**

```
n = InputBox("Enter Value of n:")
r = InputBox("Enter Value of r:")
```

- These two lines take the value of
**n**and**r**from**InputBox**.

```
result1 = 1
For i = 1 To n
result1 = result1 * i
Next i
```

- This section takes the value of the
**result**as**1**and initiates a**For Next**loop which in the end finds the value of**n!**.

```
result2 = 1
For i = 1 To n - r
result2 = result2 * i
Next i
```

- Similarly, This portion is for finding the value of
**(n-r)!**.

```
permutation = result1 / result2
MsgBox (permutation)
```

Then, the code divides **n!** by **(n-r)!** And assigns to **permutation.**

And, the result is shown in the **MsgBox**.

**Combination with InputBox**

Follow the following procedure to count the number of combinations of **r** objects from** n **number of elements.

**Copy**the following**Code**in a new**Module**and**Click**on the**Run**button.

**🔎 VBA Code Breakdown**

```
Sub Combination_with_InputBox()
n = InputBox("Enter Value of n:")
r = InputBox("Enter Value of r:")
result1 = 1
For i = 1 To n
result1 = result1 * i
Next i
result2 = 1
For i = 1 To n - r
result2 = result2 * i
Next i
result3 = 1
For i = 1 To r
result3 = result3 * i
Next i
Combination = result1 / (result2 * result3)
MsgBox (Combination)
End Sub
```

- After running the Code, an
**InputBox**will open where you have to Insert the value of**n**. Then Press**OK**.

- Then, Insert the value of
**r**in the next**InputBox**and Press**OK**.

- By doing so, we can see the number of combinations shown in the
**MsgBox**.

**🔎**** VBA Code Breakdown**

```
n = InputBox("Enter Value of n:")
r = InputBox("Enter Value of r:")
```

- These two lines take the value of
**n**and**r**from**InputBox**.

```
result1 = 1
For i = 1 To n
result1 = result1 * i
Next i
```

- This section takes the value of
**result1**as**1**and initiates a**For Next**loop to calculate**n!**.

```
result2 = 1
For i = 1 To n - r
result2 = result2 * i
Next i
```

- Similarly, this portion finds the value of
**(n-r)!**.

```
result3 = 1
For i = 1 To r
result3 = result3 * i
Next i
```

- Similarly, this portion finds the value of
**r!**.

```
Combination = result1 / (result2 * result3)
MsgBox (Combination)
```

- The first line finds the number of combinations and assigns to Combination.
- The second line shows the result in a
**MsgBox**.

### 2. Use of Dedicated Function to Perform Permutation and Combination

In this procedure, we will find the permutation and combination from the values inscribed in the worksheet. This method is useful when you have multiple cases to find permutation and Combination. For this, we have to use dedicated functions. Also, we will need a **subroutine** to insert the value of **n** and **r** from the Excel worksheet and to fill the result back in the worksheet.

**Dedicated Function to Calculate Permutation**

We have taken this dataset that has values of **n** and **r** in **Column B** and **Column C**. We want to find the **Permutation** i.e. **nPr **in **Column D**.

**Copy**the following**Code**in a new**Module**and**Run**the**Function**.

**VBA Code for Permutation**

```
Function Permute(n As Integer, r As Integer) As Double
Dim result As Double
result = 1
For i = n - r + 1 To n
result = result * i
Next i
Permute = result
End Function
Sub permute_with_Function()
Dim n As Integer, r As Integer
Dim result As Double, lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 5 To lastRow
n = Cells(i, "B").Value
r = Cells(i, "C").Value
result = Permute(n, r)
Cells(i, "D").Value = result
Next i
End Sub
```

- Doing so, all the
**Macro Name**s will appear. Select the**permute_with_Function**subroutine and Click on the**Run**Sample content

- After running the code, we find all the corresponding
**Permutation**s in**Column D**as shown in the image below.

**🔎**** VBA Code Breakdown**

```
Function Permute(n As Integer, r As Integer) As Double
Dim result As Double
```

- First, the Code calls
**Permute**as a**Function**that takes**n**and**r**as two**Integer**s and returns a**Double**value. - Then it declares the result as
**Double**.

```
result = 1
For i = n - r + 1 To n
result = result * i
Next i
```

- This portion takes the value of
**result**as**1**. Then, the**For Next**loop is used for finding**Permutation**.

```
Permute = result
End Function
```

- And it assigns
**result**as**Permutation**and ends**Function**.

```
Sub permute_with_Function()
Dim n As Integer, r As Integer
Dim result As Double, lastRow As Long
```

- First, all the variables are declared.

` lastRow = Cells(Rows.Count, "B").End(xlUp).Row`

This line finds the last used row of Column B in the active worksheet and assigns it as lastRow.

```
For i = 5 To lastRow
n = Cells(i, "B").Value
r = Cells(i, "C").Value
```

- This portion starts a
**For**loop and assigns values from Column B as**n**and Column C as**r**.

```
result = Permute(n, r)
Cells(i, "D").Value = result
```

- The first line assigns values of
**n**and**r**in the**Permute**Function. - Then the second line assigns
**result**in Column D.

```
Next i
End Sub
```

The programme moves to the next iteration.

**Dedicated Function to Calculate Combination**

We have taken the same dataset as used before. The values of **n** and **r** are in Column **B **and Column C. We want the **Combination** number in Column **D**.

**Copy**the following**Code**in a new**Module**and**Run**the**Function**.

**VBA Code for Combination**

```
Function Combine(n As Integer, r As Integer) As Double
Dim result As Double
result = 1
For i = 1 To r
result = result * (n - r + i) / i
Next i
Combine = result
End Function
Sub combine_With_Function()
Dim n As Integer, r As Integer
Dim resul As Double, lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 5 To lastRow
n = Cells(i, "B").Value
r = Cells(i, "C").Value
result = Combine(n, r)
Cells(i, "D").Value = result
Next i
End Sub
```

- Doing so, all the
**Macro Name**s will appear. Select**combine_With_Function**subroutine and click on**Run**.

- After running the code, we find all the corresponding combinations in column
**D**as shown in the image below.

**🔎**** VBA Code Breakdown**

```
Function Combine(n As Integer, r As Integer) As Double
Dim result As Double
```

- First, the Code calls
**Combine**as a**Function**that takes**n**and**r**as two**integers**and returns a**Double**value. - Then it declares the result as
**Double**.

```
result = 1
For i = 1 To r
result = result * (n - r + i) / i
Next i
```

- This portion takes the value of
**the result**as**1**. Then, the**For Next**loop is used for finding a**Combination**.

```
Combine = result
End Function
```

- And it assigns
**result**as**Combination**and ends**Function**.

```
Sub combine_With_Function()
Dim n As Integer, r As Integer
Dim result As Double, lastRow As Long
```

- First, all the variables are declared.

```
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
```

- This line finds the last used row of Column B in the active worksheet and assigns it as lastRow.
- This portion starts a
**For**loop and assigns values from Column**B**as**n**and Column**C**as**r**.

```
result = Combine(n, r)
Cells(i, "D").Value = result
```

- The first line assigns values of
**n**and**r**in the**Combine**Function. - Then the second line assigns
**result**in Column**D**.

```
Next i
End Sub
```

The program moves to the next iteration.

### 3. Permutation and Combination in Excel VBA Using Formula

We can use direct** PERMUT** and **COMBIN** formulas to find the permutation and combination in Excel VBA. We are using the same dataset as shown in the previous method.

**Permutation with PERMUT Formula in Excel VBA**

**Copy**the following**Code**in a new**Module**and**Click**on**Run**.

**VBA Code for Permutation**

```
Sub Permutaion_with_Formula()
Range("D5").Select
ActiveCell.FormulaR1C1 = "=PERMUT(RC[-2],RC[-1])"
Selection.AutoFill Destination:=Range("D5:D9"), Type:=xlFillDefault
End Sub
```

- After running the code, you can see that Column
**D**is filled with the**Permutation**.

**🔎**** VBA Code Breakdown**

- The first line Selects the
**D5**cell.

` ActiveCell.FormulaR1C1 = "=PERMUT(RC[-2],RC[-1])"`

- This line assigns the
**PERMUT**formula in the active cell where**RC[-2]**is Cell**B5**and**RC[-1]**is cell**C5**.

** **` Selection.AutoFill Destination:=Range("D5:D9"), Type:=xlFillDefault`

- This line copies the Formula to range
**D5:D9**with AutoFill Destination.

**Combination with COMBIN Formula in Excel VBA**

**Copy**the following**Code**in a new**Module**and**Click**on**Run**.

**VBA Code for Combination**

```
Sub Combinataion_with_Formula()
Range("D5").Select
ActiveCell.FormulaR1C1 = "=combin(RC[-2],RC[-1])"
Selection.AutoFill Destination:=Range("D5:D9"), Type:=xlFillDefault
End Sub
```

- After running the code, you can see the combination number fills Column
**D**as shown in the image below.

**🔎**** VBA Code Breakdown**

`Range("D5").Select`

- The first line Selects the
**D5**cell.

` ActiveCell.FormulaR1C1 = "=combin(RC[-2],RC[-1])"`

- This line assigns the
**combin**formula in the active cell where**RC[-2]**is Cell**B5**and**RC[-1]**is Cell**C5**.

`Selection.AutoFill Destination:=Range("D5:D9"), Type:=xlFillDefault`

- This line copies the Formula to Range
**D5:D9**with AutoFill Destination.

### 4. Finding Permutation of Letters for Given String

Now let’s see how you can find all the permutations for a given string. This method shows all the Permutations in Excel Worksheet. So, if you want to see all the permutations you can use this method.

We will find permutations for a 5-digit string. So, we have created this empty table to show the result of these 120 Permutations in the range **B4:B123**.

**Copy**the following**Code**in a new**Module**and**Click**on**Run**.

```
Sub Subroutine_String()
Dim str As String
Dim xRow As Long
xRow = 4
str = Application.InputBox("Enter Your String:")
Range("B4").Activate
ActiveSheet.Range(Selection, Selection.End(xlDown)).ClearContents
Call Subroutine_Permutation("", str, xRow)
End Sub
Sub Subroutine_Permutation(Str1 As String, Str2 As String, ByRef xRow As Long)
Dim i As Integer, xLen As Integer
xLen = Len(Str2)
If xLen = 1 Then
Range("B" & xRow) = Str1 & Str2
xRow = xRow + 1
Else
For i = 1 To xLen
Call Subroutine_Permutation(Str1 + Mid(Str2, i, 1), _
Left(Str2, i - 1) + Right(Str2, xLen - i), xRow)
Next i
End If
End Sub
```

- Doing so, a window will open where you have to Input your string. We inserted this
**abcde**string in the InputBox. Then Click on the**OK**button.

- And, you can see all the permutations are filled in range
**B5:B123**as shown in the below image.

**🔎**** VBA Code Breakdown**

```
Sub Subroutine_String()
Dim str As String
Dim xRow As Long
```

- The code declares a Subroutine_String and also declares str and xRow with proper dimensions.

```
xRow = 4
str = Application.InputBox("Enter Your String:")
```

- The code sets the value of
**xRow**as**4**. - And it takes the
**str**value from an InputBox.

```
Range("B4").Activate
ActiveSheet.Range(Selection, Selection.End(xlDown)).ClearContents
```

- Cell
**B4**is activated and then it clears the contents of all cells from the selected cell down to the last cell in the column.

```
Call Subroutine_Permutation("", str, xRow)
End Sub
```

- Finally, the code calls subroutine
**Subroutine_Permutation**.

```
Sub Subroutine_Permutation(Str1 As String, Str2 As String, ByRef xRow As Long)
Dim i As Integer, xLen As Integer
```

- The code defines the second Subroutine
**Subroutine_Permutation**that has three arguments**Str1**,**Str2**, and**xRow**. Then the code declares two variables**i**and**xLen**.

`xLen = Len(Str2)`

- This line finds the length of
**Str2**and assigns it to**xLen**.

```
If xLen = 1 Then
Range("B" & xRow) = Str1 & Str2
xRow = xRow + 1
```

If the **xLen **is equal to **1**, the subroutine writes the concatenated string of **Str1** and** Str2** in Column B. Then it increases the value of **xRow **by **1**.

```
Else
For i = 1 To xLen
Call Subroutine_Permutation(Str1 + Mid(Str2, i, 1), _
Left(Str2, i - 1) + Right(Str2, xLen - i), xRow)
```

If the **xLen **is greater than **1**, the code enters a loop that iterates from **1** to **xLen**. This loop finds all possible Permutations of the characters in the original string using the **Mid**,** Left**, and **Right** functions.

```
Next i
End If
End Sub
```

The Subroutine terminates when all possible Permutations have been generated.

### 5. Finding a Combination of Given Number of Ingredients

Let’s take a dataset of different ingredients for making a burger. We have taken these 7 ingredients. Here we want to take only 4 ingredients and see how many different combinations occur.

We can take 4 ingredients from 7 in 35 different ways. That’s why we have taken an empty table in the range **D5:G39**.

**Copy**the following**Code**in a new**Module**and**Click**on**Run**.

**VBA Code to Find Combination of Different Ingredients**

```
Sub Combination_Choose_Ingredients()
Dim Ingredients As Variant
Dim i As Long, j As Long, k As Long, m As Long
Dim outputRow As Long
outputRow = 5
Ingredients = Range("B5:B11").Value
For i = LBound(Ingredients) To UBound(Ingredients)
For j = i + 1 To UBound(Ingredients)
For k = j + 1 To UBound(Ingredients)
For m = k + 1 To UBound(Ingredients)
Range("D" & outputRow).Value = Ingredients(i, 1)
Range("E" & outputRow).Value = Ingredients(j, 1)
Range("F" & outputRow).Value = Ingredients(k, 1)
Range("G" & outputRow).Value = Ingredients(m, 1)
outputRow = outputRow + 1
Next
Next k
Next j
Next i
End Sub
```

**🔎**** VBA Code Breakdown**

```
Dim Ingredients As Variant
Dim i As Long, j As Long, k As Long, m As Long
Dim outputRow As Long
```

- First, all the variables are properly declared.

```
outputRow = 5
Ingredients = Range("B5:B11").Value
```

- The
**outputRow**is set as**5**. Then the**7**ingredients are assigned to**Ingredients**.

```
For i = LBound(Ingredients) To UBound(Ingredients)
For j = i + 1 To UBound(Ingredients)
For k = j + 1 To UBound(Ingredients)
For m = k + 1 To UBound(Ingredients)
```

- Starts a
**For**loop that iterates from Lower Bound to Upper Bound. - Then another 3
**For**loops are taken where the Lower Bounds are 2nd, 3rd, and 4th ingredients.

```
Range("D" & outputRow).Value = Ingredients(i, 1)
Range("E" & outputRow).Value = Ingredients(j, 1)
Range("F" & outputRow).Value = Ingredients(k, 1)
Range("G" & outputRow).Value = Ingredients(m, 1)
outputRow = outputRow + 1
```

- This section is for assigning 4 consecutive ingredients in 4 consecutive columns. This is done for the purpose of finding combinations.

```
Next m
Next k
Next j
Next i
```

- This section is for concluding loops.

After running the code, you can see all the combinations appear in the proper column as shown in the below image.

## How to Apply Permutations and Combinations in Excel with Functions

Aside from all the VBA stuff, we can find permutation with the **PERMUT **function and combination with the **COMBIN** function easily in Excel. We have included each in their own sections below.

### i. Use of PERMUT Function

The **PERMUT** function finds the permutation without any repetition.

**Syntax:**

```
=PERMUT(number, number_chosen)
number = Total number of items to choose from
Number_chosen = Number of items in each Permutation
```

We want to find the Permutation of **r** number of objects taken from a set of **n** number of objects.

The below image shows how you can perform this using this function.

Also, you can use Integer values in the function for single use.

`=PERMUT(7, 3)`

### ii. Use of COMBIN Function

The **COMBIN** function finds the combination without any repetition.

**Syntax:**

```
= COMBIN(number, number_chosen)
number = The total number of items to choose from
number_chosen = Number of items in each Permutation
```

We want to find the combination of **r** number of objects taken from a set of **n** number of objects.

The below image shows how you can perform this using this function.

Also, you can use Integer values in the Function for single use.

Like,

`=COMBIN(7, 3)`

## How to Create Permutation Table in Excel

Let’s create a Permutation Table of all possible Passwords of individual locks. These locks have different numbers of “Rings” and each ring has a different number of digits. We will find the number of different “Passwords” that each lock can generate. In this case, we need to find a permutation with repetition. For this, we can use the **PERMUTATIONA** function.

Write the following Formula in** D5**.

`=PERMUTATIONA(B5,C5)`

This formula is finding **B5^C5**.

We get the no. of possible passwords for the first lock.

Now copy the formula to other cells.

Doing so, we have got the Permutation table shown in the image below.

## Frequently Asked Questions

**1. What is the difference between permutation and combination?**

A permutation is an act of arranging objects or numbers in order. On the other hand, Combination is the way of selecting objects or numbers from a group of objects or collections, in such a way that the order of the objects does not matter.

**2. How do you know how many permutations and combinations are possible in Excel?**

You can find the permutation number by using the **PERMUT** function. And for combination numbers, you can use the **COMBIN** function.

**3. What is the real-life application of permutation and combination?**

Some real-life applications of permutation and combination are Combination locks, Passwords, Phone numbers, Car plate numbers, Playing the piano, Word formation, selecting teams, etc.

## Things to Remember

- The methods shown in this article will work only if all the elements are different from each other. If you take a string that has multiple same elements these methods will give the wrong answer.
- When working with Permutation and Combination it is important to have a better understanding of basic theory. This could help you understand the problem more easily and to solve the problem more efficiently.
- Make sure the data range is properly assigned. Otherwise, it will give you the wrong answer.

## Conclusion

So, we have shown you several methods of how you can calculate permutation and combination in Excel VBA. We tried to show you practical examples. We hope you find the content of this article useful. If there are any queries or suggestions, you can mention them in the comment section. For more articles like this, you can visit our website **ExcelDemy.com**.