Microsoft Office Tutorials and References
In Depth Information
LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME
‘Application.ScreenUpdating = False
Range("A:A").ClearContents
Range("A1").Select
Comb2 n, m, 1, ""
MsgBox (NumComb & " combinations listed")
End Sub
Generate combinations of integers k..n taken m at a time,
recursively
Private Function Comb2(ByVal n As Integer, _
ByVal m As Integer, _
ByVal k As Integer, ByVal s As String)
‘Debug.Print m, k, s
If m > n - k + 1 Then Exit Function
If m = 0 Then
ActiveCell = s
NumComb = NumComb + 1
ActiveCell.Offset(1, 0).Select
Exit Function
End If
Comb2 n, m - 1, k + 1, s & k & " "
Comb2 n, m, k + 1, s
End Function
The Sub procedure is fairly straightforward. The power play begins from the
point where the function is called:
Comb2 n, m, 1, ""
Let’s start by analyzing how to logically build the combinations. Let’s say you
want to generate combinations of 5 items taken 3 at a time (i.e., output 3
characters from 1,2,3,4,5). You would build the strings as follows:
Starting with 1, add values sequentially until your subset has a size of 3.
The next value is 2, so you get 1,2. The following value is 3, so you get
1,2,3, at which point your subset size of 3 elements is attained. Then you
look for other combinations by varying the third value, and you get 1,2,4
and 1,2,5. Thus with 1,2 you get 1,2,3 and 1,2,4 and 1,2,5.
Increment the second value to 3 to get the two-piece fragment 1,3. The third
value in sequence is 4, so you get 1,3,4, at which point your subset size of
3 elements is attained. Then you look for other combinations by varying the
third value, and you get 1,3,5. Thus with 1,3 you get 1,3,4 and 1,3,5.
Part
3
1.
2.

Search JabSto ::

Custom Search