How to Perform Permutation and Combination in Excel VBA

In mathematical analysis, especially in the fields of Probability and Statistics, Optimization, and Data Analysis, Permutation and Combination are used to optimize the best scenario from a set of possibilities. This article is about how to perform these calculations using Excel VBA.

The above video shows the overview of performing a combination of 4 ingredients using Permutation and Combination.

How to Launch VBA Editor in Excel

To launch VBA Editor in Excel:

Open Microsoft Excel.
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.

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: If the Developer tab is not visible on 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 written as:

Combination

The combination is a 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 written as:

Permutation and Combination in Excel VBA: 5 Suitable Examples

Permutation and Combination can be formulated in different ways. We will demonstrate how to perform these operations in Excel VBA by using an InputBox with a For Next loop, and by other methods.

Example 1 – Permutation and Combination with an InputBox

This method is appropriate to calculate Permutation and Combination for a single case. We’ll take the value of n and r from an InputBox and return the value of nPr and nCr in a MsgBox.

Permutation with an InputBox

To count the permutation number of r objects from n number of objects:

Copy the following code and paste it in a new Module.
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 to insert the value of n.

Enter the value of 6.
Click OK.

Another InputBox will open to insert the value of r.

Enter 4 and click OK.

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 the InputBox.

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

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 finds the value of (n-r)!.

permutation = result1 / result2
MsgBox (permutation)

Then, the code divides n! by (n-r)! and assigns the result to permutation.
The result is then shown in the MsgBox.

Combination with an InputBox

To count the number of combinations of r objects from n number of elements:

Copy the following code and paste it in a new Module.
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 to insert the value of n.

Enter 6 then click OK.

Insert the value of r (4) in the next InputBox and click OK.

The number of combinations is 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

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 this value to Combination.
The second line shows the result in a MsgBox.

Example 2 – Using a Dedicated Function to Perform Permutation and Combination

In this procedure, we will find the permutation and combination from values in the worksheet. This method is useful when you have multiple cases in which to find the Permutation and Combination. We’ll use dedicated functions and a subroutine to insert the value of n and r from the Excel worksheet and return the result back into the worksheet.

Dedicated Function to Calculate Permutation

Consider the dataset below that has values of n and r in Column B and Column C. We’ll find the permutation i.e. nPr in Column D.

Copy the following code and paste it into a new Module.
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

As result of running the code, all the Macro Names will appear.

Select the permute_with_Function subroutine and click on the Run button.

All the corresponding Permutations are listed 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 type Double.

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

Takes the value of result as 1. Then, the For Next loop is used to find Permutation.

Permute = result
End Function

The result is assigned to Permutation and the function is ended.

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

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

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 the values of n and r into the Permute function.
The second line outputs result in Column D.

 Next i
End Sub

The sub moves to the next iteration, Afterwards, the code ends

Dedicated Function to Calculate Combination

Using the same dataset as before, with the values of n and r in Column B and Column C, let’s find the Combination number in Column D.

Copy the following code and paste it into a new Module.
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

By runnng the code, all the Macro Names will appear.

Select the combine_With_Function subroutine and click on Run.

All the corresponding combinations in column D are returned.

VBA Code Breakdown

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

First, we name 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

Takes the value of result as 1. Then, the For Next loop is used to find a Combination.

Combine = result
End Function

The result is assigned as Combine and the function ends.

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

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

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

Assigns values of n and r in the Combine function.
Then assigns result in Column D.

    Next i
End Sub

The sub moves to the next iteration.

Example 3 – Permutation and Combination in Excel VBA Using a Formula

We can use direct PERMUT and COMBIN formulas to find the Permutation and Combination in Excel VBA. We’ll use the same dataset as in the previous method.

Permutation with PERMUT Formula in Excel VBA

Copy the following code into 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, Column D is filled with the Permutations.

VBA Code Breakdown

The first line selects cell D5.

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

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

Copies the Formula to range D5:D9 with AutoFill Destination.

Combination Using the COMBIN Formula in Excel VBA

Copy the following code into 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, the combination numbers fill Column D, as shown in the image below.

VBA Code Breakdown

Range("D5").Select

Selects cell D5.

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

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

Copies the Formula to Range D5:D9 with AutoFill Destination.

Example 4 – Finding the Permutation of Letters for a Given String

Now let’s find all the permutations for a given string. This method shows all the Permutations in the Excel Worksheet.

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 into 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

A window will open to input your string.

Insert the string abcde in the InputBox.
Click on the OK button.

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

Declares a Subroutine_String and str and xRow with proper dimensions.

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

Sets the value of xRow to 4.
Takes the str value from an InputBox.

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

Cell B4 is activated and then the contents of all cells from the selected cell down to the last cell in the column are cleared.

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

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)

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.

Example 5 – Finding a Combination of a Given Number of Ingredients

Suppose we have a dataset of 7 different ingredients for making a burger. We’ll take 4 ingredients and see how many different combinations are possible.

We can take 4 ingredients from 7 in 35 different ways. We’ll store them in an empty table in the range D5:G39.

Copy the following code into 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.
Another 3 For loops are used where the Lower Bounds are the 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

Assigns 4 consecutive ingredients in 4 consecutive columns for the purpose of finding combinations.

Next m
      Next k
     Next j
    Next i

Concludes the loops.

After running the code, 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 methods, we can easily find Permutation with the PERMUT function and Combination with the COMBIN function in Excel.

i – Using the PERMUT Function

The PERMUT function finds the permutations 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 image below shows how to perform this operation using this function.

We can use Integer values in the function for single use:

=PERMUT(7, 3)

ii – Using the COMBIN Function

The COMBIN function finds the combinations 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 combinations of r number of objects taken from a set of n number of objects. The below image shows how to perform this operation using this function.

We can also use Integer values in the Function for single use:

=COMBIN(7, 3)

How to Create a 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.

Enter the following formula in cell D5:

=PERMUTATIONA(B5,C5)

This formula finds B5^C5 and returns the number of possible passwords for the first lock.

Copy the formula to the other cells.

We have the Permutation table shown in the image below.

Frequently Asked Questions

1. What is the difference between permutation and combination?

Permutation is a way 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. For combination numbers, 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 license plate numbers, playing the piano, word formation, selecting teams, and many more.

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 of the same elements, these methods will give the wrong answer.
When working with Permutation and Combination, it is important to have a solid understanding of the basic theory.
Make sure the data range is properly assigned or the wrong answer will be returned.

Download Practice Workbook

Permutation and Combination in VBA.xlsm

<< Go Back to Excel PERMUT Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Perform Permutation and Combination in Excel VBA

How to Launch VBA Editor in Excel

What Are Permutation and Combination?

Permutation and Combination in Excel VBA: 5 Suitable Examples

Example 1 – Permutation and Combination with an InputBox

Example 2 – Using a Dedicated Function to Perform Permutation and Combination

Example 3 – Permutation and Combination in Excel VBA Using a Formula

Example 4 – Finding the Permutation of Letters for a Given String

Example 5 – Finding a Combination of a Given Number of Ingredients

How to Apply Permutations and Combinations in Excel with Functions

i – Using the PERMUT Function

ii – Using the COMBIN Function

How to Create a Permutation Table in Excel

Frequently Asked Questions

Things to Remember

Leave a reply Cancel reply