Need vba code to generate all combinations from 1 to 10 character length for 1,2,a,b,c,A,B,C,D

[johnnyL]

In other words, generate all of the single characters, double characters, all the way up to 10 characters which would be 1111111111, or, aaaaaaaaaa, or DDDDDDDDDD, or everything in between.

The results can be put in the A column or more if needed.

No Delimiters in between the results.

Example:
1
2
a
b
c
A
B
C
D
11
12
1a
1b
etc ...

 

[StephenCrump]

Here's some code written by @pgc01 for another thread in this forum.

To illustrate, I've shown the 91 combinations generated for 13 elements in combinations of 2 with repeats
=COMBIN(13+2-1,2)

	A	B	C	D	E
1	Choose	2		1	1
2	Combins?	TRUE		1	2
3	Repeats?	TRUE		1	a
4				1	b
5		1		1	c
6		2		1	A
7		a		1	B
8		b		1	C
9		c		1	D
10		A		1	11
11		B		1	12
12		C		1	1a
13		D		1	1b
14		11		2	2
15		12		2	a
16		1a		2	b
17		1b		2	c
18				2	A
19				2	B
20				2	C
21				2	D
22				2	11
23				2	12
24				2	1a
25				2	1b
26				a	a
27				a	b
28				a	c
29				a	A
30				a	B
31				a	C
32				a	D
33				a	11
34				a	12
35				a	1a
36				a	1b
37				b	b
38				b	c
39				b	A
40				b	B
41				b	C
42				b	D
43				b	11
44				b	12
45				b	1a
46				b	1b
47				c	c
48				c	A
49				c	B
50				c	C
51				c	D
52				c	11
53				c	12
54				c	1a
55				c	1b
56				A	A
57				A	B
58				A	C
59				A	D
60				A	11
61				A	12
62				A	1a
63				A	1b
64				B	B
65				B	C
66				B	D
67				B	11
68				B	12
69				B	1a
70				B	1b
71				C	C
72				C	D
73				C	11
74				C	12
75				C	1a
76				C	1b
77				D	D
78				D	11
79				D	12
80				D	1a
81				D	1b
82				11	11
83				11	12
84				11	1a
85				11	1b
86				12	12
87				12	1a
88				12	1b
89				1a	1a
90				1a	1b
91				1b	1b
Sheet2

' PGC Set 2007
' Calculates and writes the Combinations / Permutations with/without repetition
' vElements - Array with the set elements (1 to n)
' p - number of elements in 1 combination/permutation
' bComb - True: Combinations, False: Permutations
' bRepet - True: with repetition, False: without repetition
' vResult - Array to hold 1 permutation/combination (1 to p)
' lRow - row number. the next combination/permutation is written in lRow+1
' vResultAll - Array to hold all the permutations/combinations (1 to Total, 1 to p)
' iElement - order of the element to process in case of combination
' iIndex - position of the next element in the combination/permutation
' Sub CombPerm() deals with the input / output
' Sub CombPermNP() generates the combinations / permutations
Sub CombPerm()
Dim rRng As Range, p As Integer
Dim vElements As Variant, vResult As Variant, vResultAll As Variant, lTotal As Long
Dim lRow As Long, bComb As Boolean, bRepet As Boolean
 
' Get the inputs and clear the result range (you may adjust for other locations)
Set rRng = Range("B5", Range("B5").End(xlDown)) ' The set of numbers
p = Range("B1").Value ' How many are picked
bComb = Range("B2")
bRepet = Range("B3")
 
Columns("D").Resize(, p + 1).Clear
 
' Error
If (Not bRepet) And (rRng.Count < p) Then
    MsgBox "With no repetition the number of elements of the set must be bigger or equal to p"
    Exit Sub
End If
 
' Set up the arrays for the set elements and the result
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
With Application.WorksheetFunction
    If bComb = True Then
            lTotal = .Combin(rRng.Count + IIf(bRepet, p - 1, 0), p)
    Else
        If bRepet = False Then lTotal = .Permut(rRng.Count, p) Else lTotal = rRng.Count ^ p
    End If
End With
ReDim vResult(1 To p)
ReDim vResultAll(1 To lTotal, 1 To p)
 
' Calculate the Combinations / Permutations
Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, 1, 1)
Range("D1").Resize(lTotal, p).Value = vResultAll  'you may adjust for other location
End Sub
 
Sub CombPermNP(ByVal vElements As Variant, ByVal p As Integer, ByVal bComb As Boolean, ByVal bRepet As Boolean, _
                             ByVal vResult As Variant, ByRef lRow As Long, ByRef vResultAll As Variant, ByVal iElement As Integer, ByVal iIndex As Integer)
Dim i As Integer, j As Integer, bSkip As Boolean
 
For i = IIf(bComb, iElement, 1) To UBound(vElements)
    bSkip = False
    ' in case of permutation without repetition makes sure the element is not yet used
    If (Not bComb) And Not bRepet Then
        For j = 1 To p
            If vElements(i) = vResult(j) And Not IsEmpty(vResult(j)) Then
                bSkip = True
                Exit For
            End If
        Next
    End If
 
    If Not bSkip Then
        vResult(iIndex) = vElements(i)
        If iIndex = p Then
            lRow = lRow + 1
            For j = 1 To p
                vResultAll(lRow, j) = vResult(j)
            Next j
        Else
            Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, i + IIf(bComb And bRepet, 0, 1), iIndex + 1)
        End If
    End If
Next i
End Sub

 

[StephenCrump]

PS: Your title said combinations (not permutations), so I have assumed that is what you want.

So for example, 1|2 is shown but not 2|1, as that would be the same combination.

 

[johnnyL]

Sorry, It was late when I posted. Yes Permutations is what I want. example. 1|2 as well as 2|1

I also should have said 1,2,3,a,b,c,A,B,C,D

 

[StephenCrump]

For permutations, just change the B2 input cell to FALSE.

But you realise the numbers are going to be much bigger, e.g. 10 billion (10^10) for the permutations of length 10?

Why do you need to enumerate all the possibilities?

 

[johnnyL]

@StephenCrump Yes. After looking at the code, I see this may get out of hand quickly.

6 was at a million by itself. Factors of 10 expand rather quickly.

I may have to scrap the idea I had because it seems it is probably out of Excel's jurisdiction.

Thank you for the code snippet as well as thanks to pgc.