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.