# How to generate or list all possible permutations in Excel?

Permutation can be defined as the arrangement of objects in a specific order. In this article, the user would understand the method of generating the permutation by using the VBA code. To do so, the user needs to open the code editor for VBA and after that simply click on the “OK” button. Finally, run the written code, and the permutation sequence will be displayed on the sheet normally. Refer to all the listed steps, to understand the complete method.

## Example 1: To generate or list all possible permutations in excel by using the VBA code

### Step 1

To understand the process of generating all the possible permutation in excel. In this example user will be able to calculate all the possible permutation combination, by using the VBA code.

Consider the below given worksheet −

### Step 2

Go to the “Developer” tab and then under the “Code” section go to the “Visual Basic” tab. Consider the below given image for proper reference −

### Step 3

The newly opened dialog box is of “Microsoft Visual Basic for Applications”. This will open the below given code window −

### Step 4

In the dialog box, open the “Insert” option, and then click on “Module”. Consider the below depicted image for reference

### Step 5

This will open a blank code area −

### Step 6

Type the below-given code to the editor −

' define function header
Sub Evaluating_String()
'Declaring required variables
Dim str_x As String
Dim row_f As Long
Dim sc_x As Boolean
' setting required parameter values
sc_x = Application.ScreenUpdating
Application.ScreenUpdating = False
' message to display for dialog box
str_x = Application.InputBox("Enter text to permute:", "Input dialog box", , , , , , 2)
' if length of entered string is less than 2
' then exist the sub
If Len(str_x) < 2 Then Exit Sub
' if len is greater than 8, then
If Len(str_x) >= 8 Then
' print message for too many premutations
MsgBox "Please eneter less than 8 word combination!!!!!", vbInformation, "Input dialog box"
Exit Sub
' else block
Else
'clear column 1 data
ActiveSheet.Columns(1).Clear
'set 1 to row_f value
row_f = 1
' calling GetPermutation
Call GetPermutation("", str_x, row_f)
End If
Application.ScreenUpdating = sc_x
End Sub
' defining submodule
Sub GetPermutation(str_1 As String, str_2 As String, ByRef row_x As Long)
Dim i As Integer, len_x As Integer
' calculating the required length
len_x = Len(str_2)
' if expression
If len_x < 2 Then
' calculating range data
Range("A" & row_x) = str_1 & str_2
row_x = row_x + 1
' else block
Else
' for each block
For i = 1 To len_x
' calling permutation
Call GetPermutation(str_1 + Mid(str_2, i, 1), Left(str_2, i - 1) + Right(str_2, len_x - i), row_x)
Next
End If
End Sub


A snapshot of the code area is given below −

To ensure the proper execution of the result, mind the proper syntax, and code indentation.

### Step 7

Click on the “Run” button, shown below −

### Step 8

After that, a new dialog box, with the name “Macros” displays the above-declared module name.

### Step 9

This will open the “Input dialog box”, with an input label. Users can enter the data want to create a permutation combination.

### Step 10

Type data as ”A2#”, and click on “OK”.

### Step 11

This will display the possible permutations as given below −

## Conclusion

After completing the above article user will be able to generate the permutation sequence from the provided string data by using the VBA code. The only fact considered by using the VBA code is to use proper syntax to execute the provided code statement and to write the proper code as specified above. In this case, 3 characters are passed to the string, and the obtained number of results are $\mathrm{2^{3}\:=\:6}$.

Updated on: 08-May-2023

810 Views