Sourav Kundu
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 PressOK.
- 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 Names will appear. Select the permute_with_Function subroutine and Click on the RunSample content
- After running the code, we find all the corresponding Permutations 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 Integers 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).RowThis 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 SubThe 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 Names 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 SubThe 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 + 1If 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 SubThe 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.
