Microsoft Office Tutorials and References

In Depth Information

**LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME**

Sub ClumsyCombin()

Dim n As Integer

n = InputBox("Number of items?", "Combinations")

NumComb = 0

Range("A:A").ClearContents

Range("A1").Select

For i = 1 To n-2

For j = 2 To n-1

For k = 3 To n

If i < j And j < k Then

ActiveCell = i & " " & j & " " & k

ActiveCell.Offset(1, 0).Select

NumComb = NumComb + 1

End If

Next k

Next j

Next i

MsgBox (NumComb & " combinations listed")

End Sub

A major problem with this approach is that the number of
For...Next
loops

is hard-coded in the routine. If you need to ﬁ nd combinations of, say, 4 items at

a time, an additional
For...Next
loop has to be inserted, and the condition

check needs to be modiﬁ ed. For combinations of 2, a loop would need to be

removed or skipped.

A Better Solution

This problem is an ideal candidate for a recursive function. This solution

comprises a subroutine to specify the inputs n (population set size) and m

(subset size), initialize a combinations counter, and set things up for entry of

the combinations in column A of the active worksheet. This routine then calls

a recursive function to generate the combinations. After all combinations are

generated, the program exits, with a message with information on the number

of combinations found.

Copy the following code to a blank module in a workbook:

Dim NumComb ‘Combinations counter

Sub Combinations()

Dim n As Integer, m As Integer

NumComb = 0

n = InputBox("Number of items?", "Combinations")

m = InputBox("Taken how many at a time?", "Combinations")